Get the ID of the first item of a SharePoint query with Power Automate

The Case

I have a flow in Power Automate where I need to lookup an item in a SharePoint list based on two lookup columns (in this example, the two lookup columns are called Patient and Exercise). I know that there will be one and only one item in the list with the combination of Patient and Exercise.

The Solution

In Power Automate we need to use the SharePoint action “Get items” to be able to query the list for items based on the two input variables. To be able to address the lookup fields in the query, we add an “Id” suffix to the column name (the Exercise lookup column is referenced by “ExerciseId”). The filter query in this case is shown in the image below:

The “Get Items” action returns an array of results, so we need to pick the first item in the array. This can be done by using the “Compose” action. This action can read the result from the previous action and we can compose a new output. In this case we update a variable with the output. We use the first(…) function to get the first item (Value), and we pick the ID use the following expression:

first(body(‘Get_items’)?[‘Value’])?[‘ID’]

The image below shows the details:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s