[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

Another easier way to get the EntitySetName is by using the Metadata Browser for Dynamics 365/Power Apps Chrome browser extension.


If you set the null value or the optional lookup value from another record which can potentially be empty in Common Data Service (current environment) connector, you will 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.

There is a workaround for that issue and you can refer to it in my other blog post.

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
  2. Thanks Linn. It works like a charm !!

    ReplyDelete
  3. Hi Linn! Great post. Could you kindly tell me what I am doing wrong in this case? I am trying to populate my lookup field in CRM with data coming from Cosmos DB.

    For instance, I have a field within CRM called LEAD PM
    When I am in my logic app, I add this parameter, which is Lead PM and then when I add the dynamic field which contains my values, this fails to work.

    I am doing this:
    Lead PM(dynamicfield)

    and this is not working. Please add some suggestion. Thank you

    ReplyDelete
    Replies
    1. As mentioned in the blog post, it needs to be prefixed with the plural version of the entity schemaname (aka EntitySetName)
      What is the entity type of "Lead PM"?

      Is it the Contact entity? If so, it should be something like this:
      contacts(dynamicfield)

      Or is it the User entity? If so, it should be something like this:
      systemusers(dynamicfield)

      If it is the custom entity, find out the EntitySetName from API URL.
      [Organization URI]/api/data/v9.1

      Delete

Post a comment

Popular Posts

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

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

[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

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

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

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

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

Access Flow Run History within a Record in Dynamics 365/CDS