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

If you're using Common Data Service (current environment) connector in your flow and getting the following errors, that means you are either

  1. setting the GUID value directly or
  2. not providing a correct plural version of entity schema name.

Empty segment encountered in request URL. Please make sure that a valid request URL is specified.
Resource not found for the segment xxxxx
1. In (classic) Common Data Service connector, you can just set the GUID value of the record to populate the lookup value of the field. For polymorphic lookups, you will have an additional Type dropdown field (e.g. Customer Type) to choose the entity type of the record that you are setting.

But in the new CDS (current environment) connector, you need to provide the type of entity even if the lookup is just normal lookup to a single entity. For polymorphic lookups, there will be multiple fields for each entity type (e.g. Customer (Accounts) and Customer (Contacts) ) and you need to fill up the correct entity type that you are trying to populate. (and you still need to provide the entity type). Imagine if you are creating one of the activity records (e.g. Task) in the environment with a lot of entities enabled for Activities, you will see a lot of Regarding (xxxxxx) fields in your flow step.


2. Don't be fooled by the plural display name of the entity which is shown beside the field label. The EntitySetName that you need to provide is case sensitive and you cannot use that plural display name (Contacts) or entity logical name (contact). As mentioned in Dynamics CRM Tip of the Day #1311, what you need to use is the plural version of the entity schemaname (aka EntitySetName) followed by the GUID enclosed in parentheses. <<EntitySetName>>(<<RecordGUID>>)
e.g. contacts(<ContactID>)


For out of the box entities, the EntitySetName is the same as the plural display name for most of them. But things get tricky when you are trying to populate the value for the custom entity. You may be able to add 's', 'es', 'ies', etc. but how the system defines the EntitySetName is not perfect for all scenarios. (e.g. the EntitySetName of my lzw_journey entity is lzw_journeies)

In order to get the so-called EntitySetName, you can either search from
[Organization URI]/api/data/v9.1
e.g. https://contoso.crm.dynamics.com/api/data/v9.1

or replace the <<EntityLogicalName>> in the following URL format to get it for a particular entity
[Organization URI]/api/data/v9.1/EntityDefinitions(LogicalName='<<EntityLogicalName>>')?$select=EntitySetName
e.g. https://contoso.crm.dynamics.com/api/data/v9.1/EntityDefinitions(LogicalName='contact')?$select=EntitySetName

Now that we have to provide the EntitySetName to set the value, I am not sure how to set the null value or set the optional lookup value from another record which can potentially be empty.
When I set the null value from the Expression, I get the following error.

An error occurred while validating input parameters: Microsoft.OData.ODataException: The 'odata.bind' instance or property annotation has a null value. In OData, the 'odata.bind' instance or property annotation must have a non-null string value.
The error message is the same as setting null for single-valued navigation property using Xrm.WebApi but I am not sure it is possible/necessary to use a DELETE request to remove a reference to an entity as mentioned in the Microsoft documentation.

If you have a solution for that, please leave a comment below. For now, I changed it to the (classic) CDS connector for those scenarios.

UPDATE: As one of the commenters, manish, suggested, setting NULL value of the single lookup field can be done in the same way as web API calls. All you need to do is to use HTTP Request with Azure AD and DELETE method.
Format of that is : /api/data/v9.1/incidents(GUID)/xxxx_InternalContactId/$ref
where xxxx_InternalContactId is the name of Navigation Property linked with InternalContact.

Or we can use the Unrelate records function too.

But it is not working for most of the scenarios that I encounter such as clearing multiple lookup fields in one API call (without multiple Unrelate/Delete records actions), or the most common issue for me is passing the GUID from another step which is an optional lookup field (so that the value may/may not be null)
While you're at here, please check out and vote for my submission at Power Automate Ideas regarding that.
https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Allow-Setting-null-value-to-the-lookup-field-in-Common-Data/idi-p/427649

Comments

  1. Hi Linn,

    I was curious after checking your post and wondering if there is any way to set lookup as null and found that it works the same way as web api calls. All you need to do is to use HTTP Request with Azure AD and DELETE method .

    Format of that is :

    /api/data/v9.1/incidents(GUID)/xxxx_InternalContactId/$ref
    where xxxx_InternalContactId is the name of Navigation Property linked with InternalContact.

    Thanks

    ReplyDelete
    Replies
    1. Thanks for the suggestion, manish.
      That really works but it is not pratical for most of the scenarios that I need such as clear multiple lookup fields in one API call (without multiple Unrelate/Delete records actions), or the most common issue for me is passing the GUID from another step which is an optional lookup field (so that the value may/may not be null)

      If you agree with that, please go and vote for my submission at Power Automate Ideas. 😁
      https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Allow-Setting-null-value-to-the-lookup-field-in-Common-Data/idi-p/427649

      Delete
    2. Hi Linn,

      I am wondering if you are using CDS or CDS(Current) . Just now I tried using Update a record action using CDS and setting the lookup to null works for me . Please let me know if it works for you.

      Thanks

      Delete
    3. Please use Expression and type null

      Delete
    4. It is CDS (Current) that is having a problem with setting null to lookup field.
      CDS works fine but I want to avoid using it as much as possible so that I can deploy the flow to the target environment without re-authorising the connection. 😁

      Delete

Post a comment

Popular Posts

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

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

[Bug] Using the ClearCollect() Function in PowerApps does not Pull any Lookup Columns from Common Data Service (CDS) Data Source

Pass Parameters to Quick Create Form in Dynamics 365 using formContext.data.attributes

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

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

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

Build Efficient Flows Using Filter Expression in CDS (current environment) Trigger

[Power Apps] New improvements in Embedded Canvas Apps which are undocumented / unannounced