“Formula” Data type is a new Power FX preview feature. we will focus on the introduction and related use cases where this “Formula” datatype can be useful. Find more information here: Microsoft Power Fx overview – Power Platform | Microsoft Learn
Formula Column Features:
- Formula columns use the Power Fx syntax similar to Office Excel. Power Fx is the low code language that is being used across the Microsoft Platform.
- Formula column expands the ability of existing calculated and rollup columns it adds the value in expression by performing the arithmetic calculation in real-time.
- The formula expression can be made available with Today () and Now () functions for the first time.
- Many supported functions are available for inbuilt arithmetic operations. Check this Work with Dataverse formula columns – Power Apps | Microsoft Learn for more information.
- As you enter the formula, Intellisense helps you with recommendations for formula, syntax, and errors.
- Making it easy to see and edit the column’s formula directly inline without needing to save or open another window as we do for calculated and rollup fields where another window opens for entering the calculations.
Adding the “Formula” column in Dataverse:
Pre-requisite:
- You must navigate to the PREVIEW maker portal (https://make.preview.powerapps.com/) and under the appropriate environment try to add the column by using the steps explained in the below use cases.
Use Case1 :
To compare the “Sprint Review Meeting Date” with today’s date and if the difference between these two days is less than or equal to 7, then set the value in “Sprint Retrospective Decision” field as either “Sprint Goal achieved” or “Sprint Goal not achieved”.
Steps:
1. Select the table where you want to add the column. For this demonstration, I have used the “Sprint Evaluation” custom table.
2. Click on “+ New Column” and in the Add new column pane, select “Formula” as the data type. As shown below I have added the “Sprint Retrospective Decision” column:
3. Upon selecting the “Formula” datatype, the expression box opens where you can enter the desired formula.
4. Implement formula expression is as shown:
Formula Expression used:
- If(DateDiff(‘Sprint Review Meeting Date’,UTCToday())<=7, “Sprint Goal achieved”,”Sprint Goal not achieved”)
5. Add columns to main form. And click on save to publish the outcome.
Now, see the result:
Use Case 2:
To find difference between two dates by using “Formula” Data type.
Steps:
1. Select the table where you want to add the column.
2. Click on “+ New Column” and in the Add new column named as “Total Days” inside the table and select the data type as “Formula.”
Also, we must already have created two date time columns “start date” & “end date.”
3. Use Formula for Date Difference as shown below:
- DateDiff(new_startdate,new_enddate)
4. Add columns to main form. And click on save to publish the outcome.
Now, see the result
We can see real time troubleshooting and real time refresh that it offers, as explained below:
- The formula editor supports IntelliSense to suggest formulas and errors in real time: For e.g. If while building the formula expression double quote (“) missed in the real time then on hovering the mouse above it, it will show the error description.
- The formula editor supports instant refresh in real time: When you create a record within the model drive app, the formula column executes the defined formula expression and displays the data for the record instantly without refreshing the record.
You can find more details in this https://learn.microsoft.com/en-us/power-apps/teams/formula-columns where you can find the minimum limitations experienced as well, while working with the Formula column remember this feature is still in PREVIEW.
Conclusion
The integration of Power Fx formula columns into Microsoft Dataverse previews exciting new capabilities for microsoft dynamics crm consultants and users. Power Fx delivers a rich, easy-to-use formula language that enhances column calculations within Dataverse, enabling consultants to create custom columns with seamless support for over 250 powerful inbuilt functions. This progression unlocks the next level of Dataverse extensibility and productivity for crm workflows. Whether building solutions or configuring systems, consultants now have an upgraded toolbox through Power Fx to maximize Dataverse functionalities for stakeholders. This formula language preview brings robust new data tools natively into Dataverse for microsoft dynamics crm consultants.
