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

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.

1. We have added to Create Opportunity entity record as shown below using CDS(Current Environment)
   
2. Then mapped all required fields and set Account lookup field.
   
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>)
   
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).