PowerApps filter gallery by Date Time column and delegation (Part 2)

hello everybody my name is reza durrani and this is part 2 of my delegation video with sharepoint and in this video we are going to see how to work with date columns in sharepoint so if we go back to our delegation functions based on docstore Microsoft calm as you know date time functions if I use them in filter operations it is not delegate if I use them in sort operations they are delegate as you can see so if I head back to my power app and this is the same app that we had before I added a few things because I wanted to pre create things for this demo so let's look at the filter condition for the gallery currently in my SharePoint list of students I had a column for enrolled date which is a date/time column and as you can see it has the enrollment date for all the students in the power app for this gallery the filter function that we are using is filter the students list where the android date is less than seven days from today so let's say that's your requirement wherein you need to know which students have enrolled in the last seven days okay that's my that's my requirement so if I if I update my query to to show the students that have enrolled in the last seven days right I have I have if I play this app so it's showing me from eight so it's showing me all the dates let's check this one out so enroll date as you can see is less than today - seven so it should be greater than today - seven so this is going to show me all the students who have been enrolled is in the last seven days now in this case as you can see I'm getting no results let's change the the last 20 days so let's say I want to know who are the students who have enrolled in the last 20 days and as you can see there are students who enrolled on the 14th which is good now if you look at the results set again I have a delegation warning why is there a delegation warning out here the reason for the delegation warning is is because the I am trying to use filter operation with a date field and ask for the documentation you cannot use date with filter and as you can see it says look at number 4 and if you look at number 4 date/time functions are not eligible exception of this one unique case now if we head back to the power app so this is initial right there's a delegation issue and the problem is that let's say if I go back to my student list and let's say I have a student that setting beyond my delegation limit in this case for this specific use case we have said the delegation limit to 50 so I would like to highlight this and heading back to my list let's say student sitting at position number 84 I am going to change the enroll date for this student to the 21st of August so he is within that time range and so I've changed him to 21st of August if you also notice I have another field called enrolled date number it's a number field and I'll get to this later but in this number field what I am doing is I'm going to store the date in number format in the following in the following pattern it's going to have the year then it's going to have the two-digit month and then it's going to have the beat again in two digits so your month and the day I'm gonna save this ok so I just added I just changed the enroll date for student at position number 84 now if I head back to my app and if I was to just just for demo purposes if I was to refresh this data source and if I was to change my query to show all the students who have and rolled in the last seven days I nearly it should show up that student but as you can see it it does not show up that student and the reason is because Android date is a date field and delegation does not work with date columns and my daily gave a limit was set to 50 so it will only search in the first 50 items of this data source if I was to go back to this data source and change the enroll date for a student who is sitting in the first if 50 items so let's say I change this one to the 23rd which is within the date range for the last 7 days I'm going to change this again 2019 0 8 2 3 see if this now if I head back to my power app and if I was to refresh this the same query although it is has a delegation warning it will return a result because it's only going to work in the first 50 records because my delegation limit is set to 50 if it was 500 it will only search in the first 500 records so this is a problem right for large data sets this is not going to work and currently date fields are not Delhi gable I know this is on the roadmap because at Japan conference I had the opportunity of talking to the product team and they did mention that date time functions are on the roadmap and very soon date functions are going to be delegate but in the interim since date functions are not eligible what do we do as you can see number fields are delegate with filter right here right they are delegate so what we can do in this case is whenever you are storing information for the date right ideally you'd be doing it through a power apps form what you could also do is when the user picks the date you can also store the number equivalent of that date in a peculiar format and that format if you notice when I manually enter it through the power app that format this can be a number field hidden field and what I'm doing out here is I'm storing it in this format the year the month and the day right that's how I'm storing this now by storing it in a number field what I'm able to achieve is the fact that I can now query this without having to run into delegation issues okay so instead of querying the enrolled date field I'm going to query the number field the extra work that I want to do is I need to ensure that I have another field that I create that's called number that's the first thing I have to do second I need to also ensure that whenever enrolled date is added or updated this field is updated as well because this is the field I'm going to use for my queries now in my case for all my students what I've gone ahead and done is I've also added this date field okay so now actually head back to the power app okay and let's say I want to do the same thing I want all the students who have enrolled in the last seven days so this time instead of using this field I am going to change this to enrolled date number field now I need to get today minus seven in numeric format today is going to give me today's date minus seven is going to go as seven days behind from today so how do I do this right so if I go back to my app I'm just trying to steal a formula or nothing else so if I go back to my app and come back to the same query right I am going to replace this with this so what have I done here let's let's try and understand this right I'm saying that the enrolled date is greater than I'm taking today's date subtracting seven days from today then I am formatting it in exactly the same format that I'm using in my back-end list as a column right I'm storing it in a numeric format as your month and day correct and then as you can see once I format this date in that format I'm converting it into a value because I want the numeric value for it so I will get that value now the problem is this is also drawing a delegation warning that's because I'm trying to do these calculations value today within the context of a filter operation so what I can do as you can see on start of the app it self this is the app dot on start function I am setting a variable which says today - 7 formatted as your month day converted into a value and store it in this variable called where 8 from and now in my query I'm going to replace this whole thing with where date from right and the delegation warning disappears why because I'm doing the calculation beforehand I'm not doing it in the filter operation which is again best practice right I don't have to keep doing the calculation of the fly I already have the date set right here and now as you can see there is no delegation warning and if I look at this it will now so I'm saying show me everything where the end roll date number is greater than this so let's first run on start again and now this is going to set that field and it is going to set it to this value as you can see because I just put a label you have to show you what it will set it to oh if I change this to where date from this is a 2019 8 18 that's a week from today today right now while I'm recording this video is the 25th of August this is the 18th of August so it's gone seven days back and it's converted converted into numeric for who that's why I just added this label so what it is going to do now it's going to go and look at everything in the students list where the enrolled date number field is greater than this number right and because I have stored the dates in that format there is no delegation warning and this works it's showing me everything it's showing me all the items that are spanning beyond my delegation limits so no problem right we've solved delegation that's one number two what if I have a date range that I want to give my users to filter wrong so as you can see I created a section out here which has a from date and which has a to date right so it's date from year's day - now how do I change my query so the user can take a range and automatically it will filter the students list and show me all the students enrolled between that rage so in this case what I've done is on start of the app I have done two things first I have this variable that I'm setting which is less than 7 days from today and then I have a second variable that I'm setting which is today's date both of them formatted in this format which is your month D so I've got two variables and I'm going to run this app to show you the values and these variables so as you can see if I go to global variable gate from which is seven days from today that's the 18th of August and date to which is the 21st of August both of them in numeric format then what I have done is I have to date columns that I have created a from date and a to date the from date is defaulted to today's date - 7 the to date is defaulted to today ok and that is exactly the same setting that I have for these two variables when the app loads so they all are going to be in sync right when I run this I have the variables also set to the same date now what I want to also do is when the user changes these fields I want those variables to be updated so how am i doing that if you pick the date column and if you look at unchanged event for this date column what I'm doing is I'm resetting the variable you date from dot selected date that is the same field dot selected date and I am formatting it that's it so whenever the user changes this it will also change this variable which is date from and whenever the user changes date too I am also if you look at the on change property of this I am doing exactly the same thing here I'm setting date - based on date to the variable based on the selected value in this and now if I want to change this so that it matches that date range I'm going to do it in this fashion so give me everything from the students list where the enrolled date number is greater than or equal to the from date and the enrolled date number cannot focusing on the number field only why because that's the only field that will not give me the delegation warning date will give me a delegation is less than so the from date is greater than equal to today and it is less than or equal to the second variable which is variable date - that's it so now if I was to play this app you will notice that let's go again and let's try and rerun this I'm gonna run on start ok run my own start I'm gonna come back here let's say I changed my from date so let's say I pick second of July and I'm gonna say all the way to 31st of July so this now should filter all my results so let's look at variables this is the 25th to 4 so it did not change the variable to date so let's change this again machine is just 30 n so this will change the to date ok so as you can see now it is only querying the results based on the date range that I select them all right so it's gonna show me only the students that I enrolled between the 2nd of August I'm sorry the 2nd of July and the 30th of July and this is the list and there is no delegation warning so if I want to see all the students for the month of August very easy squawk is first all the way through August 31st this will Delhi perform the operation again delegation not happening on the date fields but the number field that I created so this is a trick that you can use to query results or data sources that span beyond the delegation limits specifically related to the date fields thank you so much for watching

Channel: Reza Dorrani

Views: 17,934

Rating: 4.9347825 out of 5

Keywords: powerapps filter gallery by date, powerapps filter gallery by datepicker, powerapps filter gallery by date range, powerapps filter sharepoint list, powerapps filter by date, powerapps filter by datepicker, powerapps filter gallery sharepoint list, powerapps filter sharepoint list by current date, powerapps filter sharepoint list by date, powerapps filter sharepoint list by date range, powerapps delegation sharepoint, powerapps delegation date

Id: eCMuXPI1Qok

Length: 14min 56sec (896 seconds)

Published: Sun Sep 01 2019