Using Logic Apps/Power Automate to set Dataverse Lookups

Way back in 2019, Microsoft released an update to their Dataverse (AKA Dynamics CE/Sales/Service) connector for Logic Apps/Power Automate that offered some wonderful new features (current environment, new action types, better triggers). One thing that we lost was an easy way to set a lookup using the dynamic content pane in Logic Apps/Power Automate.

How Logic Apps/Power Automate Used To Do It

In simpler times, we would populate the lookup attribute with a GUID/unique identifier from a previous action:

Unfortunately, when we try this today, we receive an error/failure with the following code: URL was not parsed due to an ODataUnrecognizedPathException. Resource not found for the segment provided in the URL.

How Logic Apps/Power Automate wants you to do it

To avoid this error and populate your lookup, you will need to use the following syntax: /PluralTableSchemaName(GUID).

Populating the Company Name (Accounts) lookup for a contact in Dataverse looks like this: /accounts(GUID).

And in Logic Apps/Power automate, it should look like my example below:

When the job is run, we see a successful result:

This method works great when we can guarantee that our dynamic content has a value. But what happens when that lookup is empty? 

How Power Automate/Logic Apps Expression Can Help

For this example, we can try to populate the Company Name (Contacts) with the primary contact dynamic value from our ‘Create a test account’ action:

When the process is run, this error is returned: The supplied reference link -- /contacts() -- is invalid. Expecting a reference link of the form /entityset(key).

Why do we receive this error? Looking at the Inputs section in run details: We are passing the correct syntax but no GUID/unique identifier:

Expressions to the rescue

One method to avoid this error is through the use of expressions.

Expressions can be used to create logical statements (if, and, or, etc). Meaning, we can evaluate our data inside of a parameter rather than using a dedicated condition action.

The expression we will use in our example is as follows: if(equals(outputs('Create_a_test_account')?['_primarycontactid_value'],null),null,concat('contacts/','(',outputs('Create_a_test_account')?['_primarycontactid_value'],')'))

In plain English: If the primary contact lookup is equal to null from our action ‘Create a test account’ then populate the attribute/parameter with null. Else, populate the attribute/parameter with the value of the primary contact lookup.

When we run the process with the expression, it creates a contact and we populate the field with null instead of /contacts().

Expressions are helpful as they reduce the number of actions needed. However, they can be difficult for anyone but a super user to understand exactly what the process is doing if you use too many. Additionally, if your lookup is already populated and a null value is passed, the field will be empty.

Expressions: Not the only name in the relate game

If you expressions are not the road you want to travel, then do I have the right action for you.

Introducing, the Relate Rows action!

The relate rows action can be a useful method of populating Dataverse lookups if your goal is to create workflows that are understandable to anyone who needs to review it. Furthermore, it allows you to decouple the creation of a relationship from the create or update action.

The two actions in our workflow provide an excellent example of the former statement:

If our goal is to populate the test account’s primary contact field by creating a 1:1 relationship with the contact, it would require an additional ‘Update a row’ action to achieve that.

Now, if we attempt to create the contact first, we would be able to populate the primary contact field on the account but we could not create a 1:N relationship between the account and the contact.

Instead, we add two actions after our initial creation steps to create different relationships between the records:

The first action creates a 1:N relationship between the account and contact (Contact’s parent account), while the second action creates a 1:1 relationship between the account and contact (Account’s primary contact).

When this design pattern is coupled with the ‘scope’ control and the ‘configure run after’ setting, you can effectively counter any potential interruptions to your process that may result from null

values or previous failures.

This pattern has the added benefit of allowing you to turn what Power Automate/Logic Apps considers a failure into what you may consider a success.

Decisions decisions…

Unfortunately, what tends to happen when you have options is that you need to make design decisions for what will work best for a particular workflow. Below are some of the considerations I make when deciding which pattern to use.

When to use expressions:

• Limiting actions for consumption billing/performance (particularly in Logic Apps)
• Additional logic necessary to determine lookup value
• You have less than 3 lookups to populate on a single record

When to use relate rows: 

• Looking to improve readability/accessibility
• Relationships depend on records that are created further in the process
• Large number of relationships across multiple tables

These are not rules to live by, only how I approach my design. In the end, both are perfectly valid methods and how you populate your Dataverse in Power Automate/Logic Apps is up to your design preference.

I hope you found this information valuable and be sure to check out my blog in the future for more articles on Power Automate & Logic Apps.