How to use Power Automate to Update 1:N Relationship in Dynamics 365 Customer Engagement

by | January 28, 2021 | Dynamics CRM, Power Platform

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.

summary of power automate

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.

first step triggering the flow to run update

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.

2nd step setting a place holder

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.

check a condition

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.

using a filter query

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.

apply an update step to each records

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.

Related Posts

0 Comments

Submit a Comment

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

Upcoming Events

july

01jul11:00 am12:00 pmConfab Live with Stoneridge – Dataverse Virtual Entities

14jul12:00 pm12:30 pmBeyond Reporting - What Business Intelligence Can Do For Your Agribusiness

15jul10:00 am11:00 amMastering the Production Floor

21jul10:00 am10:30 amThe Modern Manufacturer: Manufacturing Software in the Real World

21jul12:00 pm1:00 pmIs it Worth it to Upgrade to Dynamics 365 Finance and Supply Chain Management? - Everything AX Users Need to Consider

28jul10:00 am10:30 amLot Management for Batch Manufacturers in Life Sciences, Chemical and Food

28jul2:00 pm2:30 pmConsiderations for Successful Testing Plans for Major Releases of Dynamics 365 Finance and Supply Chain Management

29jul11:00 am12:00 pmConfab Live with Stoneridge – Integration Strategies for End User Success

august

04aug2:00 pm2:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

10aug(aug 10)8:00 am11(aug 11)11:00 amPower BI for Dynamics 365 - Online Workshop

11aug10:00 am10:30 amThe Modern Manufacturer - Enterprise Asset Management

11aug12:00 pm12:30 pmBusiness Intelligence with Dynamics 365 Finance and Supply Chain Management – Game Changing Insights and Analytics

11aug2:00 pm2:30 pmUsing Technology to Manage Complex Sales Pricing, Commission, and Rebate Programs

18aug10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

18aug10:00 am12:00 pmIntro to Power BI for Dynamics 365 Business Central – Online Workshop

18aug12:00 pm1:00 pmSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X