Instant Update Dataverse calculated columns with Power Automate
Instantly Update Dataverse Calculated Columns with Power Automate Tips
Key insights
In a previous blog post, the powers of Dataverse calculated columns were highlighted, specifically their ability to automate calculation logic on the database side to streamline Power Apps development. A highlight was the rollup columns feature, which can summarize data from child rows to a parent row; however, they usually update only once an hour, which can be less than ideal for real-time data needs.
The issue with the default behavior of rollup columns is that their update delay can cause confusion in real-time applications, like a non-alcoholic Beer Rating app. Users experience a delay in seeing their ranking updated, expecting an immediate reflection of their input. To overcome this, the blog suggests that calculated columns can be updated programmatically with a Power Automate cloud flow, enhancing the user experience.
Power Automate and the Dataverse Web API offer a solution to the rollup column delay: by using the CalculateRollupField function, rollup columns can be triggered to update instantly. The blog provides a detailed guide to creating a cloud flow triggered by events such as new data entries, enabling immediate reflection of the updated data in apps, though it does still carry a slight execution delay.
It is also possible to trigger the rollup column update directly from Power Apps, which has the advantage of immediate startup and API calls being handled by the app's end-user. Further, creating and invoking a custom action in Dataverse can also initiate the update, which is particularly useful if the update needs to occur before an edit or deletion within the row is committed.
To conclude, Power Automate and the Dataverse Web API can be leveraged to update calculated columns immediately, a significant improvement over the typical one-hour system job update. This ensures a more dynamic and responsive user experience in Power Apps utilizing these columns, and both cloud flows and custom actions present viable options to achieve this.
- Explained the benefits of Dataverse calculated and rollup columns for automated logic processes in databases.
- Addressed the one-hour delay limitation in updating rollup columns and introduced a solution using Power Automate cloud flows.
- Provided a solution to update calculated columns instantly using the CalculateRollupField function from the Dataverse Web API.
- Discussed how to trigger rollup column updates from Power Apps for immediate action execution.
- Outlined using custom actions in Dataverse to initiate updates, accommodating different user scenarios.
Understanding Dataverse Calculated Columns and Power Automate
Dataverse calculated columns offer a robust feature set for developers, automating complex calculation logic within databases and reducing the need for additional coding in client-side applications like Power Apps. The convenience they provide, however, is somewhat diminished by their delayed update frequency. Fortunately, Power Automate comes to the rescue by allowing cloud flows to trigger real-time updates. Leveraging the power of the Dataverse Web API even further enhances Power Automate's capabilities, empowering app developers to provide end-users with a realtime response to their interactions.
By enabling this feature, applications that require immediate data refreshing, such as leaderboard rankings or transactional systems, can offer a seamless and dynamic user experience. Additionally, considering options between triggering updates directly from Power Apps or via custom actions provides flexibility and control to developers, catering to the specific needs of different scenarios. Ultimately, the integration between Dataverse and Power Automate exemplifies the efficiencies and innovations possible within the Microsoft ecosystem when it comes to modern app development.
In an earlier blog post, the powerful capabilities of Dataverse calculated columns were highlighted. These columns can perform automatic calculation logic on the database level, eliminating the need for such calculations in the client side of Power Apps. Rollup columns are especially useful as they compile data from related child rows onto a parent row.
Despite their convenience, rollup columns have a drawback: they update only once per hour. This timing may not always match the real-time needs of app UI features. To address this issue, the blog post delves into how to update these columns instantly using Power Automate cloud flows.
Using a non-alcoholic beer rating app example, the blog shows that without an immediate update, user rankings after submitting a beer review are delayed, leading to potential confusion. This waiting period for the calculated columns to refresh can be an inconvenience, prompting the search for a more immediate solution.
To solve the problem, an API call through Power Automate is proposed. First, you must create a flow that activates when a new beer review is added, triggering the update process for the rollup column.
The process involves building the correct URL for an HTTP request; this is done by replacing specific components of the URL such as the environment address, the EntitySetName, and field names. Detailed locations of this information are briefly explained, ensuring the user can easily configure this for their environment.
The blog then instructs on how to make the HTTP request using the Invoke an HTTP request function with a preauthorized connector, eliminating the need for usernames and passwords. This request updates the specific rollup field as needed.
There is further exploration of triggering the rollup column update from Power Apps, and how cloud flows can be initiated directly from within Power Apps to save time and offload API calls to the end-user. This approach is often preferred and can enhance real-time data interaction.
Additionally, the blog touches upon the use of custom actions for updating rollup columns. By creating a custom action, one can trigger a flow from Power Apps or use classic workflows for more synchronous updates in specific scenarios.
In conclusion, the blog has provided insights on how to employ Power Automate and Dataverse Web API for instant updates to calculated columns. It allows users to circumvent the hourly system job schedule, leading to a more responsive experience in Power Apps that depend on up-to-date rollup column data.
Understanding Dataverse Calculations and Updates
Dataverse, a powerful data service, automates complex calculations within databases, enhancing the functionality and experience of Power Apps. Rollup columns simplify data summarization across tables, yet pose challenges with update timings. Power Automate, Microsoft's business automation tool, can help synchronize data updates with user interactions. It enables immediate updates to rollup columns, fostering a dynamic and user-friendly interface that reflects real-time changes in the application's data, thus increasing overall accuracy and user satisfaction.
Read the full article How to update calculated columns in Dataverse instantly with Power Automate
People also ask
How do you update a calculated column?
To update a calculated column in Microsoft 365, navigate to your list or library where the column exists, and go to the 'List Settings' or 'Library Settings.' Find the column in the 'Columns' section and click on it to edit. In the column settings page, you can modify the formula or the additional column settings. Once your changes are completed, save them. The calculated values will be updated automatically based on the new formula for all items in the list or library.
What is the difference between rollup and calculated field in Dataverse?
The difference between a rollup and a calculated field in Dataverse (part of Microsoft Dynamics 365) lies in their function and use. A rollup field aggregates data from related records, such as the sum of all open opportunities for an account. Rollup fields perform calculations over a set of records, such as COUNT, SUM, MIN, MAX, or AVG. On the other hand, a calculated field performs calculations using fields from the same record - think of it as a formula field that does not span across relationships like a rollup.
How do I add a calculated column in Dataverse?
To add a calculated column in Dataverse, access the table where you want to add the column via the Dataverse Table designer or the legacy Dynamics 365 solution explorer. Select 'Add Column,' and then in the column properties, choose 'Calculated' for the 'Data Type.' After this, you can define the calculation by specifying the conditions, operators and related columns in the calculation. Once complete, save the column to add it to the table.
What is the limit of calculated columns in Dataverse?
The limit of calculated columns in Dataverse is enforced by the overall complexity of the calculations and the number of dependent attributes. Each entity (table) can have up to 10 calculated columns. Microsoft enforces this limit to ensure that performance is not compromised. When creating calculated columns, it is essential to understand and consider the limitations, keeping in mind that complex calculations might impact system performance.
Keywords
Dataverse Calculated Columns Update, Power Automate Instant Update, Update Calculated Fields Power Platform, Real-time Dataverse Column Refresh, Automated Calculated Column Synchronization, Power Automate Calculated Field Workflow, Dynamics 365 Calculated Column Update, Power Apps Instant Column Calculation, Trigger Dataverse Column Recalculation, Power Automate Real-time Data Update