“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?
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.
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'), ')'))
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.
Getting a 401 error when trying to go to the URL of the environment.
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.
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’], ‘)’)
)