PowerApps automate expression to lookup a value in a Sharepoint List

[setta_uk]

Hi,

I am trying to create a flow to update an item in SharePoint list, to locate the correct SharePoint item I need two values (ID & Request ID) as shown below:

ID is the automatically generated unique ID every time an entry is added to the SharePoint list.

The Request ID is another unique identifier created for each entry.

Below is the SharePoint list with the two unique identifiers:

I need an expression to lookup the value of the "Request ID" and then obtain the corresponding value in the "ID" column

e.g. if the Request ID value is IND-X54, I need the expression to look this up in the SharePoint list then obtain the corresponding "ID" value, in this case "452"

Please see below illustration which I hope explains it better:

Is such an expression possible?

[creativeopinion]

@setta_uk You need to use a Get Items action to return the items from the list you are looking up. Then you can use a filter query to look up the ID base on the Request ID. 

You will need to use the internal column name of your Request ID column. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren't sure how to get the Internal Column name, you can refer to this section of one of my YT Tutorials.

Not sure how to write a filter query? Check out this YT short.

It's important to note the Get Items action will always return an array of items even if it's a single item. To avoid your Update Item action from nesting itself inside an Apply to Each action, you will need to use an expression. In the tutorial linked below, I'll cover how to avoid the Apply to Each action when returning dynamic content from a single item in an array. 

3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

In this video tutorial I’ll go over how to avoid these common mistakes when using the Apply to Each action in a Power Automate flow:

1️⃣ Looping through a Single Item

2️⃣ Creating Unnecessary Nested Loops

3️⃣ Looping through an Unfiltered Array

At the end of the video I share a few helpful insights when it comes to using the Apply to Each action in your flow.

IN THIS VIDEO:

✅ How to avoid the Apply to Each action with a single item array

✅ How to use the item() function to access dynamic content in an array

✅ How to prevent unnecessary nested Apply to Each action loops

✅ How to use the Select action

✅ How to convert an array to a string with the Select action

✅How to use the Filter Query field

✅ How to count the number of items in an array

✅ How to use a condition control

✅ How to use the concurrency control

✅ How to set a top count

✅ How to use Compose actions for troubleshooting

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[setta_uk]

Hi,

Many thanks for the detailed response! I shall have a go at using the Get Item action as you suggested, I'm on annual leave though tomorrow so probably won't be able to work on this until I get back but will get back to you on how I get on! 🙂

Thanks again

[setta_uk]

Hi @creativeopinion ,

I've created a Get Items function which seems to work.

Immediately after that I have created an "Update Item" and have tried to create an expression for the "Id" field but it does not work. Do you know where I am going wrong with this?

The full expression is :

outputs('Get_items')?['body/value']?[0]['ID']

Hope you can help, thanks!

[setta_uk]

Hi,

Some further screenshots from troubleshooting the flow:

It seems it's not pulling the array from the Get Items? The Get Items operation seems to be successful though.

[creativeopinion]

@setta_uk The ID column in your SP List is a unique number. The dynamic value you are passing is a string and will never match an ID in your SP list. Refer to the screenshot you uploaded with your original post 

You will need to use the internal column name of your Request ID column. Keep in mind that the internal column name may not always match the name displayed in your Sharepoint list. If you aren't sure how to get the Internal Column name, you can refer to this section of one of my YT Tutorials.

Not sure how to write a filter query? Check out this YT short.

Return Item Count

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Review the outputs of the Compose action to ensure the number of items being filtered out is what you are expecting. If it's not, you'll need to check your filter query.

Condition Check

Add a Condition action to your flow. If items have returned (aka there is number stored in the Compose action—that is not equal to 0), add the rest of your actions to the Yes branch. If not, do nothing.

Since you are using a person column (Team Member) the Get User Profile (V2) action isn't required. When you use the person column, there is a lot of dynamic content available to select from. Use the Email dynamic content for your person column to return the email address of the user assigned to the SP item. 

Note: My person column name is Person. You will need to select your respective person column email dynamic content.

You might also find this YT Tutorial helpful if you ever need to send multiple items in a single email:  How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

IN THIS VIDEO:

✅ How to send multiple list items in a single email with a Power Automate Flow

✅ How to create a dynamic date range

✅ How to use the Convert Time Zone action

✅ How to use a Filter Query in the Get Items action

✅ How to count number of items in an array

✅ How to use the Select action to extract a users display name and email address

✅ How to create a unique list of email addresses

✅ How to use the Create HTML Table action

✅ How to customize the HTML Table with CSS styles

✅ How to use the Send an email (V2) action

✅ How to use the Append to String Variable action

✅ How to create a custom list of items for an email

✅ How to use the Send an email (V2) action

✅ How to display singular or plural text based on the number of items returned

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[setta_uk]

Hi,

Many thanks, all fixed now.

I misunderstood the logic of the Get Items and 'eq', I thought it behaved in a similar way to vlookup in excel where it would cross reference a value in one column and pull the corresponding value in another column, my bad.

I see now it's much simpler than that!

Thanks again!!