Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

PowerApps automate expression to lookup a value in a Sharepoint List

(1) ShareShare
ReportReport
Posted on by 10

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:

 

setta_uk_0-1719846571086.png

 

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:

 

setta_uk_1-1719846844807.png

 

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:

setta_uk_2-1719847514449.png

 

Is such an expression possible?

 

 

Categories:
  • setta_uk Profile Picture
    setta_uk 10 on at
    Re: PowerApps automate expression to lookup a value in a Sharepoint List

    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!!

     

     

  • creativeopinion Profile Picture
    creativeopinion 10,109 on at
    Re: PowerApps automate expression to lookup a value in a Sharepoint List

    @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 

    creativeopinion_0-1720794456907.png

    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.

     

    creativeopinion_1-1720794581568.png

     

     

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

    creativeopinion_2-1720794581583.png

     

    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.

     

    creativeopinion_3-1720794581864.png

     

     

    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.

     

    creativeopinion_4-1720794581584.png

     

     

    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. 

     

    creativeopinion_5-1720794581570.png

     

    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 Profile Picture
    setta_uk 10 on at
    Re: PowerApps automate expression to lookup a value in a Sharepoint List

    Hi,

    Some further screenshots from troubleshooting the flow:

     

    setta_uk_0-1720790287383.png

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

     

     

    setta_uk_1-1720790496189.png

     

  • setta_uk Profile Picture
    setta_uk 10 on at
    Re: PowerApps automate expression to lookup a value in a Sharepoint List

    Hi @creativeopinion ,

     

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

     

    setta_uk_0-1720779947857.png

     

     

    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?

     

    setta_uk_1-1720780051131.png

     

    The full expression is :

     

    outputs('Get_items')?['body/value']?[0]['ID']
     
     
    Hope you can help, thanks!
  • setta_uk Profile Picture
    setta_uk 10 on at
    Re: PowerApps automate expression to lookup a value in a Sharepoint List

    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

  • creativeopinion Profile Picture
    creativeopinion 10,109 on at
    Re: PowerApps automate expression to lookup a value in a Sharepoint List

    @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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard