How to get list items for this quarter in power automate using OData

[Music] hello i'm pretty sure if you have used sharepoint get items not get item sharepoint get items action inside powerautomateflow you have seen the warning that it asks you to set up an filter it gives you a warning not the error so still although you see the warning even if you don't do anything about it the action works and does its job so naturally since that error is there everybody's attention is towards all data and people realize that there is a good way to filter the content that we are getting from any list including sharepoint lists using odata and it makes the workflow quite efficient now considering the weird data types that we are dealing with with different data sources always date is the black sheep in this video i want to show you how you can uh you have already seen how to use the o data for filtering the list items i can put the link right there if you missed that video by any chance but in this video i want to go to the question that i've been asked so many times the question is can we use odata to filter the list items for a certain quarter so for first quarter second quarter third quarter or fourth quarter of a year or this year and most importantly they want to get an o data query that can give you the list items that they are dated for the current quarter all these things are just basic expressions and i go through it one step at a time so we start by just writing the basic hard-coded odata query that can give us only one quarter for example first quarter third quarter second quarter or whatever then we take it forward and we give you the option that you can pick the quarter and it can actually filter the content for you and finally finally the last point i want to show you how you can write an odata query that it gives you the current quarter let's see our setup and move on with the work as always let's start with a quick look into our setup on sharepoint online i created a list called quarter list and inside it i have a field called title and the item date the title are just dummy titles q11 q12 q13 and as you can see for every quarter the item that is there in the list has a date that matches the queue whatever the index that i have with it so anything that starts with q1 belongs to quarter one anything that starts with q2 belongs to quarter two so on and so forth ideally i want to have a flow with this ui that in the beginning it asks you for the quarter you can pick quarter one two three and four or you may say pick the items for this quarter and based on whatever you pick it should give you the result that includes only the items in the selected quarters so if the user picks one it should show me anything that starts with q1 which belongs to a date that sits in the first quarter the months are one one and three the others are all bigger than that and eventually i want to get to the point that if i pick this quarter at the moment we are in september so anything that comes inside it it should be q3 which is these three records let's create our flow i go to flow.microsoft.com and i want to create a flow quickly so we click on create and i want to create an instant cloud flow which the user can fire it by manually pushing a button i give it a name as q selection and i pick manually trigger flow i click on create now i want to pick a parameter for it so i add an input it's going to be text and i want to call it quarter for the items i don't want to let the user type in the values that i may not like i click on this drop down and i say add drop down list options and i put one two three and four and finally i want to enter another option as this quarter so we have five options and we successfully created our flow i click on save it doesn't like it because we don't have any actions but eventually we want to get the items from the sharepoint list and this is our sharepoint list so it is called quarter list and these are the items inside it i just get the url of this site and here inside power automate i look for get items and i pick up the get items that belongs to sharepoint and for the url enter custom value and i just paste the url here give it a second and it's going to trim it to the site url and i pick up the list which is my quarter list at the moment i can save it but it gives me that special warning that says hey guys you did not put any oh data query are you sure you want to get all the items regardless you can run this and it's all good so so far we created our flow that we can push a button and it gets all the items in the list now let's create an odata query for only one quarter to do that i just need to click on show advanced options and i need to put an odata filter here this one is very easy let's say we want to pick the items for the first quarter for the first quarter i need to get the items that their item date is anything between january 1st to the end of march or smaller than april right so if this is the case i come here and i say item date is greater than and here is the cool part i can literally say 2021 hyphen 0 1 which is the first month i really don't need to put date because just year and month odata can consider it as date we are good with that and right after that i can say and item date is less than 2021 hyphen zero four this one should give me only the items that are in the first quarter so let me just save it and i add another step i just use compose to see the returned items right or you know what let me make it a little bit better right before that i use a select to simplify the format of the data that i get let's get the items from the get items value which returns an array of the returned item so i want only the title which is going to be [Music] expression item dot title i just need to click on maybe here whatever i click on okay and i also need item date and here again i say get the item and item date all right i can click on ok and everything is good so when whatever it returns instead of getting all the items all the fields that the get items returns directly from the sharepoint list i have the result which only includes the title and the item date from the values that the git item returns so let's give it a shot i can click on here i can get the output from the select add it let me just save it and test it okay manually test and continue at the moment it doesn't matter what you pick because we are not filtering for the input so i say run flow all i expect to see is the items in the first quarter i can click on compose pc quarter one and quarter one and quarter one so all you can see is anything that belongs to quarter one and if you don't want to use it i can get directly the values from the get items and you will see the same result with some extra fields that is not that pleasing to i so let me just click on test run flow done and we got it so again the same results you will see q11 with a lot of more fields gosh q12 with a lot more fields and finally the last one which is q13 nothing else came out to this output all right let me just get rid of it i really don't want to see so many fields i get output from the compose and i save it so so far we learned how we can put an odata statement to get the items in a specific quarter and by the way if you're enjoying this video please show me some love and push the like button now if you can write the odata query for one quarter nothing's going to stop us from doing the same thing for all the quarters now let's be creative a little bit think about it this way what we have here is literally a string so if you have four quarters if i create an array of four queries i can assign every query depending on the month requested so let me just copy this and create an array here i would say initialize variable and the variable name is going to be quarter o data and i want to rename this one too and i call it quarter o data the type as you expect it's going to be an array and i just need to put a json array here so i put square brackets double quote and i just paste the first one the first odata query that i added here great i need to add the second element i just copy this one and i add this one here right but this time this one is going to start from the fourth month and it should be less than seven we are looking for the months four five and six so anything less than seven is going to be good with us so again we are looking for the third quarter we're just going to start from 7 and it goes on till 10 and finally the last quarter is going to start from 10 and goes all the way to 0 1 but 20 22 basically the next month right so i just save it at the moment we have an array of queries that can give me whatever i need so now if i want to have the first quarter result i need to have the first item in this query which has the index of zero for the second quarter i need the second item which has the index one so whatever the quarter that the user requests here i just need to deduct it by one and use it as an index former query fantastic so if this is the case let's come back here i don't need this guy anymore and i just add the expression here this is the trick of writing the expression at the moment i want to get the array in the expression field so if i just go here and i add it here it just gives me the visual interface but to get the expression for i go to the expression tab push the space here once just to trick the ui go back to the dynamic content and now you have the quarter data but when you add it it doesn't give you a visual icon of this it actually gives you the code behind it and right after that i can add my index and that index is going to come from quarter but this quarter that we got from here we need to make it -1 and to do that first you need to convert it to integer so let me convert it to integer oh gosh right and then you need to say sub because there is no minus sign inside power automate you need to use mathematical functions and i say comma 1 and i close it so let me just bring the whole formula inside notepad so it looks like this this is my array we need to get the element that its index is whatever that our trigger gets if we convert it to integer then we use the sub function so we deduct one from that integer and that's going to be the index that is going to return the right odata query looks okay to me let's give it a shot and see if i got it don't forget to push okay here and then let's save it this time if i click on test i can say give me the items for the second quarter and i click on run and and now it's going to return me the items in the second quarter i have q2 i have q2 i have q2 again and q2 great what about the next one let's test it again i say test quarter three run flow done fantastic what did we miss edit so let's see so first second there we go here's the problem one extra zero here save it again so let's test it one more time test and quarter three i click on run flow done and this time we should see anything belongs to quarter 3 which is not that important but testing the very last one which is quarter 4 is important so i say test quarter 4 and i click on run flow done and see if we got it and there we go it didn't give us anything so we are missing something here for sure so again i click on edit let's see the fourth statement again this is what i got it should be 2022 because that's going to be the next year so i save it again and i test it one more time hopefully this time we get it fourth run flow done and we are good right time to go with the auditor query for all quarters in current year at the moment if you notice all the queries that i have here oh gosh here if you see all the queries that i put here they have the year hard coded in it this is not what i want this number should be actually updated based on the year that we are in it so let's get the current here in a variable let me just minimize this one and here i want to add another variable so initialize variable and this variable is going to be current here right let me rename this guy too okay the type is going to be an integer and the value how do we get the current here again another expression i click on the expression here and i say utc now utc now gets the current date and time and this is the date so i just need to format it format date time and for the formatting statement i just need to return here and i ignore the rest of it so if i click on ok this variable is going to contain the year numbers let me just save and test it just to make sure it works fine run it doesn't matter which quarter i pick yes it's not happy because what we returned is a string not number and actually we really don't need integer let me convert it to string because eventually we want to use it as a string and put it in the query so let me just test it again save and test run now done and this time it gets the current year so when i have the current year in a variable it's very easy to update my oh data queries and everywhere that i have 2021 i just need to remove it and replace it with current year again the same thing here 2021 replace it with current here again 2021 current here you guessed the rest correct and finally the last one which should be one year added to it i just need to take that out and replace it with another expression and this expression is going to be we need to convert that variable that we have to integer which is the current here we need to add something to it so we add one to it and to be nice and clean i need to string right so again let me just put the entire thing in notepad so this is the formula we get the current here we convert it to integer we add 1 to it and finally we convert it to string again so it perfectly matches the type of the string that we are working with inside that object so i click on ok and we have this variable here too let me do a quick check here let me just save it test it again and i say test run it doesn't matter which quarter we are dealing with but at the moment i just want to see when i say initialize quarter oh data everything looks good so yes 2021 2021 and all the way to 2022 perfect time to make the audit query for this quarter at the moment the flow that i have if i run this and i pick this very last one which says this quarter and i click on run flow it throws me an error with some inappropriate words because this poor guy cannot actually convert that this quarter to an integer and subtract something from it and provide it here as an index so forget about it we need to handle that i click on edit i need to get the query index for this quarter at the moment if i come back here when i say get items the variable that we used here we actually directly got that value from our trigger or from our quarter input but actually we need to process it a bit earlier before we blindly pass this value and hoping it's a number to this expression so i go to the top of the page and i want to add another action and its action is going to be a condition for this condition and by the way before that i want to have a variable i say variable let's say initialize variable and this variable i call it query index right let me just rename it type is going to be integer and i don't want to put anything inside it at the moment or let me just put 0 inside it it doesn't matter because i want to put the value inside it when it comes to condition and inside this condition i want to check the value that we get from the user so it's going to be the quarter that i get from the manually trigger flow i put this value here i say if it is equal to see what was the last item was this quarter right so if i come here inside this condition i say if the quarter that the user enters is equal to this we got to do something that gives us the current quarter but if it is no we can simply steal the calculation from here right which i have it actually inside notepad right and this is my calculation so basically whatever the number that the trigger returns i need to convert it to integer subtract it by one and then we are good to go we have our query index so if the condition is no i just need to say set variable right and inside this set variable i pick the query index and the value do not paste it here directly make sure you click on the expression tab and then you paste it here i click ok we're good so if the user picks a specific quarter this one calculates the right index and puts it here if not we have a little bit of work to do right now we need to process the part that the quarter is this quarter for this part again i use set variable right where is it all right here and again i need to assign the same variable query index but this time we have a little bit of calculation to do if i divide the number of the month by 3 i can get the quarter not exactly the quarter it's going to be quarter minus 1 which is the exact index that we need so if it is the second month 2 divided by 3 is going to be 0 point whatever which whatever doesn't matter that zero is exactly the index of the array that i want here to process my query right so if i come here i can say [Music] get the current month and to get the current month i can use utc now it doesn't work here make sure you are in the expression tab utc now right and first of all i need to format it format date time and i want to format it this time to m just make sure you put capital m so i get the month here this month needs to be converted to integer don't miss this part it is important and now that we have an integer we can divide it by 3 which gives us the exact index that we want for this now you may ask doesn't divide return the decimal point yes it does if you're working with non-integer numbers so if you're for example working with float numbers with the decimal point numbers that's great but here we specifically put int so it should give us the correct number i press update let me click on it just make sure it has everything that we want let me just put it for you inside notepad so it looks like this i have the current date and time i format it to mm which only gives me the month then i convert this one to integer and after that i divide that integer by 3 which gives us our query index so let me go back here again i click on update just to make sure and i guess we have nothing else to do let's test it save and test it is september today so we should be in quarter three let me just test it and i picked this quarter i click run done run failed see what we missed exactly we forgot to replace the scale items here because now instead of variables quarter or data or whatever that we have here instead of this long expression for the index i can get the value from the variable that we have it as quarter index because we calculated and put the value there so i click on update this time and let's run it save test it again test and this time i pick this quarter and i should get quarter three because we are in september and let's see how lucky we are it gave us quarter four why is that that's what i wanted to talk to you about we are in september september is the ninth month nine divided by three gives us three which pushes us to the fourth quarter so that's where you actually need a little bit of adjustment so when i come here when i get the month before i divide it i need to deduct one from it so that the numbers add up because for us when we are working with the index we are dealing we start everything with zero so i would say subtract let me remove this guy whatever that i get from the month and i convert it to integer subtract 1 and then close it so let me just put the entire thing inside notepad in case you are following me so let me replace this one with this it should look like this if we get the date we convert it to month and then we convert the whole thing to integer whatever the month number that we have we deduct one from it then we divided by three right so let me just test it again gosh here just make sure you click on update click on it again make sure you have everything here that sub is still here great test and there we go give me this quarter run flow done and we should be good we are in quarter 3. now if your head is spinning with so many formulas and expressions that i wrote and i just put in this video probably you are not familiar with my other course that i have on udemy which is called master microsoft power automate expressions in two hours this course covers everything that you need to know about working with expressions and how they work and whatever that happens backstage behind the ui that you see in the power automate yes the course goes back to 2019 but nothing has been changed till today when it comes to expression writing so if you haven't taken this course and you're really interested in getting into power automate expressions the link to all my udemy courses are in the video description and that also includes the discount voucher and i have quite a bit of courses on udemy that was it i think it was very easy although there are different ways if i want to do it myself probably i make it a lot more complicated with a lot less actions and throwing a lot more formulas inside it but this is what i could somewhere pick the midground thank you for watching and i will see you in the next video [Music] do you

Channel: Alireza Aliabadi

Views: 684

Keywords: get items for this quarter in power automate, get items in power automate, Get items OData filter, OData quarter, Power automate quarter filter, Power automate OData date filter, Power automate get this quarter, Get items OData date, Get items OData this quarter, get items filter, get items date, get items quarter, Power automate sharepoint, sharePoint quarter

Id: 0J_iFn3fNcY

Length: 33min 5sec (1985 seconds)

Published: Thu Sep 30 2021