In my previous post, i have explained Filter query in details. Now i will cover up some most useful filter query syntax examples.
Filter Query Examples in SharePoint Get items action-
- Working with dates: In filter query, date value must be in same date format of your column date format. you can check your column date format by simply running your flow. Once your flow run successfully, Expand your get items action and check output by click to download link. This will open a new tab where you can search your column name.
- In the above snap, you can see my overtimeDate format in ‘yyyy-MM-dd’ but if you see Created & Modified column name then you will see dates in UTC format. Here lots of user are doing mistake. they do not convert UTC format into required date format that’s why their filter query return blank items.
- To convert any date in required format we use formatdatetime() function in expression. For more details about formatting please check my this article.
Example-1 : Overtime date is less than today’s date.
Output– I am using select action to showcase our output.
Example-2 : Get current month overtime requests.
- I am using compose only for demonstration purpose. You can directly pass expression in your filter query.
startOfMonth(utcNow(‘yyyy-MM-dd’))
subtractFromTime(startOfMonth(addToTime(utcNow(),1,’month’)),1,’day’)
Output-
Note: In same way you can get items on weekly, quarterly or even yearly basis. Also, This kind of expressions are useful in building backup flow for SharePoint List.
Example-3 : Get items from Choice column-
- For choice column, Please use only its internal name. ColumnName/Value will not work here.
Output-
Example-4 : Using filter query for blank date column.
- For blank date column value, we usually think that expression null() will fetch all blank date column value but it will not work here. You need to simply type null in your filter query.
Example-5 : Filter query for Person field
- Though Filter query does not support Person column. But you can filter specific attribute of Person field.
- Use – PersonColumnInternalName/EMail, PersonColumnInternalName/Title
- EMail & Title are case sensitive
I will add more examples here. In case you have any filter query syntax related issue please mention your requirement in comment section.
Thank You!!!
-Gopenly
