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:

Solution

Background

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

The Account table has a Primary Contact column.

The Contact table has the Region column.

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.

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

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

Compose Action

Flow Name

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.

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.

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:

Add another Get a row by ID action.

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:

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.

Set Region Variable

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

Add another condition:

Set condition to where Region is not equal to null.

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.

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).

Update a Contact

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

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.

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.

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.

The flow succeeds.

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.

Error

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

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.

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:

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:

Secondary Testing

Success

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

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

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

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.