Selecting a Single Row from Table

(0) Share

Report

Posted on by madi1

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.

Categories:

Building flows

I have the same question (1)

All responses (4)

Answers (1)

Sort by

Pstork1 69,387 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
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']

Was this reply helpful? Yes No
madi1 18 on at

Like (0)

Report
Copy link

Link copied!

If I save the output of the List Rows into an array, do I use a Parse Jason action to use the first() function?

Was this reply helpful? Yes No
Verified answer

Pstork1 69,387 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
madi1 18 on at

Like (0)

Report
Copy link

Link copied!

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

https://www.youtube.com/watch?v=EsRWGw-KYtA

Was this reply helpful? Yes No