We will learn and understand the formula columns in Dataverse. Before we start, make sure to subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.
What are formula columns in Microsoft Dataverse?
The Formula Columns are currently in preview, but as per Microsoft’s announcement, these formula columns are currently available in Power Apps for Teams. Formula columns empower citizen developers to express business logic on top of existing columns and reference column from other tables, directly in Dataverse. As calculations are done at the Dataverse level, results can be seen and used in all Dataverse endpoints, including the Dataverse for Teams table view, canvas and model-driven Power Apps, Power Automate, Power BI, the Dataverse APIs etc.
Formula columns are based on Power Fx commands which are already present in the Canvas App editor. We are using the same Excel-like language you use for Power Apps can be applied here. Power Fx provides a much richer set of functions than were previously available with calculated columns, especially for text processing. Even Power Fx’s support for Dataverse many-to-one relationships and option sets is included.
Creating a Formula Column in Power Apps
We have created a new formula column in Power Apps for Team. This column will concate the data from two existing fields called “Name” and “Employee ID”. Below is the Power FX formula for appending the two attributes values.
Concatenate(Name, 'Employee Number')Below screen represents the creation on new Formula Column in Dataverse.
Apart from Power Apps for Team, we can also create Formula Columns in canvas and model-driven Power Apps, Power Automate, Power BI, the Dataverse APIs etc. As you can see in the following Image from Dataverse below , you can use the Power Fx formula bar to create a formula column that computes few arithmetic operations using the supported functions. The formula dynamically returns based on the data type of the resulting expression.
The formula editor supports IntelliSense to suggest formulas and errors in real time. This new column feature supports text, decimal, choice, boolean, lookup and limited date/time functionality, targeted primarily for core calculations while we are working towards building the complete parity with existing Calc columns. On read, all calculations are on done on the client side level / real-time as added projections of the SQL Select query. The formula column for non-team application is currently in preview, Microsoft will start rolling out this feature as per the region. Stay tuned to CRM Crate for understanding more about Formula Columns.
Hi, thank you for the article
I have a question, since there is no lookup function available, what can i use for the same objective?
I have a table with following columns: Id of resource, Name of resource and Id of manager, and i want to create a column Name of manager, to be autopopulated, considering that i have id and name of the resource. Normally i would use a Lookup formula, but i get an error, saying it is not supported.
Thank you in advance for the reply