How to work with dataverse formula column

This Power Apps Dataverse tutorial will cover all you need to know about the Dataverse Formula Column, including what a Dataverse Formula field is and how to construct one in the dataverse table.

I had to use the Dataverse Formula field in a recent project where I needed to perform some calculations using numerous columns. And the Power Apps Dataverse Form will show the outcome.

Additionally, we will cover all the topics below:

• What are the operators for the Dataverse Formula column
• Supported Data types in the Dataverse Formula column
• Supported Function types in Dataverse Formula Column
• What are the Dataverse Formula Functions
• How to create a Dataverse Formula Column?
• Dataverse Formula Column Examples

Formula Column in Dataverse

In a Microsoft Dataverse table, formula columns are columns that display a calculated value. Formulas are created with the programming language Power Fx, which is strong but user-friendly.

Within Dataverse, formula columns enable the addition of business logic and calculations (formulas).

The columns for Dataverse Formula are currently in preview status. Power Apps for teams now offer these formula columns.

Results from calculations can be viewed and used in any Dataverse endpoints, including the Dataverse for Teams table view, canvas and model-driven Power Apps, Power BI, Power Automate, and Dataverse APIs, among others, because calculations are performed at the Dataverse level.

We may use the same excel-like language for Power Apps Dataverse formula columns because they are built on Power Fx commands.

NOTE:

You can include formula columns as a calculated field. Plugins and roll-up fields cannot currently use formula columns.

Operators for Dataverse Formula column

In a Dataverse formula column, you can use the following operators:

• + (Addition)
• – (Subtraction)
• * (Multiplication)
• / (Division)
• % (Percentage)
• ^ (Circumflex)
• in
• exactin
• & (And)

Supported Data types in the Dataverse Formula column

In a Dataverse Formula column, you can show the following sorts of data:

1. Text
2. Decimal Number
3. Yes/No (boolean)
4. Date

The Currency data type is not supported at this time.

Function types in Dataverse Formula Column

In the Dataverse formula column, you can employ the following function types:

1. Decimal
2. String
3. Boolean
4. Option Set
5. DateTime (TZI)
6. DateTime (User local) (limited to comparisons with other user local values and the DateAdd function)
7. DateTime (Date only) (limited to comparisons with other date-only values, and the DateAdd function)
8. Currency
9. Whole Number, promoted to Decimal

Also Read: Power Apps Dataverse Choices [Complete Guide]

Dataverse Formula Functions

The following scalar functions are available for usage in the Dataverse formula columns:

Abs	And	Average	Blank
Char	Concatenate	DateAdd	DateDiff
Day	EndsWith	Exp	Hour
If	IfError	Int	IsBlank
IsError	ISOWeekNum	IsUTCToday	Left
Len	Ln	Lower	Max
Mid	Min	Minute	Mod
Month	Not	Or	Power
Replace	Right	Round	RoundDown
RoundUp	Second	Sqrt	StartsWith
Substitute	Sum	Switch	Text
Trim	TrimEnds	Trunc	Upper
UTCNow	UTCToday	Value	Weekday
WeekNum	Year

NOTE:

When there is no decimal separator present, only whole numbers can be used with the Text and Value functions. Different regions use different decimal separators. There is no way to correctly understand or produce the decimal separator because formula columns are evaluated without locale information.

Create a Dataverse Formula Column

Now we will see how we can create a formula column in the Dataverse table. Refer to the instructions below.

• Go to your Dataverse Custom data table (Product Sales). This custom table has some below fields with various data types. Such as Name, Customer Name, Product ID, etc.

• Now to create a Dataverse formula column, click on the + New column (on the top of the page). On the New column pane, specify the below fields:
  • Display Name = This column must be present. Enter the name of the Formula column you wish to create in the Dataverse custom table (let’s say it’s called Product Name And ID).
  • Description = This field is not required. Specify the field’s purpose in order to create it.
  • Data type = Expand the chevron and select the data type as a Formula (Preview).

• Similarly, fill in the remaining fields below:
  • Formula = This formula box will display once you choose the Data type as Formula and you can enter any calculation or formula there.
  • Format = Seven different sorts of formats exist. Text, Rich Text, Email, Phone Number, etc. Select the format as per your need.
  • Schema name = It is a column that has already been filled in with data from the display name. It contains the customization prefix for the Dataverse solution publisher. You won’t be able to edit the table once you’ve saved it.

