Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to update Dataverse lookup column with Power Automate

Posted on November 16, 2022November 16, 2022 by Tom

“I always get some URL error when trying to update Dataverse lookup column in a Power Automate flow, what does it want from me?”


Lookup columns are the more complicated ones to work with, no matter what datasource you use. Let it be SharePoint or Dataverse, updating lookups can be challenging. In SharePoint you must get the id of the lookup item before you can update it, and in Dataverse it’s similar, yet a bit more complicated.

Additionally to the unique id of the lookup row you’ll need also the lookup table entity name. How do you get it?

Power Automate dataverse lookup update

Get the table entity name and update the column

Firstly, get the url to your organisation environment. Add the ‘List rows’ action followed by a ‘Compose’ and store the ‘value’ output in the ‘Compose’ action. The reason is that the ‘List rows’ action doesn’t show the JSON it returns directly.

Run the flow and check the ‘Compose’ action in the run history. The second row will contain the url to your environment, copy it up to the /9.1/ part.

Enter the url in the browser and press Enter, it’ll show you a list of all the tables.

Go to the table with the lookup values, open its ‘Properties’, and copy its ‘Logical name’.

Search for the logical name in the list of tables.

Power Automate dataverse lookup update

Once found, copy the whole ‘name’ value and use it together with the row id to update the lookup field, e.g.

/cr09b_lookuptables(d663e4e5-d365-ed11-9561-0022489fdc7c)

And that’s it, you just updated the lookup field in Dataverse. Although there’s a small problem – such update will return an error message if there’s no value to update. So let’s continue.

Remove value from a lookup column

If on the other side the column already has a value and you want to remove it, use the null expression. Not a text, an expression!

Combine it together

Now you know how to update a value and how to remove a value from the column, and you can put it together in a single expression. If there’s a value use the value, and if there’s no value use null to avoid errors.

if(empty(<guid>), null, '/<tableName>(<guid>)')

Since the <guid> will be probably a dynamic content, you’ll have to combine it with the concat(…) expression.

if(empty(<guid>), null, concat('/<tableName>(', <guid>, ')'))

In the end it might look as the example below:

if(empty(outputs('Compose_2')), null, concat('/cr09b_lookuptables(', outputs('Compose_2'), ')'))
Power Automate dataverse lookup update

Summary

It might be a challenge to update Dataverse lookup column with Power Automate as it has such a specific format. Additionally to the row id you need also the table logical name in plural – something that’s not visible anywhere in the user interface. But with the right url you can easily find it, and use it for the update.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

3 thoughts on “How to update Dataverse lookup column with Power Automate”

  1. Oliver says:
    December 19, 2022 at 6:43 am

    Getting a 401 error when trying to go to the URL of the environment.

    Reply
    1. Tom says:
      January 9, 2023 at 3:47 pm

      Hello Oliver,
      I’m not sure what type of permission do you need on the environment, I’d try to contact the environment admin to call the url address to see if it’s a permission issue or if the url is wrong.

      Reply
  2. Jochen says:
    March 24, 2023 at 10:19 am

    my learning regarding “remove value” = in my case listrow action returned not empty but 00000000-0000-0000-0000-000000000000′ for the column so therefore i had to add a second if condition:

    if(or(empty(body(‘Parse_JSON’)?[‘_objectid_value’])
    ,equals(body(‘Parse_JSON’)?[‘_objectid_value’], ‘00000000-0000-0000-0000-000000000000’)
    )
    , null
    , concat(‘/accounts(‘, body(‘Parse_JSON’)?[‘_objectid_value’], ‘)’)
    )

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes