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

By Jeff Spehn | January 28, 2021

summary of power automate

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

Recommended Reading:

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X