Power Automate OData Filter Query flow for SharePoint list

hello everyone this is raza durrani today's video is all about using filter queries or data queries against sharepoint lists and libraries using the get items action or the get files action and flows there has been a nice recent update which allows us to build these filter queries quite easily however there are still certain complex scenarios in which you would have to understand how filter queries or data queries work i will try and cover a lot of those complex scenarios in this video so let's get started with the video but first my introduction [Music] now in this scenario i have a flow that is triggered manually and the flow has a get items action against my sharepoint list the sharepoint list is a list of students in my case that has various types of columns because i wanted to cover all the different column types as part of the filter query actions the list currently has over 5000 items and the reason why i specifically leveraged this list is to show what happens in scenarios wherein your lists are large so in this case my list is greater than the threshold list view limit and sharepoint which is 5000 now in my flow for the get items action if you go to show advanced options there are additional options there is the option of providing filter queries which are odata filter queries to restrict the entries returned that means you can query your data source and only get the data that you need based on your query condition in this flow once i get the items i'm using the select action to just select specific columns that i need from the data set that is retrieved then i leverage the html table action to create a table and finally i'm sending an email to myself with the results so if i run this flow so my flow ran successfully now my sharepoint list has over 5000 items now if i head over to my email you will note that in the email that i just received the count of the number of items is only a hundred that means in flow when you're executing get items action or even the get files action for that matter exactly same scenario for libraries if i leave all the settings as default it will only get me 100 items so what can i do now i can increase the top cop the top count property by default it states that it is all it will get you all the items but it is configured to only give you 100 items so if you need more records you can increase the total count number now in my case i know i have more than 5000 records so let's say i increase my total account number to 10 000. now if i go ahead and save and test this flow notice that as the flow ran it failed and the reason why it failed if you look at the error details it says that the attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator now in sharepoint online you cannot change the list view threshold limits if i go back to edit the flow what is the listview threshold that's 5000 that means i can only get a top count of 5000. and if i was to go and run this flow right now this will go and fetch the 5000 records from my data set because i am within the threshold limits of my list i have got my 5 000 items and i also get the email with my 5 000 records now how do i get more records what if i need more data from my sharepoint list now in order to do that you would have to go to settings and you can go and turn on pagination now here you can define a threshold of items now one thing to note about pagination is you have to set a number that's greater than zero and less than or equal to a hundred thousand so it can only paginate through a hundred thousand records now for users with standard licenses the threshold limits that they can set here is 5000 for users who have additional premium licensing they can increase the threshold limit up to 100 000. if you go through the documentation on docs.microsoft.com when you are paginating 5 000 items or a hundred thousand items and of course all of this depends upon your license now in my case let's say i have the standard license so i will of course change this to five thousands it will only still give me five thousand records so these are some important basics that are important to understand about the get items action that if you don't specify a top count it is only going to get you 100 records now think about this do you really want to get all the data from your backend data source from a performance standpoint it would be much better if i only get the data that i need now in most scenarios what folks do is they query their data source they get all the data like in my case i'm getting all the 5000 records and then they start adding conditions or filters to basically filter their data if you look at my data set maybe i'm interested in getting all the students where the student name is james mary now if i need filtered data sets as part of my get items action there is the filter query that's available right here but traditionally the biggest challenge of filter queries is understanding how o data filters are written now very recent update that was pushed out to flow is wherein the odata query process has been simplified for the sharepoint actions in order to get to that simplified gui like experience what you need to do is go to settings go to view all power automate settings and turn on the experimental feature once you turn on this experimental feature if i get back to my flow now and go to the get items action and go to show advanced options you will note that i have that same filter query action right here but this time i get this nice interface through which i can perform my odata queries so if my use cases get me only the student whose name is james mary i can go to select an item i have title as one of my properties title equal to and right here i've gone ahead and entered the student name this time if i run and test this flow as you can see the number of records that it returns is only one and this time when i receive the email i only receive one student and that is james mary that's because i just applied if filter query to get me that result now i could have achieved the same thing by adding a condition and checking to see if the title is equal to james mary or adding filter actions later on and returning only data where the title is james mary but the problem there is that i am adding additional actions in flow and the more actions i add to the flow the more number of api calls associated with my flow if you read about the request limits and allocations of flow every user depending upon their license gets a specific number of api requests that they can make in a 24 hour period important to understand that an api request does not mean a request against a data source even regular actions in flow for example my actions right here of selecting data or creating the table or just counting the number of records so i just printed here in the flow all of these are counted as api calls in the flow so you want to reduce the number of api calls or actions in your flow as much as possible and in order to do that the best option is that when you're querying your data set only get the items that you truly need and that's why filter queries are extremely powerful now with this new interface it is much more easier to build these filter queries if i go back to the filter query action and if i look at the columns i'm only seeing a certain set of columns from my data source i'm getting text columns i'm getting the standard id column and sharepoint date columns yes no columns now i do not see any choice columns lookup columns management or data columns there are still column types specifically the complex column types that are not available right here i'm hoping that those get added soon in future but we can still take a lot of help from the filter query that is put out right here and if you would like to see the actual filter query in action right next to the filter query here if i click on this option to switch to advanced mode it will actually show me the filter query that it executed at the back end title equal james mary title is the internal name of my sharepoint column now what's an internal name if i go back to my list settings every column that i create in sharepoint has an internal name associated with it and if i would like to see the internal name of the column so for example enroll date if i select this in the url right here you will see field equal to this what you see right here is the internal name of my column if i head back to my flow right now and if i switch back to the editor experience right here and let's say i pick enrolled date now i know this query is not correct because i'm comparing a date column against the name but if i switch over to advanced mode notice the internal name has been picked correctly it's enrolled date it picks the exact internal name so make sure that you are entering the internal name right here the if you're creating a filter query from this new experience it will automatically apply the internal name for you now i'll change this back to title equals james mary now let's say my use cases i want to create an additional condition i want my students where the name is james mary or the name is john patricia so if i need both these students how would i change my query you can go to and right here and you can add an additional row now when you add the row you can define whether these actions will be performed in an and operation or in an or operation in my case i want the results to be either of the two so i'm going to create an or condition and then i'm going to pick title equals john patricia now if i switch to advanced mode observe the query title equals in single quotes my text which is james mary or title equals and single quotes john patricia so if you want to create these complex type queries with ands and ors you can easily create them using this new interface experience but more importantly you can switch to advanced mode and even look at the actual odata query at the back end which is very helpful and important for you to understand so i've gone ahead and run this flow and if you look at the number of results that i get it's two because my condition returned two records from my data set also if i head over to my email right here notice i got those two items and they are james mary and john patricia so that's how easily you can add your filter queries so now let's cover all the different column types in sharepoint so let's begin with the text column of course that's title so title what are the different options i have i have equals and i have starts with that means if i go back to my data set i can even search for all my students whose name starts with the letter a or whose name starts with james i can easily create those scenarios right here so title starts with james and in order to look at the actual odata query if i switch over notice it says starts with title and the word that you're checking in my case james now if i go ahead and run this flow this will query my data source and get me all the students whose names begin with james and right here if you notice i have 14 students whose names begin with james and and here is the email as you can see i have multiple students that i created that have their their names on the title beginning with the word james now what if i would like to perform a contains operation in my title this interface just provides me two options right now equals and starts with and i'm sure more options will come in future however if i would like to perform a contains operation against my title column in sharepoint i can switch back to advanced mode now although the only options here i have is starts with an equals i can easily plug in my o data filter query right here so here's my filter query substring off the text that i want to check against the internal name of the column that i've provided right here so in this condition it will check to see if the title contains the word dor so let's go ahead and run this and in this scenario the email that i receive has 79 records and if you notice it returns all the names which contains the letters d o r right here see d o r d o r next scenario is around date columns now in the interface that's provided you have the option for selecting dates currently so if i pick enrolled date the condition has a lot more actions that i can choose from now in my case i'm going to query enroll date equals now over here let's say i want to get all the students who have enrolled today i am recording this video as of december 19th 2020 now if i would like to put a date in here you would have to go to the expression syntax and this is the new expression editor experience and the expression that we use to get the current date and time is utc now this gives me the current date and time now notice i entered the expression but i don't see it right here however if i head over to advanced mode i do see that expression right here now this editor is currently experimental there are still changes coming but it actually did apply that query and to confirm that i can always go to switch to advanced mode and confirm that the query is in place another thing to note is when you're working with dates and times and flows flow always understands utc times so when it's actually performing a query against my data source in this case it's sharepoint even though the dates and times and sharepoint when i view them the dates and times on sharepoint would be showcased based on my time zone however when i actually perform a query at the back end sharepoint still stores that information in utc format now in this case if i go ahead and run this flow if you notice the number of records that i got it's zero and the reason why i do not get any results here is notice when you use utc now yes it does give me today's date but apart from that it's also adding the time component to it when i'm querying my sharepoint column i don't need the time component to come along with it so what do i have to do in order to remove that so go back to the expression editor and use format date time provide the current date and time which is utc now and this i want to format as year month day that's all i have to do now if i watch to go ahead and run this flow this time i get three results because i have three students who have enrolled on today's date another case around dates is searching around date ranges or get me all the records where the date has been updated in the last x number of days so for that once again expressions is my friend i will go back to my expression so i will add another function called add days so add days to utc now comma how many days do you want to add now in my case i don't want to add days i actually want to go and subtract days because i want to go back in time i want to check all the records that have been modified in the last 30 days so i can plug in minus 30 year which actually subtracts 30 days and once i get that date i'm formatting it if i go back to my filter query it says enroll date equals the expression that i put in however i need all the entries that have been modified in the last 30 days so what i need to do is just change this filter query to greater than or equal to the query that i've plugged in and just to be sure that my expression is correct i will switch back to the advanced mode and right now as you can see it says enroll date ge that's the code for greater than or equal to the expression that i plugged in now if i go and run this this will return all my students who have enrolled in the last 30 days and as you can see in my scenario i have nine students who have enrolled in the last 30 days next scenario i have a column called active which is basically a yes no type column in sharepoint now for the yes no type column you have to check that value against 0 or one not true or false zero or one because that's how sharepoint internally stores it zero means false one means true so if i need all the students who are active i just have to plug in one if i need all my inactive students i just have to plug in 0. now if i look at the query right here it says active equal to in single quotes 0. that's the query now if i go ahead and execute this action it should return all the students where the active column value which is the yes no column is false so here's that filter applied in sharepoint as you can see get me all the students where the active value is false and if i look at my result set i get those same four records right here from my filter query let me go and change this to active one and now let's try and cover other column types right here now the next scenario is around a people picker column now right here i have a manager column of type person and let's say my use cases i need all my students who are active and their manager is sana now in order for me to achieve something like this if i go to add because i want to add another condition on top of this and this time i need the and operation because both have to be true if i start looking for the manager property i won't get that right here so what i'll recommend is this because i want at least the query to be formed go ahead and pick any column so in my case i'll just pick the title column and switch over to the advanced mode now the advantage is it it kind of gets me halfway there in the query right now of course the first part of the query is correct and the second part of the query is what i want to change now in my case my column and sharepoint the manager column the internal name is manager now manager is a complex type column it's a person type column if i want to query against this column and get only those students where the manager is sana what i can do is this manager slash email and please observe email with an uppercase e and an uppercase m equal to the email address of the manager and in my case i've gone ahead and plugged in the email address of sana so i've gone ahead and executed this and as you can see and it returned 2236 records because i have those many records whose manager is sana and they are active another common case is around empty values right so let's say i pick my manager column is empty so this is going to return all my students who currently do not have a manager and if i head back to my filter query right here if i would like to query and check to see all my students who are active and do not have a manager all i have to do in that case is this manager equal to null i'm not checking against an email because the column itself is empty and empty you do basically are null checks and as you can see it has returned me four students where the manager is null now i also have a number column right here called score now if i would like to get students who have a score what do i have to do then is the opposite so in that case my query is going to be score is not equal to n e null that means get me all the students who are active and they do have a score associated with them and once i run this flow as you can see i have 17 students who have a score associated with them now one thing to be aware of is if i switch to advanced mode and just come back notice this time it says not equal to null in single quotes now if i was to run this right with a null in single quotes and that's what the new editor experience does but if i was to run this notice that it literally returned all the records and the reason why it did that because it's comparing it against a null string so bear in mind that when you're comparing for empty cases you need to compare it with just null so if you look at region as my choice column there are four choices that are available now if i would like to compare against my choice column all i have to do in that case is this region the internal name of my choice column is equal to and i've plugged in the value of my choice so in this filter query it's going to get all my students which are active and the region is equal to north i have 1484 students who belong to the region north now i also have another column called class right here and class is a lookup column now if i would like to query against the lookup column so let's say i want to add another and condition to this go back to filter query add another row pick any column and switch back to the advanced mode so it adds that additional query so what i can do is this class is the name of my lookup column now lookup columns are special type of columns when you're looking up you look up to a different list and typically when you do a lookup you get two things you get the id and you get the value value is against the column that you choose when you're looking up to a different list in sharepoint id is the actual id of that value in that lookup list now in my case when i was when i created the lookup function for class i have another list called class and in there i looked up against the title column so what you need to do is this is provide the internal name of that column right here so in my case class slash title is equal to plus three so get me all the students who are active region is north and the class is equal to class three and if you look at this now i only get three students back because there are only three students in my data set who are active active is true the region is north and the class is class three if you would like to check against the id you can even do that you can just change this to class slash id equal to id is numeric field so all you need to do is just enter the respective id in your lookup list now what about scenarios with multi-select choices so in this case i have a subjects column and the user can make multiple choices right here as you can see i have multiple choices selected so for querying against the subjects column the internal name for me is subjects i'm going to pick subjects is equal to it's a choice column so i can directly relate to the column equal to the value in question so i'm going to pick subject english this will return all the students who have english as one of the subjects now if i do english and geography here in sharepoint and click apply this will return students who have either english or who have either geography so it's like an or operation but what if i want students who have actually selected both english and geography how do i do that all i have to do is add another and condition so in this case it's going to get me all the students who are active and have the subject english and have the subject geography and in this scenario i have 861 students now who have this combination of course if i go back to the interface what i can also do let's say i would like to check for students who have either english or geography in them if i would like to perform an or so what i can do in that case is i can make selections and put them in a group right so i've just grouped these and now i can put this in an or condition so this is an or condition and then this result is under an and condition with this query that i'm performing now if i go back to my advanced mode notice how it plugs the query so this will get me all the students who are active and they either have the english subject or the geography subject so you can leverage this interface here to create groups and then define whether those groups are going to execute in and or an or operator now another column type is multi-select lookup columns so hobbies is a multi-select lookup column and let's say i would like to get all the students whose hobbies are drawing and photography they have this combination in them they need to have drawing and they need to have photography and hobbies slash title remember it's a lookup column you have to use the slash id or the column that you're looking up against my case title equals photography and equals drawing now if i was to go ahead and run this flow you will note that i have only two students who have this combination now in my case i have a column called job title which is of type managed metadata now if i was to query against this column so let's say i need all my students whose job title is technical lead and if i plug in that query right here which is which is job title equals technical lead and if i was to run this query this query will fail purely because you cannot use taxonomy field types when you're performing filter query functions this is a limitation however when you create the taxonomy column in a sharepoint list or a sharepoint library there is another special column type created it's called it's a hidden column it's called tax catch all and what you can do is you can actually query against that column so instead of using the job title here i will replace this query with this tax catch all slash term that's the term value equal to technical lead now if i was to run this i get that one student who has that specific job title but if you have multiple taxonomy fields all of those column values for that specific record would be stored in tax catch all so there could be a scenario wherein you have two different taxonomies and both of them have the same terms then in that case this query might not give you accurate results so what you can also do is this tax catch all slash id for term wherein you can specifically plug in the unique id that gets created for each term which is a good so in my case i'm actually querying the same scenario the only difference is i'm querying against the id of the term and if i was to execute this query i would get exactly the same result a few more things is order by so if you would like to order your results by a specific column so fire the query get me the results and then order by a specific column once again enter the internal name of the column by default it will sort in sending manner if you want to sort in descending just enter the name of the column space d e s c so it will sort the results based on the order by column that you provide additionally to even enhance performance further if you're only dealing with a certain set of columns you can create a view in your sharepoint list and then just pick that specific view right here that ways you're only working with those column types so i hope everyone enjoyed this video if you did then do like comment and subscribe to my youtube channel and don't forget to hit the bell icon so that whenever i post my new video you get notified about it thank you so much for watching and see you in the next video

Channel: Reza Dorrani

Views: 124,695

Keywords: power automate odata filter query examples, power automate odata filter query, power automate odata multiple filters, power automate odata filter query null, power automate odata filter contains, power automate odata filter query not null, power automate odata filter query sharepoint, sharepoint get items odata filter query, flow odata filter query, flow odata filter query multiple values, flow odata filter contains, flow odata filter lookup column, power automate

Id: yeAnuTB85eg

Length: 29min 15sec (1755 seconds)

Published: Tue Dec 22 2020