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

february

03feb12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 2 – Platform

10feb12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 3 – Implementation Partner

11feb11:00 am12:00 pmConfab with Stoneridge - Livestream - Live Agents, Power Virtual Agents, Omnichannel – Oh My!

16feb12:00 pm12:45 pmFour Keys to Choosing the Best ERP or CRM Solution: Part 4 – Cost

24feb10:00 am11:00 am5 Campaigns Manufacturing Marketers Don’t Want to Miss

25feb11:00 am12:00 pmConfab with Stoneridge - Livestream - Surfing Through the Dynamics 365 Wave 1 2021 Release

march

03mar10:00 am10:30 amThe Modern Manufacturer - MRP vs. MPS – What, When and How

03mar2:00 pm2:30 pmLot Management Best Practices for Batch Manufacturers

10mar10:00 am10:30 amEnhancing the Customer Experience and Driving More Business with Dynamics 365 Customer Service

10mar2:00 pm2:30 pmThe Five Steps to Managing Sales Tax for Manufacturers

11mar11:00 am12:00 pmConfab with Stoneridge - Livestream - Power Apps Component Framework Power Hour

17mar10:00 am10:30 amThe Modern Manufacturer: Pro Tips for your Finance Team

24mar10:00 am10:30 amUnlock the Potential of Digital Marketing with Dynamics 365 Marketing

25mar11:00 am12:00 pmConfab with Stoneridge - Livestream - The Four Keys to Choosing the Best ERP or CRM Solution

31mar10:00 am10:30 amThe Modern Manufacturer: Keys to Reporting Success

31mar01aprFinancial Reporting w/ Account Schedules for Dynamics 365 Business Central

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