Using OData to filter date in Microsoft Power Automate

[Music] hello it's almost a year that i posted a video on youtube about how to use odata inside power automate today i want to show you how you can use odata inside power automate to filter a date field we even take it to the next level and we use date range considering that it has always been a question for many of my students that they were asking ali how can we use date to filter considering that when you write the odata query it is just the string so basically that there is no date data type now let's get into it and see how we can handle that let's start by understanding what we are going to build i already created a list inside sharepoint this list has only two columns we have title which has some dummy values and another field called record date which is actually a date field with some dummy values starting from january 11 2021 to april 29 2021. in this video we would like to filter these values for the records that their date is between march 15 and april 15th so basically out of the records that we have there we should get an output like this three records march sample one april sample one and april sample two these three records fall within the range of the filter that we define here of course i wanna do it using odata filter and that odata fielder you guessed it right we want to use get items inside microsoft power automate you may want to pause the video for a second take a quick look at it make sure it's all clear then we go to power automate and start building this this is the sharepoint list that i have it's inside the website called power automate workshop and i want to go to flow.microsoft.com you log in and we start creating our flow so click on create and i want to create a manual flow scroll up instant cloud flow and manually trigger flow i give it a name as date filter demo i click on create and it's created for this one i'd rather pick up two inputs and they're gonna be date first one is gonna be start date and another input again date it's gonna be and date we are good so when the user starts the flow we actually prompt the user to enter two dates one for the start and the other one is end then we want to get those values and use it as filter to filter the list that we have inside sharepoint now in this video i not only want to tell you how you can do that i also want to tell you how you can find the way to do that to start with i need to understand how power automate deals with the date fields easiest way i go to my magical control compose i click on it and i just get for example start date and i just run a quick test manually trigger a flow test start date let me get to day date and end date it doesn't really matter but it's required at the moment i click on run flow and done let's see what we got here you see we get year month day so when we are talking about the date inside power automate if we just use the string value of this date and we try to sort it alphabetically it actually sorts from the oldest to the newest correct right with that behind us now we can easily go there and implement the get items so i still keep this here because i may want to use it later on i go to new step and i say get items make sure you look under sharepoint and i will use get items remember get item does not have the odata filter the site that i want to pick is going to be power automate workshop again if you don't have it in the drop down list just click on the custom and copy and paste the url here list name i pick up the list name it is called date filter demo which i just created limit entries to folder no i don't want to include nested items i don't i come back here now i need to put my filter here the filter that i want to put i need to put the field name here the field name is record date so what i do here i just type in the record name because there is no space it doesn't include any special character i say record date is greater than single quote and i can simply get the start date from here let's see what we got record date is greater than start date let me just save it and quickly test it test manually trigger the flow continue and start date i want to pick for example february 15th they did as a matter of sylvia are not dealing with it run flow done and let's see what we got if i come here i can click on download and it's a big mess no worries i copy all of that and i just bring it to the code beautify i paste it here and i say tree viewer the body that i have has four values one two three four the first record that i have the record date is march 17. second is gonna be april april again and finally april again so we are good we actually got four records and we could easily filter it now if you want to add the second condition i need to go back here and edit it and i can say and again record date is less than again single quote i mean in between them i scroll down here and i pick up and date there we go just make sure it inserts it in between the single quote assume that they are string and you can easily work with it so if i go back here i can save it again and i can run it just to make it a little bit easier i go to data operations and i pick select but everybody who has already taken my power automate expressions course they are already familiar with the select for the ones who are not who are new to this concept select basically gets one format of json and converts it to another one at the moment the json that we got from get items looks like this even if i just beautify it it is quite weird because it has lots of unnecessary information while i actually needs two fields inside the body i just need the record date and i need the title so if i use select i can say only give me these two fields in the format that i want so i go back here and i say get the values from the get items value or the list of items i added here and i need the title field this is the property name in the outcome of this select statement so i can literally put whatever i want for the property names but i want the title of this produced json be the item which is the current item that is being processed for this get item values all right dot title i press ok button then for the other one i pick record date again i can put any title that i want here to replace the record date with a new one at the moment i just keep it the way it is again i go to the expression i pick the item dot record date just making sure that the names are correct record date and i come back here record date is correct right and that's it i can click on save and we can see the result let me just test it manual i pick the dates from march 15 to april 15th and i click on run the flow and done the flow is completed successfully and if i go to the select statement i should see three records why ubc two records okay march 17 april 1st if you have march 2 17 oh the second record that i added here is april 18. so basically this is here by mistake this shouldn't be here the two records that they should come to the output result should be this regardless even if you are not using the trigger flow instead of these get items you can comfortably hard code the date in the format of year month day separated by hyphen so basically exactly this format let me show you this format that it says here year month and day all the dates when they are converted to string in power automate are ordered and formatted this way and you can comfortably compare them filter them and even sort them using whatever you want just like strings and that was all about it thank you for watching and i'll see you in the next video [Music] do [Music] you

Channel: Alireza Aliabadi

Views: 19,332

Keywords: odata to filter date power automate, microsoft flow, power automate, sharepoint odata, OData, Power Automate row filter, Flow OData, SharePoint Get Items, Get Items row filter, Get Items filter query, Alireza, Alireza Aliabadi, OData cheat sheet, Power Automate date range, microsoft Flow date range

Id: e3Pv8bw4Ma4

Length: 12min 21sec (741 seconds)

Published: Wed Jul 28 2021