• Simply Save the column as shown below once all the fields have been filled in. The new Formula column in the Dataverse table is eventually ready for use.

In this case, I only wanted to display the Product name and the Product ID in the Formula field. We must merge these two Dataverse fields, Name, and Product ID, in order to achieve this.

We can use the three methods listed below to do this.

Approach – 1: (By using Concatenate function and & Operator)

To combine both fields in one field, we can use the Concatenate function along with the & operator.

Concatenate(Name & "---" & 'Product ID')

Approach – 2: (By using Concatenate function and without using & Operator)

To achieve the need, we also can use the Concatenate function without using the & operator.

Concatenate(Name, "---", 'Product ID')

And set the Format as Text type. Refer to the screenshot below.

Check out: Power Apps Dataverse Yes/No Field

Approach – 3: (By using only & Operator)

Moreover, by using only the & (AND) operator, we can merge two Dataverse fields in the Formula column. You can apply the code below in the Formula section.

Name & "---" & 'Product ID'

Once you applied the formula, set its Format type and then click on the Save button.

Now refresh the Dataverse table (Product Sales). We can see the new formula field (Product Name And ID) will appear with the calculated or merge value as shown in the image below.

This is how we can create a Formula field in the Dataverse table.

Have a look: How To Get Dataverse List Rows Count Using Power Automate

Dataverse Formula Column Examples

We’ll then talk about some actual scenarios based on the Dataverse Formula Column to help you understand.

Example – 1:

• In this case, I want to perform a calculation wherein the formula field will show the message “Discount Applicable” if the Product sale (Price) is larger than 5,00,000.
• The formula field will display the text “Discount Not Applicable” if the requirement is not met.
• To achieve this, create a Dataverse Formula column (Is Discount Applicable) and then select the Data type as Formula (Preview).
• Next, apply the below code inside the Formula box (Fx) as:

If(Sales>500000, "Discount Applicable", "Discount Not Applicable")

Where,

Sales = Dataverse Decimal Number field

• Select the Format as Text and then Save the formula field.

• Now refresh the Dataverse table (Product Sales). You can view the new formula field (Is Discount Applicable) with the user-based condition as in the screenshot below.

Check out: How to Create Dataverse File Field

Example – 2:

• What I want to do in this scenario is offer a 50% discount on the product price if there are more than 3 units purchased. If there are fewer than three items purchased, there is no discount and the item’s pricing remains the same.
• To do this, add a Dataverse Formula column called “Total Amount” and then choose “Formula” as the data type (Preview).
• Apply the following code next inside the Formula box (Fx) as follows:

If(Quantity>3,Quantity*Sales*0.50,Quantity*Sales)

Where,

Quantity, Sales = Number fields in Dataverse table

Finally, save the formula field to view the result.

• Reload the Dataverse table (Product Sales). You can observe the new formula field with the particular condition (Total Amount). For instance, there will be a 50% reduction if there are more than 3 of the same item. Otherwise, the price will remain the same.

For a better understanding, consider the real-world case examples given above. This is how the Dataverse Formula field can be utilized.

Moreover, you may like some more below Dataverse tutorials:

• How to Update a Row in Dataverse Using Power Automate
• How to Detect Text in Dataverse Using AI Builder
• Dataverse Solution [Complete Guide]
• How To Remove Commas From Dataverse Number Field
• Dataverse Version History
• How to Create Dataverse View
• How to create and use dataflow in Dataverse
• How to get data from Dataverse in Power Apps
• Delete All Records From Dataverse Table [With Examples]
• Invalid Connection. Please Choose a Connection Power Automate

This Microsoft Dataverse tutorial described what a Dataverse Formula field is, how to add a Formula column to the Dataverse table, and how to use it. We also addressed the following extensive topics:

• Operators for Dataverse Formula column
• Supported Data types in the Dataverse Formula column
• Function types in Dataverse Formula Column
• Dataverse Formula Functions
• How to create a Dataverse Formula Column?
• Dataverse Formula Column Examples