How to set or map null value in Lookup Field using Power Automate CDS Connector (Current Environment)

By | October 19, 2020

Introduction

Power Automate is a powerful business application that allows the user to create and automate workflows across multiple applications and services without developers’ help.

In this blog, we will explore how to set a lookup field as null while mapping the Lookup field with another record’s lookup field which has null value. We can simply handle this in custom programs (plugin, workflow, javascript, etc. ) but in Power Automate it is quite tricky if we use CDS(Current Environment) connector.

Scenario: On Contact entity, there is a lookup field of Account entity named “Company Name” and this “Company Name” is null/empty on the contact record. Now, we will create an opportunity record on the creation of the contact record and will map the “Company Name” on the new Opportunity record. So, here we won’t be able to create Opportunity record with direct mapping field of “Company Name” since the “Company name” is empty on the contact record. To handle this, we need to take some extra efforts, that is, we need to an add additional expression while mapping the field.

We created a Power Automate which will get triggered on the creation of the Contacts entity record as shown below.

set or map null value in Lookup Field using Power Automate CDS Connector

  1. We have added to Create Opportunity entity record as shown below using CDS(Current Environment)
    set or map null value in Lookup Field using Power Automate CDS Connector
  2. Then mapped all required fields and set Account lookup field.
    set or map null value in Lookup Field using Power Automate CDS Connector
  3. In the above screenshot, we have set lookup field with direct mapping without any additional expression, as <EntitySetName>(<RecordGUID>), e.g. accounts(<accounted>)
  4. In this case, Power Automate fails and you will get below error if Account lookup field does not contain data on the contact record:

    Error – The supplied reference link —- is invalid. Expecting a reference link of the form /entityset(key).

    To handle the above scenario, we need to add the below expression in the Account lookup field mapping as shown in the below screenshot. Here, we need to check the value conditionally using empty() function and set empty string ‘ ‘ to the lookup field.

    if(empty(triggerOutputs()?[‘body/_parentcustomerid_value’]), ‘ ‘,
    concat(‘accounts(‘, triggerOutputs()?[‘body/_parentcustomerid_value’],’)’))

  5. In the above expression, we are checking if Account lookup doesn’t contain data (i.e. empty()) then set null, otherwise set value in the lookup field. For this, we need to concat “EntitySetName” and “Guid” of the record i.e. accounts(<accountid>)
    set or map null value in Lookup Field using Power Automate CDS Connector
  6.  The above expression can be used to set the lookup field with null value in Create a new record action.

Conclusion

Using the above solution we can Set Lookup Field with null value using Power Automate CDS Connector (Current Environment).

One thought on “How to set or map null value in Lookup Field using Power Automate CDS Connector (Current Environment)

  1. Victor Sanchez

    Great post. However, this only works for Trigger Create condition, if you want to do a Trigger Update you need to do a condition and replicate the step without the lookup.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *