Hello,
I need help fetching a singular record from a table to use in a lookup field on a parent table...
I have a table of approvers, with the columns:
Approver ID
Approver (user lookup)
Department (choice)
Title (choice)
There is only one approver for each Department (ex. accounting) with a given Title (ex. manager)
Based on values in the Parent table, I want to auto assign an approver based on the title and department, on a row in the Parent table that is a lookup field.
I essentially want to do a "get" call on the table to fetch a record based on a specific Title and Department (which should never return multiple records, since they are unique combinations of Title and Department)
The closest I can get is with the Dataverse List Rows function, but this returns a table and automatically applies an Apply to Each on the record I want to update with the approver I retrieved, even though based on my filtering it should be a table of 1 record.
Is there a way to get just the one record so that I can use it in the Dataverse Update Row function in lookup field to the Approver table?
Alternatively, if I could just retrieve the Approver ID, I could use that to populate the lookup field in the Parent table.
This worked! Thank you!
For anyone else with this issue, or myself in the future when I forget how to do this: This video gives a visual on how to get the record by itself, as well as how to work with lookup columns in power automate
The output of the List Rows is already an array and you should be able to access the fields using the first() function without having to do a Parse Json. You will need to type the JSON fieldname into the expressions tab manually. Using the dynamic content will add an apply to each loop. But the field name is the same as what you would get inside the loop.
If I save the output of the List Rows into an array, do I use a Parse Jason action to use the first() function?
The easiest way is to either do a list rows with an appropriate OData filter or to do Filter Array after you get all the rows. Then you can use the First() function to get the first row of the array that is returned. If you only need the value from one column you can append that column name to the end of the First() funtion in JSON and it will return just that one value. Something like this
first(Outputs('List_rows'))?['Approver']?['Email']
Michael E. Gernaey
179
Super User 2025 Season 1
David_MA
161
Super User 2025 Season 1
stampcoin
143