How to Set Lookup Column Value in Microsoft Dataverse Connector in Cloud Flows

If you're using Microsoft Dataverse 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 logical name.

Empty segment encountered in request URL. Please make sure that a valid request URL is specified.
Resource not found for the segment xxxxx

In that case, you may see one of the errors above or below.

URL was not parsed due to an ODataUnrecognizedPathException. Resource not found for the segment provided in the URL
1. In Microsoft Dataverse (legacy) connector, you can just set the GUID value of the record to populate the lookup value of the column. For polymorphic lookups, you will have an additional Type dropdown column (e.g. Customer Type) to choose the entity type of the record that you are setting.

But in the new Microsoft Dataverse 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 columns 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) columns 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 logical name (aka EntitySetName) followed by the GUID enclosed in parentheses. «EntitySetName»(«RecordGUID»)
e.g. contacts(«ContactID») which will be translated to something like
contacts(9b5d7915-a22a-41d4-ab95-e4fc2a5f80b3)


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 get it from the table hub under Tools menu.



You can also search it from the API
[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 Level up for Dynamics 365/Power Apps Chrome browser extension to open the Entity Metadata list view and search by the table name.






Another alternative is the Metadata Browser for Dynamics 365/Power Apps Chrome browser extension.

The EntitySetName and the brackets need to be set in the input of the column (as in the first image below). If you use Expression to set the field with EntitySetName (as in the 2nd image below), you will get the following error .
Unable to process template language expressions in action 'Step_name' inputs at line '0' and column '0': 'The template function 'xyz_entitysetname' is not defined or not valid.'.



UPDATE2: You can now set the null value to the lookup column by using the Microsoft Dataverse connector.

If you set the null value or the optional lookup value from another record which can potentially be empty in Microsoft Dataverse 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 column 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
  4. Hi, it's nice to see your blog. But I have a question can I populate guid to the lookup value of the field?

    ReplyDelete
    Replies
    1. Yes, you can populate the GUID value to the lookup field with <>(<>) format as mentioned in the blog post.
      e.g. contacts(a40675ed-84bc-43ca-bf4d-57e98d49d58b)

      Delete
  5. Hi Linn,
    I know this is a somewhat older article but I just wanted to say thank you for your contribution here. I've just spent the better part of 4 hours trying to debug this issue and every other article I read neglected to point out the need to use the EntitySetName and not the plural display name. That guidance and also the helpful API link to identify the Set name made all the difference in the wee hours of the morning. Thank you!

    ReplyDelete
    Replies
    1. I'm glad it helped to solve your problem 😊
      I really appreciate that you took some time to leave this comment. It really made my day. ☺️

      Delete
  6. Hi Lynn,

    Great article thanks. When referencing the lookup table do you have to use the GUID or can you just use a name field.
    For instance I want to reference the Contact Name "John Smith" rather that an ID 8046dfOAB......

    This is important for me becuase I am using the flow across different environments and have to keep getting a different GUID for the same Table (but in different environments)

    ReplyDelete
    Replies
    1. Hey dlutchy,

      When referencing the lookup table do you have to use the GUID only. The name can be duplicate and based on how the MS Dataverse API works, GUID is required to populate the lookup column value.

      Normally, we create the rows of the reference table (e.g. Country) with a script or tool so that the GUID is same across different environments. Otherwise, you will have to "List rows" and find the GUID of the row by name before referencing the lookup.

      Delete
  7. Thanks a ton! at last your blog solved my problem. The issue is with the SetName and the Plural Name, everywhere it's mentioned as Plural name but you have explained it clearly. Thanks again!

    ReplyDelete
    Replies
    1. I'm glad you learnt about the EntirySetName from my blog post. 😊

      Delete
  8. This blog post is a magic. Exactly what I wanted. Thanks a lot Linn.

    ReplyDelete
    Replies
    1. Thanks for your comment, Ramesh. Glad to know that it helps.

      Delete
  9. Hi Linn

    I am trying to populate owner field on opportunity entity from the excel records.
    Only name of owner is given, no guid.

    Can you please help me with the syntax to set the owner lookup field from the name of owner.

    Thanks in advance

    ReplyDelete
    Replies
    1. To retrieve the GUID for the owner, you will need to call List Rows action on the Users table with the filter "fullname eq <>" and get the GUID of the matching record.

      Delete
  10. To know how to write to complex columns in dataverse , go to my YouTube video .

    I have explained in details , how to update dataverse lookup column using power automate 
    https://www.youtube.com/watch?v=hstUL0UfnI4



    ReplyDelete
  11. Hi Linn, thank you so much for your post, very helpful. I have a requirement, that have a Contact-based folder structure in SharePoint, but I want to same/similar folder structure of the Account, or even other entity like Opportunity in SharePoint. Can I use same way you introduced to do that?

    ReplyDelete
  12. Hi Linn. I got the link to your blog in one of my posts on Power Platform community. I was wondering you maybe have a solution for my business case. I'm trying to update 'Owner' field in Dataverse with Power Automate. My Dataverse table has a 'Supervisor email' field and I was wondering if there's a way to retrieve Dataverse user ID (not AAD ID) from it and then update Owner field?
    Link to my post: https://powerusers.microsoft.com/t5/Microsoft-Dataverse/How-to-retrieve-user-ID-from-Dataverse-table-based-on-email/m-p/2165595#M30051

    Thanks!
    Pawel Pomaranski

    ReplyDelete
    Replies
    1. I've replied in that forum thread. Please mark it as answer if it solves your problem.

      Delete
  13. Hi Lynn, I am trying to create a note and the regarding is a lookup to a custom entity called 'Loyalty Membership'. At first, I was just setting the GUID in the regarding field and was obtaining the error ODataUnrecognizedPathException. Then I cam across your blog. As per your recommendation, I have checked the entitysetname via the Metadata browser tool and the entitly set name is mk_loyaltymemberships. I have then set the regarding field to mk_loyaltymemberships(GUID). The error "Unable to process template language expressions in action 'Create_Note' inputs at line '0' and column '0': 'The template function 'mk_loyaltymemberships' is not defined or not valid.'." is now being obtained.
    Is there anything that I have missed out?
    Thanks in advance.
    WY

    ReplyDelete
    Replies
    1. Hi WY

      The EntitySetName and the brackets need to be set in the input of the column (as in the first image below). If you use Expression to set the field with EntitySetName (as in the 2nd image below), you will get the following error .

      https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEic5qJ9fSZBPij3cmzlQrBQvCX6Pgo9grGOgDx0Zg1wQgeSsehOIMpOb-SrTbvhXFaDsfzwxIm5GOA-SClnC7oaOb8xS_0VnXrXOmKur3Oa9HqrI7GeYNIZJkhvkYZOyri3CgvDa-i1t6f3ghBLcBWqJQWonNvegNxrSkRImzSRHx8YPsgF_HTVuDVlNiDK/s993/Adding%20EntitySetName.png

      Delete
  14. Thanks so much for the information in your blog. One more thing to add is when your custom entity ends with an S, e.g. tomateos, then EntitySetName becomes tomateoses!

    ReplyDelete

Post a Comment