Power Automate | Dynamically Set D365 CE (CRM) Optional Lookup

A common scenario needed by businesses is to set a lookup value on a Microsoft Dynamics 365 CRM solution (otherwise known as CE) – related table in Power Automate (formerly known as Microsoft Flow). Setting lookups follows a different process than a single line of text or an option set where you can bring over the source value and call it good. In addition, within a Power Automate flow, it needs to be considered whether or not the lookup actually contains a value. If the Power Automate flow is not set up correctly, a null lookup will cause an error when the flow runs. How do you prevent this from happening?

Situation

A business requirement can surface that states that every time a Contact is created, a Region field needs to be populated. The caveat is that the Region must match that of the parent Account Primary Contact’s Region, and, as this field is optional, the value may or may not contain data.

Saving a lookup value that is null will produce an error, so we need to figure out a strategy that can accommodate for a populated or null value.

Flow Summary

Here is a visual of the entire flow we are about to build in Power Automate:

Lookup-Value

Solution

Background

Before we get started on the flow, observe the table relationships and data.

The Account table has a Primary Contact column.

Lookup-Value

The Contact table has the Region column.

Lookup-Value

The business requirement is that this column needs to be populated on the newly-created Contact, and this value may or may not be populated.

Create Flow

Navigate to the desired Power Platform (make.powerapps.com) and solution and create a new Cloud flow.

Lookup-Value

Navigate to Dataverse triggers and select When a row is added, modified or deleted.

Lookup-Value

Just to get the flow saved right away, fill out the trigger information, create a Compose action, provide the flow a name, and save.

Trigger Action

Lookup-Value

Compose Action

Lookup-Value

Flow Name

Lookup-Value

Initialize Region Variable

This is side one of the most important element of the entire flow. We need to initialize a variable that will allow either a populated or null value.

Lookup-Value

We set the default value to null which is key when it comes to updating the Contact Region value.

Get Contact

We need to retrieve the newly-created Contact so we can retrieve the parent Account, Primary Contact, and Region.

Add the Get a row by ID action.

Lookup-Value

Ensure you select the unique identifier from Dynamic content.

Get Parent Account

We only need to proceed to retrieve the parent Account, Primary Contact, and Region IF a parent Account exists, so we add a condition like below:

Lookup-Value Lookup-Value

Add another Get a row by ID action.

Lookup-Value

Here, I only selected three columns to keep the performance of the flow at its highest.

Get Primary Contact

The same concept applies here. We only need to proceed if the parent Account has a Primary Contact.

Add a condition:

Lookup-Value

The Primary Contact belongs to the Account table.

In the yes section of the condition, add a Get a row by ID for the Primary Contact.

Lookup-Value

Set Region Variable

And here we go again. We only need to proceed if the Primary Contact has a Region value.

Add another condition:

Lookup-Value

Set condition to where Region is not equal to null.

Lookup-Value

The Region belongs to the Contact table.

In the yes section, add a Set a variable action and choose the correct variable in the Name list.

Lookup-Value

IMPORTANT – Surround the Dynamic content with the plural logical name for the table. This is how lookups are set in Power Automate.

Another alternative is to just add the Region GUID to the variable and then set the Update a Contact column with the additional information (described below).

Lookup-Value

Update a Contact

Outside of the Parent Account Contains Data condition, add an Update a row action.

Lookup-Value

For Row ID, insert the Dynamic content for the unique identifier from the Get Contact action earlier.

Then set the Region column to the Primary Contact Region Set Value variable.

Lookup-Value

Another alternative is to just add the Region GUID to the variable (described above) and then set the Update a Contact column with the additional information.

Lookup-Value

Initial Testing

Success

If we stop right now and test for a Primary Contact that has a Region value, the flow succeeds.

Create a record from an Account.

Lookup-Value

The flow succeeds.

Lookup-Value

The Region input parameter is perfect – exactly what we want. The flow succeeds, and the newly-created Contact has the correct Region value, pulled from the parent Account’s Primary Contact’s Region value.

Lookup-Value

Error

If Rene Valdes (sample) Contact does not have a Region value set, the flow errors out.

Lookup-Value Lookup-Value

Why does it error out? Since the Region value is not populated but the variable still contains the “gwh_regions()” supplemental information, the flow attempts to populate the Region column with this, and it fails.

Lookup-Value

Changes to Flow

We have finally reached the crux of the solution; we need to make changes to the flow to allow for populated OR null values.

We leave the Set variable action alone, assuming we set it like below:

Lookup-Value

Navigate to the Update Contact action and remove variable for Region.

Instead, click in the value area and click expressions and add the following statement: if(empty(VARIABLE),null,VARIABLE). Replace VARIABLE with the Dynamic content of the variable you created. It will look something like this:

if(empty(variables(‘Primary Contact Region Set Value’)),null,variables(‘Primary Contact Region Set Value’))

Visually, it will look like this:

Lookup-Value

Secondary Testing

Success

Now, when the flow is run, it will succeed regardless if Region is populated.

Lookup-Value Lookup-Value

Why? In the Initialize a variable for Region, we defaulted the value to null:

Lookup-Value

When the flow runs, Regions is not represented – at all.

Lookup-Value

Region is not in the list of columns to update the Contact; it jumps from “Profile Is Anonymous” to “Relationship Type.”

The above is a great method to ensure your flows don’t fail when lookups need to be set in a create or update action and the lookup does not contain a value. If you have any questions about using Power Automate, please connect with us.

Dynamics 365 CE (CRM) How-To eGuide

41 pages of step-by-step instructions for 6 different key tasks in Dynamics 365 Customer Engagement (CRM). Includes interactions with PowerApps and Flow!

Get the eGuide

Dynamics 365 CE (CRM) How-To eGuide

Get the eGuide