Adding the Power Fx Formula column into Dataverse

Microsoft has introduced a new field that is a Formula field. Now Microsoft is going to depreciate the calculated field and the formula field will be used in the future. The formula field is very powerful in many aspects and it works the same as the formula expressions in the power apps. Now, Microsoft is leveraging the power of FX expressions. In this blog post, we will discuss some use cases in which the formula field can be useful and then we will also discuss the features and limitations of the formula field.

Features of the Formula Field:

Formula Fields in Power Apps bring a lot of features that empower developers to create sophisticated business logics with ease:

1. Power Fx Syntax: Formula Fields utilize the Power Fx syntax, similar to Office Excel, providing developers with a familiar language for expressing calculations and manipulating data within their applications.
2. Real-Time Arithmetic Calculation: Formula Fields expand upon the capabilities of existing calculated and rollup columns by performing arithmetic calculations in real-time, ensuring accurate results and dynamic data updates.
3. Today() and Now() Functions: For the first time, Formula Fields introduce support for Today() and Now() functions, allowing developers to incorporate current date and time values directly into their formulas, enhancing the timeliness and relevance of data within their applications.
4. Built-In Functions: Formula Fields offer a wide range of supported functions for performing built-in arithmetic operations, providing developers with the tools they need to create complex calculations and transformations with ease.
5. Intellisense Support: As developers enter formulas, Intellisense provides real-time recommendations for formula syntax and functions, as well as error detection, streamlining the development process and ensuring formula accuracy.
6. Inline Formula Editing: Unlike traditional calculated and rollup fields, Formula Fields allow developers to view and edit formulas directly inline, eliminating the need to save or open another window for formula entry. This intuitive interface enhances productivity and simplifies formula management.

 

Use Cases for Formula Field:

Let’s talk about how formula fields in Dynamics 365 can make work easier. Imagine you have to manage expenses or figure out which leads are the most promising. With formula fields, you can set up rules to handle these tasks automatically.

1. Expense Approval Workflow:
   • If the total expense amount is less than $100, set the approval status to “Approved”.
   • If the total expense amount is between $100 and $500, set the approval status to “Pending Manager Approval”.
   • If the total expense amount exceeds $500, set the approval status to “Pending Finance Review”.
2. Lead Qualification:
   • If the lead source is “Referral”, set the lead rating to “High”.
   • If the lead source is “Website Form”, set the lead rating to “Medium”.
   • If the lead source is “Cold Call”, set the lead rating to “Low”.
3. Employee Performance Review:
   • If the average of all performance ratings given by managers is greater than 4, set the employee’s performance status to “Exceeds Expectations”.
   • If the average rating is between 3 and 4, set the status to “Meets Expectations”.
   • If the average rating is less than 3, set the status to “Needs Improvement”.
4. Product Discount Calculation:
   • If the customer type is “Wholesale”, apply a 20% discount to the product price.
   • If the customer type is “Retail”, apply a 10% discount.
   • If the customer type is “Corporate”, apply a 15% discount.
5. Event Registration Status:
   • If the event registration date is in the past, set the registration status to “Closed”.
   • If the event registration date is within the next 7 days, set the status to “Upcoming”.
   • If the event registration date is more than 7 days in the future, set the status to “Open”.

6. Event Reminder: Set up a formula field to calculate the number of days remaining until an event or deadline, and display a reminder message if the event is approaching within a certain timeframe.
7. Age Calculation: Automatically determine the age of a contact or customer based on their birthdate, simplifying age-related calculations without the need for custom JavaScript or additional logic.
8. Subscription Renewal: Calculate the renewal date for subscription-based services by adding a predefined number of days or months to the subscription start date, ensuring timely renewal reminders and notifications.
9. Due Date Calculation: Calculate due dates for tasks, projects, or deliverables based on their start dates and predefined timelines, ensuring timely completion and visibility into upcoming deadlines.

 

These were the simple use cases where formula field can be helpful. Now, here we will show you some examples of some usecases.

Age Calculation:

• • Field Names: Birthday (Date of Birth), Age (Calculated Age)
  • 

 

We will calculate the age based on this field.

Now, we will create a formula field using the formula datatype to calculate the age.

 

The formula to calculate the age will look like this

 

If we see the output it will be looking like this.

 

Explanation: This formula calculates the age of an individual based on their birthdate. It subtracts the birth year from the current year and adjusts for cases where the current date has not yet reached the individual’s birthday this year.

Due Date Calculation:

• • Field Names: Start Date, Due Date (Calculated)
  • Formula Expression:

DateAdd(StartDate, 7, Days)

Explanation: This formula calculates the due date for a task or project based on its start date and a predefined number of days or months. It adds the specified duration to the start date to determine the due date.

1. Event Reminder:
   • Field Names: Event Date, Reminder Message (Calculated)
   • Formula Expression:

If(

    DateDiff(EventDate, Today(), Days) > 0,

    Concatenate(“Event in “, Text(DateDiff(EventDate, Today(), Days)), ” days”),

    Concatenate(“Event today!”)

)

Explanation: This formula generates a reminder message for an upcoming event based on its date. If the event is within 7 days from the current date, it displays a reminder message; otherwise, it remains blank.

1. Expense Approval:
   • Field Names: Total Expense, Approval Status (Calculated)
   • Formula Expression:

 If(

    TotalExpense <= 1000,

    “Approved”,

    “Pending Approval”

)

Explanation: In this expression:

“TotalExpense” represents the total expense amount.

The “If” function evaluates whether the total expense is less than or equal to 1000. If true, it sets the approval status to “Approved”. If false, it sets the approval status to “Pending Approval”.

This expression determines the approval status based on the total expense amount. If the expense is $1000 or less, it is automatically approved; otherwise, it requires further approval. Adjust the threshold value (1000 in this example) as needed for your specific approval criteria.

These formula expressions illustrate how formula fields can be used to automate calculations and decision-making processes within Dynamics 365, simplifying complex tasks and improving productivity.

 

Calculated Field vs. Formula Field:

While Calculated Fields have been a staple feature in Power Apps, Formula Fields introduce several enhancements and advantages:

1. User-Friendly Interface: Formula Fields offer a more intuitive and user-friendly interface compared to Calculated Fields, making it easier for developers to create and manage complex formulas.
2. Enhanced Functionality: Formula Fields introduce new functions and operators, expanding the capabilities of developers to manipulate and analyze data within their applications.
3. Real-Time Feedback: Unlike Calculated Fields, Formula Fields provide real-time feedback and error detection, improving the development process and reducing debugging time.

Limitations of Formula Fields:

Despite their myriad benefits, Formula Fields do have certain limitations to consider:

1. Limited Data Sources: Formula Fields may have limitations when it comes to integrating with certain data sources or external systems, potentially restricting their functionality in complex enterprise environments.
2. Performance Impact: While Formula Fields are optimized for performance, excessively complex formulas may still impact the overall performance of Power Apps, particularly in large-scale applications.
3. Learning Curve: While the Expression Builder simplifies formula creation, developers may still require some time to familiarize themselves with the syntax and functionality of Formula Fields, especially those transitioning from Calculated Fields.