Dynamics 365 CE Multiple Entity Rollup Field with Power Automate

By - November 21, 2019

Out of the box rollup fields are very useful in Dynamics 365 Customer Engagement, but have limitations such as the inability to pull data from calculated fields, the inability to reference multiple entities at once and the fact that they won’t always display updated information. Fortunately we can overcome all of these limitations using Microsoft Power Automate.

To illustrate the potential for a Power Automate Flow to create more powerful rollup fields, consider a scenario where Dynamics 365 Sales is being used to manage sales of widgets. The AccountContact and Lead entities each have a custom currency field called Annual Widget Budget that is used to estimate their potential purchases. The sales team manager wants a way to tell how much potential revenue each Salesperson is managing through a field called Total Managed Budget on the User record that totals the budget for records that person owns. A standard rollup field could only show data from one entity, but using Power Automate we can populate this information in a single, simple currency field.

To begin, navigate to the Power Automate Application in Office 365, go to + Create and create an Automated flow. Name your Flow and select the Common Data Service trigger When a record is created, updated or deleted. Note Dynamics 365 triggers can also work.

If using this trigger causes an error, create the Flow with an Update trigger then make copies for the Create and Delete triggers. Alternatively, you can redesign to use a Scheduled trigger to run periodically like a standard rollup field.

The first step is to set the Trigger Environment to (Current), the Entity Name to Accounts and the Scope to Organization. Note that using the (Current) environment setting allows you to create your Flow in your development instance of Dynamics, export it and then import it into the production instance to promote to production without making any changes to Flow steps directly. Then, be sure to rename your Trigger step so it can be understood at a glance:

Next, create an Initialize Variable step. This variable will be used to sum the Annual Widget Budget field values for all records that the User owns.

With this groundwork in place, we can start totaling the fields. Create a List Records step to return all Account records that have the same OwningUser as the trigger Account’s OwningUser. For more information on filtering on the List Records step reference this blog.

Now that we have the list of relevant Accounts we can sum their Annual Widget Budget fields. Add an Increment Variable step and select the variable Total Managed Budget and the rollup field, Annual Widget Budget. Note that using this field automatically creates an Apply to each block around the Increment Variable action. This just means that the Flow uses a loop to perform the Increment Variable step for each result of the List Records step above. After this step the Total Managed Budget variable will have the sum for the Account entity, just like a standard rollup field.

Repeat the List Records and Increment Variable steps for the Contact and Lead entities with the same owner. This will set our variable to the sum of the Annual Widget Budget field for all records owned by this User.

The last step is to add an Update Record step on the User entity. Use the Owner lookup field value from the trigger as the record identifier and set the Total Managed Budget field with the variable.

With all steps included, the complete Flow will look like this:

 

This field is now set as a multi-entity rollup field triggering on the Account entity. To enable the Flow to trigger on the Contact and Lead entities, we will make copies of this Flow and update the Trigger.

To do that, save and select My Flows in the left navigation bar. Check your Flow and Save As, updating the name to add Contact Trigger.

Open your copy and switch the Trigger entity from Account to Contact, then save and Activate your Flow. The rollup will now trigger on Contact.

Repeat these steps for Lead and the rollup is complete!

Sam is a Senior Consultant in RSM's Dynamics practice. He has experience working with many aspects of Dynamics CE as well as integration technologies like Scribe and SmartConnect.

4 Comments

Innawan Widiyan Kumara February 7, 2020 at 1:31 pm

Hi Sam,

Can we create a roll up field and get the average value in Ms. Flow ? In my case, I want to average the opportunity line value and put the average value in custom field in opportunity.

Reply
    Sam Stern February 7, 2020 at 7:19 pm

    Hello Innawan,

    Good question – you absolutely can. One way to do that is to add a couple simple steps to the process. Initialize an integer variable for the number of child records, aka “Number of Child Records” starting at 0. Put this step directly beneath the other Initialize Variable step.

    Now that you have that variable, add a “Set Variable” step in your “Apply to each” loop beneath your increment variable step. This step should set the new “Number of Child Records” variable to its previous value + 1, so enter the expression “add(Number of Child Records,1)” . This will be an easy way to count the number of Opportunity Lines. If you are calculating over multiple related child entities, add one of these steps per related entity.

    Now that you have both the total of all the values, and the number of values, the average is a simple division of the two variables. So in your final “Update Record” step, instead of populating your custom field with the “Total…” variable, populate it with the expression “div(Total Variable,Number of Child Records Variable)”.

    One note to be aware of is that if this Flow is triggered on the deletion of the only child record for your Opportunity it will get a divide by 0 error and it won’t update correctly. You can circumvent this by adding a Condition step. Check if “Number of Child Records” variable is greater than 0. Then put the update step that calculates the average in the True branch. In the False branch, put a new Update Record step for the same record and set the value of your custom field to 0.

    Thanks for reading!
    -Sam

    Reply
      Innawan Widiyan Kumara February 8, 2020 at 1:29 am

      Hi Sam,

      Thank for your answer. If you don’t mind, could you add a picture on it. Because I stiil learn on it.
      Btw, I will try on it.

      Thanks Sam.

      Reply
        Sam Stern February 8, 2020 at 3:13 pm

        Sorry Innawan, but we can’t add images in the comments! But that could be a good idea for a future blog! Good luck with your Flow.

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Receive Posts by Email

Subscribe and receive notifications of new posts by email.