Filtering Power Automate (Flow) “Get Items” with Filter Query
Microsoft Power Automate, formerly Flow, makes it easy to copy items from one SharePoint list to another – much easier than doing the same either manually or with Microsoft Access. However, the limit to the number of items that can be “grabbed” via “Get Items” is 5000 – perfectly in line with SharePoint’s list threshold. Additionally, if you’re looking to filter what, exactly, is copied from one list to the next, a Filter Query can save you a lot of work. But the syntax must be exact or the flow will fail. So let’s see how to do it!
If you would like to watch a short video on how to do the following steps please click here
Imagine you have a List with Budgeting Phase values – and you only want to copy some of those items to another list, based on the particular Budget Phase for each list record. First, you can capture that value in a Manual trigger, allowing the user to choose what records they wish to copy. This can be done with a Text input with a Drop Down list of options. The result will have this structure:
Once the person who triggers the flow enters this data, it will be captured in a Text field and stored for the flow to reference it later.
Next, insert your “Get Items from a SharePoint List” Action, and select the list from your site. If your connection is already established, these should be available from the drop downs.
Now, click on “Show Advanced Options” to set the Filter Query value. We will want our Budgeting Phase field to equal whatever the person who triggered the Flow selected, and Flow allows us to use that stored value we mentioned previously. Your Filter Query line should be:
Field_Name_In_List_You’re_Pulling_From eq ‘Text Field from the Flow Trigger’
The Text field – that stored data we talked about – will be selectable in Dynamic Content – it will appear as whatever you named it – in our case, “Budgeting Phase:” Important note: Even though you’re selecting this field from dynamic content, it MUST still be in Single Quotes, while the name of the field must NOT. The result will look like this in the Filter Query box:
You may be thinking, “What the heck is that _0020 nonsense in the middle there?” That is actually part of the Internal Name of the field, which you’ll need. You can find this by going to your list settings, clicking on that field, and looking at it in the URL. It still won’t be perfect, especially if it has spaces in it. But you can use a handy decoder to find the exact name here: https://www.url-encode-decode.com/ Just paste in your long URL from your field details page, and this will be the result:
Now all that’s left in your flow is to add your Create Items action. Because you’ve already filtered what you want copied, no conditional clauses are needed. You simply create items now, and only those that match your filter will be created!
Go forth and Flow!