“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’], ‘)’)
)
Sir you are a lifesaver. Thank you so much.
Great Post Tom, concise and right to the point,
Many thanks
Thanks for the post. It gives me a start on how to deal with lookups.
This syntax works for inserting the record to the table, correct? I am struggling to inserting record with lookup column with this syntax and keep receiving the error of below.
{
“error”: {
“code”: “0x80060888”,
“message”: “Bad Request – Error in query syntax.”
}
}
Syntax used are, there are two lookups, below
/cr1da_projects(‘********-0218-ef11-840a-002248e87f0e’)
/cr1da_members(‘********-f102-ef11-9f89-6045db64ab9a’)
Please help
If the logical name of the lookup table is singular, you will need to make it plural when trying to update the lookup column in Power Automate. For example:
“systemuser(00000000-0000-0000-0000-000000000000)” does not work, even though the table properties say the logical name is “systemuser”.
Instead, you make it plural:
“systemusers(00000000-0000-0000-0000-000000000000)”
Changing it from singular to plural was the one for me. Thank you Bob you handsome man you!
Great post tom really worked. I was struggling a lot for this but your post helped. Thank a lot!
Good Day, how would I update the “/systemusers(‘@{outputs(‘Start_and_wait_for_an_approval’)?[‘body/outcome’]}’)” when I update a row in a selected environment?
I am trying to update the table when an approval has been completed using the outcome from the approval. It keeps saying: Bad Request – Error in query syntax.
The output from the previous step is: /systemusers(‘Kyle User’)
Hello Kyle,
the first part is the table you’re updating, the part in the brackets is ID of the row from that table that you want to store them. If you’re updating a user then it must be /systemusers(userGUID), not a string with task outcome.