Have you tried the experimental version of Filter query in the SharePoint Get Items action in Power automate yet?
Filter Query in Get Items
In this post I will look at the experimental Filter Query feature and how you can make use of this in your production environment. I hear you! Experimental features in production? Of course not!
A while back I wrote a post about filter queries in the Get Items action. It is one of the most popular posts on my blog with about 400 views every day. this shows how many people struggle to get it right.
Microsoft have realized this and they are working on an easier to use filter query settings. You can already use this by enabling the experimental features.
You can enable these from the settings in Power Automate. Just click on the View all Power Automate settings.
And you will get the following dialog and all you have to do is toggle the experimental features on.
Before you do this, do make sure that you have saved your flow first!
Then you can now reload your flow or create a new one and you will notice straight away that the get items has a nicer looking Filter Query there.
Quite quickly you can now add the options that you need. in your expression.
And before you know you have multiple filter queries configured with some boolean logic. It is even easy to make the queries a bit more complicated. As shown below I have 3 separate part in my logic.
How many times have people contacted me with a query like this
(....) and ( ... ) and (...)Well it will not work, why not? Well we will see this very soon.
Time to switch the experimental features off. Again make sure that you first save the flow! En when we then reload our flow from before we get given the filter query that we need:
The boolean logic has the right brackets. Did you know that and and or can only handle two parameters? Well this is why it failed so often in the past. Also That ugly internal fieldname in SharePoint, I didn’t have to type that all in. Or how about getting the startswith syntax right?
I wouldn’t use experimental features in a production environment, but temporarily using them might just about be very helpful. One word of warning please do create a backup of your flow first!
Thanks for elaborating on this!
It might come in handy (if I don’t forget about it when needed ;-))
It is especially good to know about the (max) two parameters to AND and OR!
I may have stumbled upon it in the past.
Thanks for your post. My filter query with 2 params using AND used to work but no longer do so. I had to get rid of one param and then it worked fine again. Will try out the expt. feature. thanks.
You are welcome.
Will this work if we have items more than 5k ?
More than 5k items in the list should be ok, more than 5k items in the results will not work.
What I’ve been stymied by is a list where the item might not exist. I can filter on the primary key field, but when the record isn’t there (very possible on the business logic) the flow wants to stop. I can’t test length to see if I got a hit, it’s already aborted. I can build an OnError follow up to set the record found to no, but I’d have to bend over backwards to let the logic continue as if nothing wrong happened.
Is there an equivalent to just test if the record exists before proceeding with get item or items?
Yes I would check if an item exists first ( using a get items). If you use the run after settings you will find that there are failed actions, which I try to avoid where possible.
What I settled on was a get items with a greater or equal than filter, a top count of 1, and a test both for the length of 1 vs null and of the returned first key of equal my query key or not. That avoided the loop through. Thank you for a very fine site full of excellent advice.
How do you ‘finish’ entering an expression? There’s no ‘update’ button or anything like that. Sorry if I’m being a bit thick!
I assume that you have the experimental features enabled as it isn’t as clear when you use those. You can type the expression and then close the expression editor either by closing that dialog or clicking on the action that opened the expression editor.
I’ve tried clicking the X in the top right corner of the expression editor, and clicking back on the action, but neither seems to work, perhaps I’m missing something obvious? I did a quick recording here if you wouldn’t mind taking a look: https://photos.app.goo.gl/48RyqjoWTXN5WnJGA
In the experimental version I actually do think that the expression was added. when you go to the little box after closing the editor and hit the up and down arrows you will find the expression. Not great! but still working-ish.
UPDATE: after turning off experimental features, I found that the expression had in fact been entered multiple times! So it’s a browser UI issue rather than a PowerAutomate issue perhaps? I think I’ll stick with the non-experimental version for now… but thanks for your help!
I find that at times I also don’t have the update button, which I ascribe to the UI getting confused. Saving the flow, closing that tab, and opening fresh again from the bookmark to edit my flow once more clears that up and the update button shows again.
That doesn’t sound right.
Hi Pieter,
Thanks for the information. Do you know how to force case sensitivity in the filter query?
Thank you so much.
Holy sh%$%#%#&^&! You save my life.
Great work!!
I’m having trouble with a field name that is 2 words (Expiration Date). What is the syntax to use? I’ve tried [Expiration Date] and no container and neither works.
Hi Lisa,
You will need to look at the internal field name. You can find this by going to the SharePoint list. Then i the list settings find the column in question and the url should include the internal field name.
Any thoughts on why my ‘Calculated’ column in SharePoint does not show up in my Filter Query column lists? I turned on experimental and can see all the other columns in my list but not the one I need.
Calculated columns cannot be used in filter queries. You would have to collect all items and then use a filter action to do the filtering.