Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

Lookup to SharePoint list with approvers in Power Automate

Posted on November 22, 2020March 15, 2023 by Tom

“I am building an approval flow in Power Automate, and need some conditions at the front to know who should get a task – I need to lookup approvers in another SharePoint list.”


When building approval flows in Power Automate, there’re many good reasons to keep the approvers in a configuration list. It can be different approvers for each user, or country, or request value. But it means you must get the approvers from a different list. You need to implement a lookup. In Power Automate it’s done using the ‘Get items’ action with ‘Filter Query’.

Create a configuration list

Configuration list a simple SharePoint list that can contain just 2 columns. One column to define the approver, and another column to define when is the approver needed. In this example it’s a specific approver for each user, e.g. the user’s manager.

Implement the lookup

Add the ‘Get items’ action into your flow. You want to get data from the configuration list with the approvers, but if you don’t add any ‘Filter Query’, it’ll return all items from the list. That’s not what you want, you need only one result = one item with the approver based on the lookup. You can even limit the ‘Top Count’ to 1 if you’d like, but it’s not necessary.

Power Automate lookup for user

Note: Make sure that there’s only 1 item in the configuration list for your filter. The column you’re using must be unique. It can be user, it can be country name, it can be request type, but it must be only once in the list.

The filters might differ based on your needs. You can lookup a person, text value, or a number. It’s even possible to use multiple filters, e.g. person AND number.

Using the lookup result

When you use a value from the ‘Get items’ later in the flow, Power Automate will automatically add ‘Apply to each’ action around it. That’s completely fine. Since the action will return only 1 item, it’ll run only once. It doesn’t look elegant, but it works as needed.

Power Automate using 'lookup' item

Summary

To lookup approvers in Power Automate, you need two things: a configuration list with the approvers, and the actual lookup via ‘Get items’ action. Make sure that the lookup property is unique, that there’ll be always exactly one configuration item for an approval. Then you can use the approver from the configuration to assign him the task.

Just be careful about the common mistakes using the Filter Query.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

1 thought on “Lookup to SharePoint list with approvers in Power Automate”

  1. Hubert says:
    March 15, 2023 at 10:49 am

    SharePoint Configuration lists are so useful! It really ought to be plugged a lot more! 🙂

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes