How to Set Lookup Fields with Null Value from Dynamic Content in CDS (current environment) Connector

In one of my previous blog posts, I wrote about How to Set Lookup Field Value in Common Data Service (current environment) Flow Connector. In that blog post, I mentioned that I was not sure how to set a lookup as null either directly or from another record’s lookup field as the output of the step (which can potentially be null). Recently, I discovered a way to do that and I would like to share that with you all in this post.



Problem statement
What is a Nullable value in this situation?
Nullable value is any field that is blank or has no value. The field could be empty/null because the field was not mandatory and/or the user may not have entered any value for it when filling up the form. When a nullable filled is used in flows to get data from, we need to consider the scenarios where the field value is empty, how to check if it’s null from a previous step (trigger, Get a record, List records) before using that field in the subsequent step of the flow.

What is that problem with Nullable lookup value in Common Data Service (current environment) connector?
Because of the way the lookup value has to be set in CDS (current environment) connector <<EntitySetName>>(<<RecordGUID>>), the flow throws an error "The supplied reference link -- systemusers() -- is invalid." if the <<RecordGUID>> of the reference field is empty.



Scenario
For simplicity’s sake, let’s consider a scenario where the system must create a new Account record if a Contact record is created and if the Company Name field of the Contact is empty. One of the requirements is to populate the Contact’s Full Name into the Name of the Account and the Preferred User of the Contact into the Preferred User of the new Account. In this scenario, the Preferred User field of the Contact entity is optional and the value can be empty for some of the Contact records.



Solution
I posted in the Power Automate Ideas to allow setting null value to the lookup field in CDS (current environment) connector. I got a response from one of the Microsoft employees that the workaround solution is to conditionally check the value using empty() and set empty string '' to the lookup field (not null).

This is the flow expression that you can use to set the lookup field with nullable value in Create a new record action.

if(
    empty(triggerOutputs()?['body/_preferredsystemuserid_value']),
    '',
    concat(
        'systemusers(',
        triggerOutputs()?['body/_preferredsystemuserid_value'],
        ')'
    )
)


However, setting the empty string '' to the lookup field only works for Create a new record action, it does not work for Update a record action. If you set the empty string to the lookup field in the Update a record action, you will get the following error.
The supplied reference link -- -- is invalid. Expecting a reference link of the form /entityset(key).
If you want to set the null value to the lookup of the existing record, you can use the Unrelate action as shown in this blog post by EY Kalman. Unrelate action can only update one lookup field for each step and currently, there is no other way to set null value to the multiple lookup fields in one step using the CDS (current environment) connector.

If you are looking for an option to set the nullable value to the lookup field in the update action, there are 3 workaround solutions
1. Check the value in the Condition step to find out if the value is empty. If the value is not empty, update all required fields and if the value is empty, update the rest of the fields except the lookup field.
Pro: Only 1 web request API call to the server.
Con: If there is any change required, both steps need to be updated.


2. Update all other fields except the lookup field in one Update step. Check the value in the Condition step and if the value is not empty, another Update step in the Yes condition branch to update the lookup field only.
Pro: All common logic is in the first Update step so that it is easy to maintain and any change can be done in one place.
Con: There will be x2 API calls and update the record twice if the value is not empty.


3. Use old/standard/normal/soon-to-be-deprecated/non-current CDS connector. Setting the lookup field in this connector is just record GUID and it does not complain if the value is empty.
Pro: Just one simple Update step.
Con: Normal CDS connector is not ALM-friendly and the connections in the flow require to be re-authorise after each deployment.

This is just the simplified version of the scenario but I encountered a similar situation in most of the flows that I built for the project work. One of those is for creating the Case Stage custom entity on the status change of the Case entity and certain lookup fields of the Case Stage entity was populated with the nullable values from the Case and Contact (customer in the Case) entities. Another one is for creating the Attendance entity based on the Attendee field of the Appointment and as usual, some of the lookup dynamic values from other entities are nullable.



Other Scenarios
This can also be applied in other scenarios such as conditionally setting the value of the polymorphic lookup. 

In the example above, the duplicate Contact is created from the Contact from the trigger step. The Company Name is the Customer Lookup field which can either be populated with Account or Contact. The following expressions can be used to populate the Company Name lookup based on the entity type of the Company Name value.

For Company Name (Accounts)
if(
    empty(triggerOutputs()?['body/_parentcustomerid_value']),
    '',
    if(
        equals(
            triggerOutputs()?['body/_parentcustomerid_type'],
            'accounts'
        ),
        concat(
            'accounts(',
            triggerOutputs()?['body/_parentcustomerid_value'],
            ')'
        ),
        ''
    )
)

For Company Name (Contacts)
if(
    empty(triggerOutputs()?['body/_parentcustomerid_value']),
    '',
    if(
        equals(
            triggerOutputs()?['body/_parentcustomerid_type'],
            'contacts'
        ),
        concat(
            'contacts(',
            triggerOutputs()?['body/_parentcustomerid_value'],
            ')'
        ),
        ''
    )
)



Summary
In the Create a new record action of the Common Data Service (current environment) connector, we can use the empty string '' to set the lookup field with an empty value.

Comments

Post a comment

Popular Posts

[Power Automate] How to Set Lookup Field Value in Common Data Service (current environment) Flow Connector

[Power Apps] Using Common Data Service's Lookup Data Type Field in Canvas App

Validating Document Upload in Business Process Flow by Setting Field Requirement Level Conditionally (Part 2 of 2)

Get the Lookup Display Name and Option Set Value Label in a Single Query Using a CDS (Current Environment) Connector FormattedValue Property

Move Attachment from File Field of CDS to SharePoint in Power Automate (Part 1 of 2)

Find out how to include a link to the record (Record URL) when sending an email from Dynamics 365/CDS using flow

[Power Automate] List Records - Use Expand Query to Retrieve Related Data in flow

Send Email from Dynamics 365/CDS with Attachment from Notes Using Flow

Getting the Lookup Attribute of the Parent Record in Power Apps Canvas App Development with Common Data Service (CDS)