How to use Power Automate to Update 1:N Relationship in Dynamics 365 Customer Engagement
Once in a while, we run into a requirement in Dynamics 365 Customer Engagement to update a 1:N (i.e. One to Many) relationship with automation. Prior to the Power Platform world, there really was not a way to do this using out of box workflows in Dynamics 365 or Dynamics CRM. In the past, developers have written custom plug-ins that would add custom steps into the workflow utilities.
Now in today’s era, we can update records in a One to Many relationship using Power Automate (aka Flow).
There are many, key 1:N relationships in standard Dynamics 365 CE where you may want to update the many related records. For example:
Accounts to Contacts – Use Power Automate to update each contact record related to an Account.
Accounts to Opportunities – Take action on all related Opportunities to an Account.
Entity to Activities – Take action on an activity like “all related tasks” to any regarding the entity.
There are many, many more examples.
Use Case:
For this article, we will show a Power Automate for the Opportunity to Opportunity Product (this is our 1:N) relationship. We have a custom field on both the Opportunity and Opportunity Product entities called “Est. Qty”. This field is updated by users on the Opportunity main form. When this happens, we needed automation to take the value in the Est. Qty field from the Opportunity and update each Opportunity Product record related to that Opportunity with the same value.
Here is a summary of our Power Automate. We will discuss each step in more detail after this summary.
In the first step, we are triggering the Flow to run on the update of the Est. Qty field (stn_estqty) on an Opportunity record.
In the second step, we are simply setting up a place holder (Initialize Variable) to store the value of the Est. Qty field from the triggered Opportunity. We are giving our variable the name “Opp Est. Qty” and defining it as an Integer.
Next, we are checking a condition to make sure that there is a value in the Est. Qty field. This would protect us if the field had a null value in it for some reason. We would not want to move ahead with the updating of Opportunity Products if the field was Null.
In the next step, in our “Yes” or “True” branch after our Condition check in Step C above, we first want to go “fetch” or get a “list” of records from our related Opportunity Product entity that we want to update. The key to this step is to make sure we are using a filter query (highlighted in BLUE below) to make sure we are only getting those records related to our original Opportunity.
In our last step, we are now going to apply an Update step to each of the records returned in our previous step D. Here we are updating the Est. Quantity field on the Opportunity Product record with the value that we stored in a variable in step B.
That is it!
Just a few notes and comments on the above:
- When writing Flows or Power Automates for Dynamics 365 CE, be sure to ALWAYS do them in a solution file. This makes the moving of the solution file from a DEV or Sandbox environment much easier and will automatically set your database connections for you.
- We do not need anything in the “No” or “False” branch of our condition step. If our condition fails, the workflow will just stop seeing there is not anything in the “No” branch.
- Getting the syntax correct in the Filter Queries can be tricky. That is one of the main reasons we love to write these blogs so others can see how it is done!
As mentioned earlier, there are so many different scenarios where you may need to update related records from a 1 to Many relationship. The Power Automate will have the same steps listed above, with the exception of the entities or tables you are using.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.