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

may

05may2:00 pm2:30 pmSimplifying Item Pricing, Availability, and Tracing for Batch Manufacturers

06may11:00 am12:00 pmConfab with Stoneridge - New Features to Empower Your Remote Workforce

12may10:00 am10:30 amThe Modern Manufacturer: Manufacturing Software in the Real World

12may12:00 pm12:30 pmFinancial Implications of Moving from Dynamics GP to Dynamics 365: Licensing and Infrastructure

17may(may 17)8:00 am28(may 28)11:00 amDynamics 365 Finance & Supply Chain Development Training - Online Workshop

19mayAll Day20Manufacturing and Microsoft Technology Conference

19may9:00 am10:00 amWhat is Your Global Manufacturing Strategy? Is It Time to Reshore?

19may10:30 am11:30 amFour Keys to Choosing the Best ERP or CRM Solution

19may10:30 am11:30 amGetting and Keeping Customers in 2021 and Beyond

19may10:30 am11:30 amMastering the Production Floor

19may12:00 pm1:00 pmImagining the Possibilities with Dynamics 365 for Manufacturing

19may12:00 pm1:00 pmBeyond Reporting - What BI Can Do For Your Manufacturing Operation

19may1:30 pm2:30 pmSmart Field Service for Manufacturers and Distributors

19may1:30 pm2:30 pmV is for Victory - The Elements of a Successful MRP Implementation Rollout

19may3:00 pm4:00 pmLot Management Strategies and Best Practices

19may3:00 pm4:00 pmSelling Through a Complex Channel - Distributor Management

20may10:00 am11:00 amManufacturing Technology in 2021 - The Impact of Digital Transformation

20may12:00 pm1:00 pmThe Power of a Connected Shipping Solution

20may12:00 pm1:00 pmEmployee Experience with Company ERP Implementations and Recommendations to Increase Employee Satisfaction

20may1:30 pm2:30 pmReporting and Business Intelligence Strategies for Today's CFO

20may1:30 pm2:30 pmSelecting and Implementing ERP: Don’t Leave it to Chance

20may3:00 pm4:00 pmCybersecurity Tips for Manufacturers – How to Protect Yourself From Ransomware & Other Vulnerabilities

20may3:00 pm4:00 pmEnsuring Quality for Batch Manufacturers

20may3:00 pm4:00 pmGuaranteeing ERP Project Success with an Enterprise Process Review

26may10:00 am10:30 amFinancial Implications of Moving from Dynamics GP to Dynamics 365: Services

26may10:00 am10:30 amThe Modern Manufacturer - Enterprise Asset Management

june

01jun8:00 am02(jun 2)11:00 amPower BI for Dynamics 365 - Online Workshop

02jun2:00 pm2:30 pmBusiness Intelligence for Batch Manufacturers

09jun10:00 am10:30 amThe Modern Manufacturer - Machine Maintenance Work Orders

23jun10:00 am10:30 amThe Modern Manufacturer: Cost Accounting in D365

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