Dataverse, a powerful tool developed by Microsoft, has unveiled a new feature known as “formula columns.” This addition is not just any random update; it aims to simplify and bolster data operations in Dataverse. In this blog post, we will delve into the essence of formula columns, understand its benefits, and also guide you through the steps to create one.
Introducing Dataverse Formula Columns
“Formula columns” have been an integral part of Dataverse for Teams. In a significant move, Microsoft recently announced its preview release in the full version of Dataverse. But what are they, and why should you care? Formula columns act as virtual columns in a Dataverse table, not storing any data but returning values based on Power FX formulas. These are similar to calculated columns present in platforms like SharePoint and SQL Server.
While Dataverse did have calculated columns, these depended on a specific language with bounded capabilities. The new ‘formula column’ type allows users to design calculations with Power FX, the same language prevalent in canvas apps and model-driven commands. This aligns seamlessly with Microsoft’s vision of establishing Power FX as the standard language across the entire Power Platform, encompassing tools like Power Automate and Power BI. The fascinating part? A single Dataverse table can possess both calculated columns and formula columns.
Why Formula Columns in Dataverse Matter
The core benefits of formula columns revolve around two main aspects:
- The values undergo computation server-side in Dataverse. This is not only faster but also minimizes the need to fetch data on the client side for calculation purposes.
- They present a streamlined method to showcase calculated values, especially in model-driven and portal apps, which might lack robust native Power FX support.
Creating a Formula Column in Dataverse
As of now, formula columns are in preview. To initiate the creation of a formula column, users need to access the preview designer here:
https://make.preview.powerapps.com/
Within the table designer, it’s straightforward to define a formula column. Simply create a new column, opt for the data type as ‘formula’, and input your desired formula in the accompanying text box.
An exemplary formula column named “IsImportant” might return ‘true’ if the description field contains certain keywords like urgent or critical. This exemplifies tasks that were once challenging with conventional calculated columns due to restricted support for string functions.
The formula input is facilitated with full IntelliSense. For a comprehensive list of supported functions and operators, you might want to refer to the official documentation from Microsoft, ensuring that you remain within the boundaries of the formula column’s capabilities.
Limitations to Consider
Notwithstanding their advantages, formula columns have their set of limitations. The prime concerns relate to locale-specific functions. Since formula columns get calculated server-side, the server is oblivious to the user’s language. As a result, certain issues, especially related to localization, can arise, making tasks like extracting date parts a bit challenging.
Wrapping It Up
Formula columns are undeniably a game-changer in the Dataverse realm. They not only enable the use of Power FX at the database level but also accentuate the functionality of model-driven and portal apps. While they are still in the preview stage, their potential is undeniable. We hope this blog provided clarity on the concept and utility of formula columns in Dataverse.
For more insights or technical assistance, don’t hesitate to contact us. Our team is always ready to assist and guide you through any challenges you might face.
