Filter Query for SharePoint Get Items action in Power Automate MADE EASY!

leveraging power automate to filter data from SharePoint lists or libraries requires us to plug in or data filter criteria which could be a challenge for citizen developers imagine a scenario in which we get a GUI like experience wherein we can pick our columns Define our criteria and auto magically it creates the odata filter criteria for us that's exactly what I will Showcase in this video so let's check it out in action [Music] I have a manually triggered flow that gets items from a SharePoint list the list has a wide variety of column types from text to numbers dates lookups and more I then create an HTML table based on the output of the get items action and that HTML table is what I send out as an email the email sends me a table of all the rows from that SharePoint list and the columns are the ones that I selected and that HTML table action for the get items action under show Advanced options we have filter query this is an odata filter query that allows us to perform filter queries server side in this scenario the query will be performed on the SharePoint list or Library depending upon the action that I'm leveraging get items as for lists get files is for document libraries perform filter queries we need to learn the odata filter query syntax to simplify this process there is a new experimental feature that provides a GUI like experience for creating these filter queries to enable this I will head over to settings go to view all power automate settings and turn on the experimental features and click save once this feature is turned on for the get items action under show Advanced options the filter query dialog experience becomes citizen developer friendly I can select a column from my SharePoint list I'll start with the title column filter the items in my SharePoint list if the title is equal to I will take this specific item which is ac7320 and I will enter the value associated with it I will go ahead and test my flow the flow has run successfully and the email that I have received only includes that specific item from my SharePoint list if you would like to look at the actual odata filter query you would need to switch to advanced mode and here is the syntax title equals in single quotes the value I will switch back to basic mode I can also perform a starts with operation filter all the items from my data source where the title starts with AC I have two records that match that criteria when this flow runs here is the output that this filter query action has generated it provides those two records another common scenario is to check if the text column contains the text for that we would need to type the odata filter query substring of the text that you would like to search in single quotes comma the internal name of the SharePoint column that you are trying to search in in my case my column internal name is title these two items contain 0 0 in the title I'll run this flow the output that I receive includes the items from my SharePoint list where the title contains 0 0. to get the internal name of your columns in your SharePoint list or Library experience if you simply go ahead and sort on your columns the URL at the top will have an attribute called sort field is equal to this would be the internal name of the column I will search for the word surface on the model column I will test this flow and here is the output of the filter query action I get four items where the model contains the text surface now the supported odata query operations for SharePoint are as follows and specifically focusing here on the filter query action we can use less than greater than equals not equals and more now the filter query action The Columns that it lists out here are typically text columns date columns and number columns so as an example if I pick the due date column the comparison types I can perform I have more options equals greater than greater than or equals and more Choice columns status in my list as a choice column so if I need to filter my SharePoint list where the status is in use I need to ensure that I have the internal name of this column so I will just sort and I will pick the sort field internal name the status column doesn't list out out here so what I can do is I can add a custom item Photon status and say status equals in use I will test this flow the result of that filter query action are three rows from my SharePoint list where the status is in use for lookup columns in my example I have another list called asset types where I'm maintaining the type of the assets and in my main asset manager list I have this lookup column that looks up to the title column of that list to filter the data based on all the assets that are of type laptop I will add a custom item the internal name of my lookup column slash the column that I'm looking up to which in my case is the title column in my lookup list so if this is equal to laptop I'll test this flow the output of that action are three records from my SharePoint list that are all of type laptop we can apply multiple filters for the filter query action as an example I will add a custom item for it put the internal name of my column status equals in use and the purchase price is greater than or equal to 300. let's run this flow the output of the multiple filters that I performed is as follows I have two records in my SharePoint list where the status is in use and the purchase price is greater than 300 dollars person type column current owner is a column of type person or group my filter condition get all the items from the asset manager list that belong to Reza in my filter query action it will not list out the person column I will go to add a custom item and paste the internal column name person column is a complex type column and SharePoint I'll put a slash and I would like to search based on the email property e m both uppercase a i l lowercase if that is equal to in this case I'll plug in my email address and this I can also get from Dynamic content I will bring it from the email address of the user who is triggering this flow so let's go ahead and test this the output of the filter query is a single Row from my SharePoint list since Raza is the owner of only one item let's take the person column again get me all the assets where there is no current owner assigned the formula for this would be current owner email equals simply type in null make sure you go to switch to advanced mode you see equals null it puts these single quotes next to it so it is treating it as a string so this is something that you have to be careful so I will simply change this to equals null and I will test the flow the output are three records from my SharePoint list where there is no owner defined in the order by property of the get items action I will simply put the internal name of the column I would like to sort on by default it will sort an ascending order if you want to sort it in descending I can type in desc I'll sort it in ascending test the flow the data is sorted based on the model column in ascending order now we can make the filter query conditions and the values we apply for filtering dynamic let's say I need the items that are being owned by my manager for that we have the get manager action from The Office 365 users connector so whose manager are you looking out for the current user who is running the flow in the filter query action current owner equals the mail property from the get manager action in my scenario I have set SATA as my manager and active directory my manager which is Sarah I only have one row in my SharePoint list the filter query criteria current owner equals let's say user one reports to me I'll add another row current owner email equals let's say user 2 user 3 user 4 and so and so forth and all of these I would like to run in an or data operation because I need all the items belonging to any one of those users however here I have hard coded how many users are reporting to me if I switch to the advanced mode we can see the format that it expects current owner email equals the email address of that user this in Brackets then a space and or condition a space and then my second criteria now this entire criteria is what I would like to create dynamically based upon the users who report to me for that I will Leverage The get direct reports action from The Office 365 users connector so get me all the users who report to me in active directory I will add an action I will use the select data operation action so select from get direct reports Dynamic value I will choose is value and for the map property I will switch to text mode remove all this code and map it to the dynamic value mail coming in from the get direct reports action so I have the email that is this portion but I need the remaining part of it as well so I'm simply going to copy this and paste it right here and where I have this user 1 I will remove this and right in between those two double quotes I will paste that Dynamic value mail and this entire query I will put it under double quotes and all I have to do now is join them with the space or space text and to do that we have a join data operation action so join from the output of the select action and join this with space or space join will contain that entire odata query and for my filter query condition I need to make sure I am in the advanced mode the odata filter query will come dynamically from the output of the join action Let's test this flow in my active directory James and Sarah report to me in my SharePoint list there are items that are owned by James and Sarah and these are the two items I only get those records that are owned by users who report to me in active directory filter the data where the due date is less than today filter query I will pick the due date column is less than to put today's date from expressions I will leverage the function UTC now Let's test the flow I am recording this video as of the 21st of October 2022 so it will only return those items where the due date is less than October 21st now to get all the records where the due date is in the month of October I need to filter based on a date range and the ranges get me all the items where the due date is greater than or equal to the 1st of October and the due date is less than 1st of November due date greater than or equal to the first day of the current month that is the month when I'm running this flow I will head over to expressions and there is an expression function called start of month and all this needs is the timestamp for which you need the first day of the month UTC now will give me the current date and time when this flow is running so this will give me the first day of that month and so I'll add Arrow the due date is less than the first day of the next month now to get that I will simply copy this expression to get the first day of the month and before get items I will add an action which is add to time it's a date time action the base time I will plug in that expression which gives me the first day of the current month and to this I will add one month so this will go a month ahead and the output of this is what I will plug in to my second criteria which is due date is less than the dynamic value calculated time coming in from the add to time action Let's test this flow and the output if you enjoyed this video then do like comment and subscribe to my YouTube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 64,281

Keywords: Power Automate ShaePoint List Filter Query, filter query power automate, filter query power automate syntax, power automate odata filter query, odata, filter query, filter, query, power automate, sharepoint, flow, flow filter query multiple conditions, flow filter query person field, flow filter query greater than, flow filter query, flow filter, flow filter date, reza dorrani, filter query choice column power automate, filter query date power automate, filter query date range, person

Id: I8FdUmECAn8

Length: 16min 57sec (1017 seconds)

Published: Mon Oct 24 2022