“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.
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.
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.
SharePoint Configuration lists are so useful! It really ought to be plugged a lot more! 🙂
I created a list of approvers and their email address. But I want to each one to get an email. The Filter Query is the issue. Not sure how you get the field Username how and from. I searched the search and expressions and it didn’t show anything relative.