There are times when you need to create a many-to-many relationship (N:N) in Power Automate (formerly Microsoft Flow) for Dynamics 365 for Sales (D365) records. Some documentation exists, but what should you do when you have multiple environments? Do you need to update the Relate Records Action URL each time you move the flow from one environment to another?

The answer is yes if you hardcode the URL. However, this flow will show you how to tap into an existing flow component to avoid this unnecessary flow management situation.

For this example, I need to create a N:N relationship between a Contact record and a Web Role record when a custom field, Contact Type, is set to one of two values.

In summary, the flow will include the following:

• Common Data Service (Current Environment) trigger
• Connection Reference (New)
• Common Data Service Get Record action
• Condition to determine Contact Type
• Common Data Service List Records action
• Apply to Each
• Common Data Service Relate Records action

Common Data Service (Current Environment) Trigger

Navigate to the correct D365 environment, using https://make.powerapps.com/. Create a solution if needed or open an existing solution. Click New Flow.

• In the search bar, enter Common Data Service
• Select When a record is created, updated or deleted
• Select the desired Trigger condition
• Entity the appropriate entity
• Set the correct Scope

Connection Reference (New)

Connection References are relatively new as of November 2020. Each trigger and Common Data Service action requires a Connection Reference.

Each Connection Reference can only be used 16 times as indicated by this Microsoft error message when attempting to assign a trigger to an existing Connection Reference:

Ensure the Connection Reference is part of your solution when migrating to the next environment.

Common Data Service Get a Record Action

After the trigger, add a new Action and choose Common Data Service Get a Record. Enter the same Entity as the trigger. Click in the Item ID field and choose Contact unique identifier from the dynamic content.

Condition to Determine Contact Type

I am using a flow variable to house the D365 Contact Contact Type field which is a lookup field to the Contact Type entity.

• Click new Step
• Search for Initialize variable
• Give the variable a name

If you wanted, you could initialize and set the variable in the same action; here I chose to enter a second action, set a variable, and set it with the following value:

@{outputs(‘Get_Contact’)?[‘body/_ebs_contact_type_value@OData.Community.Display.V1.FormattedValue’]}

To get this value, run the flow and then look at the flow results. Navigate to the point of Get Contact and look at all the values. In the body section, you will see values like this:

This gives us the lookup display value.

Now click inside the Set Contact Type variable Value field and ensure the dynamic contact is showing. Enter fullname and select Full Name so that it populates the field.

With your cursor, select the entire value, copy, and then paste into Notepad or other application that will strip out the formatting. You should see something like this:

Enter this string into the Value field; it should reformat to look like below:

We now have a string value of the lookup/entity Contact Type.

Select new Step and choose Condition.

Common Data Service List Records Action

In the yes condition block, click new Add an Action and search for Common Data Service List Records. In this case, I selected Web Roles, which has a N:N relationship to Contacts.

I know I am looking for only one specific Web Role, so I will utilize the Fetch Xml Query. Use the D365 Advanced Find to get exactly what you want. Here is my Advanced Find query:

When you have the query the way you desire, click the Download Fetch XML button, and save to your computer. Open the file, copy the code, and paste into the field into the Fetch Xml Query field.

Set the top count to 1:

Apply to Each

Click Add an Action and enter Apply to each. Select the value option from the dynamic content. This will loop through the list of Web Roles:

Common Data Service Relate Records Action

It took all the above to get where we really need to be.

Click Add an action and enter Relate records. Since we are looping through Web Roles, enter Web Roles for Entity name. Select the appropriate relationship. You could find the N:N relationships for that entity by going to a D365 solution, expanding entities, finding the correct entity, and then selecting N:N. In this case, it is adx_webrole_contact.

This is the critical step to set a dynamic value for the Relate Records URL field so that you do not have to update every single time you move this flow from one environment to another.

Unbeknownst to you when you created the Get a Contact action above, it established a Web API URL, and it looks something like this:

Here is the run flow version to give you an idea of what it looks like:

This is what we need for the Relate Records URL! So instead of hardcoding a value that will need to be changed, why don’t we enter a dynamic value that will change according to the environment in which it is in?

All you need to do is click inside the URL field and enter odata in the dynamic content search:

Select the Contact OData Id:

Save, and run.

When a Contact record is created and the Contact Type is either Salesperson or Broker-Agent Representative, a Salesperson Web Role will be dynamically and automatically assigned to it.

This is a great method to keep your Power Automates dynamic across environments. By following the steps above, you will not have to modify the flow after migrating to a new or different environment. Please contact us if you have any questions about using Power Automate.