Ignoring Null Fields When Mapping Field Values in Power Automate

By - March 23, 2022

Automating standardized record creation can save users a great amount of time. Traditionally this is easier using legacy workflows, so consider that as an option when reviewing requirements. Legacy workflows will ignore null values and still create the record if null values were mapped, including on required fields. Power Automate flows, on the other hand, will fail if you attempt to map a null value.

There are certain cases where I need to create a new record and map values in a flow rather than a workflow. Usually this is because of a limitation with workflows, such as only being able to grab directly related records.

Still, there is a solution to get around this issue in flow! It requires creating an expression to check if the dynamic value is null.

For each field that you will set using dynamic content from another record, use the following expression:

if(equals(coalesce(triggerOutputs()?['{dynamic source field}],'NULL'),'NULL'),'', {dynamic source field or variable})

The expression can be broken up into 3 parts, denoted by the various colors

  • Checking if the field is null
  • If null, set to null
  • If not null, set field to this value

Note: using a variable is particularly useful when setting lookup fields. If setting a text or number field, you can reference the dynamic field directly. With lookups, we need to pass the context of the entity surrounding the GUID, and flow doesn’t seem to like if you put this directly into the expression.

Creating the variable to set a lookup field will look something like this:

Creating variable to reference account schema and guid to set lookup

You would want to use the if expression above when mapping dynamic values to any fields that could potentially not be filled out in the source record.

Mapping dynamic fields using expression

A closer look at the expression setting Company Name (an Account lookup):

if(equals(coalesce(triggerOutputs()?['body/_accountid_value'],'NULL'),'NULL'),'',variables('Account'))

In contrast, the expressions setting First Name and Last Name would look like this:

if(equals(coalesce(triggerOutputs()?['body/firstname'],'NULL'),'NULL'),'',triggerOutputs()?['body/firstname'])

if(equals(coalesce(triggerOutputs()?['body/lastname'],'NULL'),'NULL'),'',triggerOutputs()?['body/lastname'])

Since these two fields are text fields, we do not need to reference the entity like we do for lookups, so we can call the dynamic content directly.

Marti is a Senior Consultant in RSM's Dynamics practice. She has experience working with many aspects of Dynamics CE and is highly skilled in Power Automate Flows, and Portals. She specializes in Nonprofit and Association clients. LinkedIn

Receive Posts by Email

Subscribe and receive notifications of new posts by email.