I checked the same table today on MS' website while watching this AWESOME series and it looks like all listed functions are now delagable with date/time as it says Yes for each item in that column. I hope this series covers the People field too! i.e. filtering by assigned. In any case amazing work Reza! You're just amazing.
Hi Reza, I'm working with a DateTime column in SharePoint and want to be able to filter only on time as well in Powerapps. What would the text string be for a "varTime" variable in a shorttime24 format, if I were to convert to a number. E.g. 10:30 to 1030 Set( varTime, Value( Text( Now(), "????????" ) ) ); Another great video and thanks for posting!
Not sure as it is working fine for me Why do you only need the hour and minute if using the technique described in video which requires the date details as well
@@RezaDorrani Okay so it would beText(Now(), “hhmm”)? I am creating a calendar view where a gallery item is an event, and i am going to place galleries in line with an hour line, as if on a calendar. And need the time variable so I don't get delegation warning
Thanks Reza, this is really helpful!! By the way, I'm surprised how much coding is required in Power Apps to provide a simple functionality like this and how NOT so straight forward this is. Not sure if this will be something that in the future will be simple to add in Apps through few simple clicks...
Hi Reza, thank you for ur content. I have a question about filtering dates,, the less than or equal does not work - it as if its less than without the equal. This is the formula I wrote: Filter(list, 'Created' >= DatePicker1.SelectedDate && 'Created'
Its related to date and time being set at 12 am midnight and more. I have seen plenty of solutions around this on forums. I will recommend checking on the forums at powerusers.microsoft.com/
This is excellent, thank you for your work!! By the way, indexing your SharePoint list is a great idea, but does it matter which column you index by? Thanks again!
Reza, you made my day. Thank you so much for sharing this solution. One question please, have you ever used Power Automate with OneNote Actions to create pages in a flow and print a pdf to this page? If so, i'm interested in any idea how to solve this. I'm able to do all the html stuff, but i won't be able to print the pdf inside the generated page. Maybe this would be a great topic for a video? Cheers Thomas
Hi Thomas, did not work with OneNote and flow. I would recommend posting your query on the community forum at powerusers.microsoft.com/t5/General-Power-Automate/bd-p/MPAForum
Hello Reza, Thanks for always sharing excellent contents. I would like to ask you one thing. I'm using filter function to filter dates (From x To) and it's working perfectly, however, I'd like also to get those data that the status column is equal to "Working", even if the date is out of my range. is that possible? Example: Filter April month "From - 01.04.2021 x To 30.04.2021", it shows me only the data from that range, but if there is\are data with column status "Working", show me as well all of them. Thank you.
Hi Reza, Thank you for the video. How to do the filter by month and year in a separate dropdown each?. Say I want to choose the Month filter and keep the year constant to view the data and viceversa.
You would need to create the dropdowns for each and then when filtering use values from those dropdowns to create a date object and query. I do not have a video on this use case. I would recommend you post this query on the forums at powerusers.microsoft.com
Hello Reza, I am using below filter for my gallery. If( varShowUpcmg, Schedule >= Today() , true ) am setting varShowUpcmg to true at onCheck property of a check box and I would like to display the sessions scheduled after today. "Schedule" is datetime column in SharePoint. Surprisingly I still see delegation warning here despite Date columns are delegable now. Why do you think?
I think your issue is with the If statement within the filter function. I will havr to check. I am doing a video next on filtering gallery items, I am hoping it will help. Also, if SharePoint column is DateOnly, delegation will work. DateTime does not delegate.
Hi Reza i am working with a datepicker, hourpicker,minutepicker + pm/am. These thingd dont take over the datecolumn from my source in sharepointlist. The thing is the column in sharepoint is mm/dd/yyyy hh:mm am/pm. How do i get those things to pick up the details from the source?
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza, instead having date filter on app,can we filter from sharepoint list to latest month data,on that data we can create gallery for interaction. Appreciate your answer?
Hey, do you have a powerapps course that covers more adept functionality like this? Would be great to enrol in something like that as most courses out there right now are very basic
@@benjackson2284 Thank You. I am banking on the fact that many folks are in need of good training content. This will be a lot of effort. Fingers crossed on the outcome.
Hi, Reza great video and helped me out loads. Quick question. How do I format the date number back into a date value in PowerApps. I can view the number but it doesnt look great to the user. I'd like to format it back into a dd/mm/yyyy format.
Hey, we no longer need to do this hack for dates as SharePoint date columns now support delegation. I am working on a new video on delegation which will highlight this point.
HI Reza, How are you passing Datepicker.selectedDate to the SharePoint Date column from the DatePicker control? I keep getting the error: String '09/20/2020' does not validate against format 'date'. I've tried many ways to populate into sharepoint by way of a flow connected to my powerapp form, but to no avail. I would appreciate you insight. Thank You so much.
Hi Reza, this is awesome, and I've used this in my apps. I have a unique case though where I only want to show the records from SharePoint that match the current month from any year. I.e. Records for August 2019, August 2020, August 2021, August 2022 etc. Is this achievable without the delegation warning?
My guess is Yes but I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
I would have to try it out with sql to provide guidance. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, how would you create a weekly time sheet report for a part time employee table {ID, Name, Date, Time In, Time Out}, I was wondering how you would group the employee names with weekly date ranges (eg.1 Jan 2021 - 7 Jan 2021), ideally it needs 3 nested galleries, what is your workaround for the limitation? This could be an idea for your next video.
Hi Reza ,Can you please help me in this . Instead of converting it in to number , what is possible way to show only that day data in powerapps ? starttime = today() ?
Hi Reza, One more question. What is the point to keep the Date Column if the user has to enter another "Number" column to contain the date data for querying. Can we just keep the "Number" type date data and delete the "Date" type column in SP LIST?
Yes it does but when user is filling out the form they wont enter data in numeric format In the powerapp form - we can add a hidden number column which gets filled with the format when user picks a date In future once Date columns are delegable all we need to do then is switch the formula to point to actual date column
@@RezaDorrani Thanks for replying. Yes, user won't know how to fill out yyyymmdd format. How to add a "Number column" that will automatically sync (filled) based on "Date column"? I have tried the "calculated column" and choose output as Number to do the auto filled part but it is "calculated column" anyways. still have the delegation issue for "calculated column".
@@seanli6265 Calculated columns only work in context of SP. Flow and PowerApps will not be able to read values from calculated columns. If the form the user enters data in is a PowerApp, then you could add the format logic in the PowerApp, or you could also let Flow update the date column value when user enters data
Not sure. I will have to look at your App in action to know why. I will recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi Reza, i have a powerapps Application and i increase the data delegation limit to 2000. So, i'm asking whether my Gallery can't get more than that 2000 items when the data is much. Please is there a way to work around that to avoid future delegations issues?
Hey Reza, Really enjoy your videos, you are very clear and thorough throughout the videos! I am trying to Filter a Gallery by a Date Dropdown rather than a Date Picker. I have a SharePoint list that holds records for past and future dates. I want to be able to filter by a specific date or by the whole gallery. Do you think you could provide a video to show us how to do something like that? Is that something that is even possible? Chris
Why would you have dates in a dropdown? I have another video on filter by date range as well - ruclips.net/video/5dSk5iOgT68/видео.html There are a lot of posts on the forums around filter by date scenarios. I would recommend checking the forum at powerusers.Microsoft.com
@@RezaDorrani Hey Reza, Thanks for the reply. The reason that I would like to provide the users the ability to select the date from a drop down is because I would like to give them the visibility to see a particular training date. I watched the other video you mentioned in order to select a date range, but my goal is to give them a list of dates in the SharePoint list. I also checked the forums with no luck. I can accomplish getting a drop down with the training dates, but cannot find a way to show all records. Is there a way to maybe just create a "Show All" button? Chris
Not sure what you mean by buttons? You need a direct filter (hardcoded) which shows all data that are created or modified 2 days ago? I did one on date filter range (includes selecting date range) - ruclips.net/video/5dSk5iOgT68/видео.html
Hi Reza, I have a sharepoint list which holds nearly 4000 records, In Powerapp, I have sharepoint date column, which Iam using to filter the gallery.I have to filter the gallery based on current month and year[Text(DateValue(Check_Point_Date),"yyyy/mm")=Text(DateValue(Today()),"yyyy/mm")], I have used this formula but it shows delegation warning and not retrieving the data. Can you help me with this,please
It will not be delegable. Better option would be to query it based on date range. So current month and year - you would do something like date >= 01/10/2022 && date
Reza yes, I have looked at the documentation at Microsoft and it still tells people to convert your date into a number - I didn't see mention of a road map unless I missed it. However, it doesn't tell you how to use it in conjunction with a date picker like you do. I also discovered that clear collect is also not delegable from another channel. I have a table with over 30,000 rows with a date from which I extract a diary id, the diary id is then used to filter down a table with over 1 million records. @@RezaDorrani
Hi Reza I have added global variable with datatype value and I passed variable in items of gallery along with another filter ,still am facing delegation warning. Appreciate your answer.
just got confuse with more than 5K items. Are you saying if I have a List with 5K items and if I try to filter item with Title then it would not work ? if the Title column is not index
This is a SharePoint limitation. If the view or query returns more than 5k records and if the column being queries is not indexed in SharePoint - it will throw the list view threshold limit exceeded error
@@RezaDorrani Thanks for your reply. I am trying to fetch item from a large list (which has 5003 items). When I scroll down and try to see more than 5000 items it shows error. "The requested operation is invalid.The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." Even Title is a Index column. Query : Filter(MoreThank5KRecords,StartsWith(Title, txtTitle.Text))
No - it would work since SharePoint now auto indexes. But as a best practice I would recommend making your indexes beforehand since you only get 20 for a list/library
Hi Reza, I got it to work without delegation issues. However, I am now experiencing a bizarre problem. Every second time I use the date picker, it returns no data. For example, start the app and pick 4/1 I get data pick 5/1, and no data is returned. Pick 3/1 data returned pick 4/1 no data returned but then pick 5/1 data returned. Using SQL server data.
I have not come across this issue and hence not sure what the cause could be. I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Hello Reza, i am using this Filter('Work', StartsWith('Function',Searchbox.Text)) but i still get the delegation error. the work table is a sharepoint list which was created for data input and right only has 3 records but the function column is coming from a table with over 11k records which is also on sharepoint. i created an indexed column for the column i want to filter on and increased my delegation row to 2000 but i still get the error
@@Itsprincessa01 Confused since video is on date column. I would recommend you post your issue with screenshots on the forums at powerusers.microsoft.com/
You cannot change date column type. Create an additional column of type Number and update this column with the date column value in numeric format. Run all your queries on the numeric column.
Reza Dorrani did u mean a calculated column or normal numerical column? Coz I used a calculated column to replicate the date but in power apps it threw datatype as table..so my Filter has shown delegation
Mageshwari Ganesan Calculated columns are calculated at run time in SharePoint and cannot e interpreted by Power Apps. You will need a Numerical column.
Enrolled Date Number field technique not work in my case, because i create simple Number field also calculated column which update automaticly but problem is that this filed is not recognised in powerapp Error: Filter('PQ-VTS', 'Reporting Date Number' > 20240813) Invalid Argument Type. Expecting one of the following: Number, Decimal, Date, Time, DateTime, Untyped Object but once i apply Value on Filter('PQ-VTS', Value('Reporting Date Number') > 20240813) It gives Delegation warning.
i really need your help in fixing this, i have a list on sharepoint with over 5000 recprds, using your video, i created indexed columns on the field i need. now in powerapps, i am trying to create a combo box so i can use the "find item" box to search through the long list and select multiple items from that table problem 1: i get server error because it exceeds the limit so the combo box does not display any data problem 2: i tried this using another column which has 11000 records but the data does not reflect in my sharepoint list. problem 3: i need to be able to select multiple items from the combo box and save all multiple items on in the same field in my sharepoint list problem 4: i need to auto generate ids from powerapps and save in my sharepoint list, so when i create a new item in powerapps, it auto generates an id i am stuck, please help
Combo box does not support records greater than 2000. You will have to provide the user an option to search in a gallery. I would have to recreate your scenario to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani thanks, i was able to resolve most of my issues but i haven't figured out how to save multiple items in a combobox on a single field in sharepoint list
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani I haven't tested it myself but the official SharePoint delegation documentation mentioned "Yes" under DateTime type. I will try to test it and confirm.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
I checked the same table today on MS' website while watching this AWESOME series and it looks like all listed functions are now delagable with date/time as it says Yes for each item in that column. I hope this series covers the People field too! i.e. filtering by assigned. In any case amazing work Reza! You're just amazing.
Very cool!
Yes, this series has that one covered. These were some of the earliest videos on my channel. Glad to hear they are still valuable.
Really great teaching. You have answered my questions regarding "Delegation" and "Delegable functions" in the first video.
That was the goal. I am coming up with a delegation workarounds video soon which will cover more advanced concepts. Hope you will like it.
Thank Reza ! Your video is great! And it should be part of MS training on PowerApps & Sharepoint!
Thank you :)
Amazing video Reza! Very useful information.
Glad you liked it
a trick : you can watch movies on flixzone. I've been using them for watching loads of movies during the lockdown.
@Miles Isaac yea, I've been watching on Flixzone} for years myself =)
@Miles Isaac definitely, been using Flixzone} for years myself :D
@Miles Isaac Yup, have been using flixzone} for months myself =)
Thank you Reza for such a great video.
My pleasure!
Hi Reza, I'm working with a DateTime column in SharePoint and want to be able to filter only on time as well in Powerapps. What would the text string be for a "varTime" variable in a shorttime24 format, if I were to convert to a number. E.g. 10:30 to 1030
Set(
varTime,
Value(
Text(
Now(),
"????????"
)
)
);
Another great video and thanks for posting!
Text(Now(), “yyyymmddhhmm”)
That makes sense but I actually ONLY want the time and when I put "hhmm" in it picked up "hourmonth" and not "hourminute"
Not sure as it is working fine for me
Why do you only need the hour and minute if using the technique described in video which requires the date details as well
@@RezaDorrani Okay so it would beText(Now(), “hhmm”)?
I am creating a calendar view where a gallery item is an event, and i am going to place galleries in line with an hour line, as if on a calendar. And need the time variable so I don't get delegation warning
That was brilliant, thank for sharing.
You are most welcome
Thanks Reza, this is really helpful!!
By the way, I'm surprised how much coding is required in Power Apps to provide a simple functionality like this and how NOT so straight forward this is. Not sure if this will be something that in the future will be simple to add in Apps through few simple clicks...
It does require some amount of work.
Many thanks. Useful information
Glad it was helpful!
Thanks
Hi Reza, thank you for ur content. I have a question about filtering dates,, the less than or equal does not work - it as if its less than without the equal. This is the formula I wrote: Filter(list, 'Created' >= DatePicker1.SelectedDate && 'Created'
Its related to date and time being set at 12 am midnight and more. I have seen plenty of solutions around this on forums. I will recommend checking on the forums at powerusers.microsoft.com/
@@RezaDorrani Thank you Reza !
This is excellent, thank you for your work!!
By the way, indexing your SharePoint list is a great idea, but does it matter which column you index by?
Thanks again!
learn.microsoft.com/en-us/microsoft-365/community/large-lists-large-libraries-in-sharepoint
Reza, you made my day. Thank you so much for sharing this solution. One question please, have you ever used Power Automate with OneNote Actions to create pages in a flow and print a pdf to this page? If so, i'm interested in any idea how to solve this. I'm able to do all the html stuff, but i won't be able to print the pdf inside the generated page. Maybe this would be a great topic for a video? Cheers Thomas
Hi Thomas, did not work with OneNote and flow. I would recommend posting your query on the community forum at powerusers.microsoft.com/t5/General-Power-Automate/bd-p/MPAForum
@@RezaDorrani Thank you for this quick answer. So I'll post on the forum. Regards
Hello Reza, Thanks for always sharing excellent contents. I would like to ask you one thing. I'm using filter function to filter dates (From x To) and it's working perfectly, however, I'd like also to get those data that the status column is equal to "Working", even if the date is out of my range. is that possible? Example: Filter April month "From - 01.04.2021 x To 30.04.2021", it shows me only the data from that range, but if there is\are data with column status "Working", show me as well all of them. Thank you.
You would want to use an OR condition to do that.
Something like
Filter(datasource, (your date filter condition here) || status="working")
@@RezaDorrani It's working. Thank you so much. :)
Reza, se minha base de dados for o Data Verse, muda minha delegação de campos?
Check learn.microsoft.com/en-us/power-apps/maker/canvas-apps/delegation-overview#delegable-data-sources
Hi Reza, Thank you for the video. How to do the filter by month and year in a separate dropdown each?. Say I want to choose the Month filter and keep the year constant to view the data and viceversa.
You would need to create the dropdowns for each and then when filtering use values from those dropdowns to create a date object and query.
I do not have a video on this use case. I would recommend you post this query on the forums at powerusers.microsoft.com
Hello Reza, I am using below filter for my gallery.
If(
varShowUpcmg,
Schedule >= Today()
,
true
)
am setting varShowUpcmg to true at onCheck property of a check box and I would like to display the sessions scheduled after today. "Schedule" is datetime column in SharePoint. Surprisingly I still see delegation warning here despite Date columns are delegable now. Why do you think?
I think your issue is with the If statement within the filter function. I will havr to check. I am doing a video next on filtering gallery items, I am hoping it will help. Also, if SharePoint column is DateOnly, delegation will work. DateTime does not delegate.
@@RezaDorrani My SharePoint column is datetime, so that was the problem. If I want to avoid If condition here how my function should look like
@@ResponsibleXI Wait for my video next week :)
Check my delegation video part 3
Thanks Reza 🙏🙏🙏
Most welcome
Hi Reza i am working with a datepicker, hourpicker,minutepicker + pm/am. These thingd dont take over the datecolumn from my source in sharepointlist. The thing is the column in sharepoint is mm/dd/yyyy hh:mm am/pm. How do i get those things to pick up the details from the source?
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi Reza,
instead having date filter on app,can we filter from sharepoint list to latest month data,on that data we can create gallery for interaction.
Appreciate your answer?
Yes can be done using Power Fx functions.
Hey, do you have a powerapps course that covers more adept functionality like this? Would be great to enrol in something like that as most courses out there right now are very basic
Working on it! I'm planning my own training portal. Should be ready in Q1 of 2022. Stay tuned!
@@RezaDorrani Great news! Ill definitely be interested in enrolling
@@benjackson2284 Thank You.
I am banking on the fact that many folks are in need of good training content. This will be a lot of effort. Fingers crossed on the outcome.
@@RezaDorrani Hi Raza! Is there any update on this? Looking forward to it!
@@TheMKTube I was dealing with a personal loss in the family and hence this has got delayed. Hopefully I will start on this soon.
Hi, Reza great video and helped me out loads. Quick question. How do I format the date number back into a date value in PowerApps. I can view the number but it doesnt look great to the user. I'd like to format it back into a dd/mm/yyyy format.
Hey, we no longer need to do this hack for dates as SharePoint date columns now support delegation.
I am working on a new video on delegation which will highlight this point.
Thanks Reza, is this applicable to power bi tile url?
No. This is only applicable when querying a data source. In this case SharePoint using either Filter or Lookup function.
HI Reza, How are you passing Datepicker.selectedDate to the SharePoint Date column from the DatePicker control? I keep getting the error: String '09/20/2020' does not validate against format 'date'. I've tried many ways to populate into sharepoint by way of a flow connected to my powerapp form, but to no avail. I would appreciate you insight. Thank You so much.
Hi James, I have it covered in this video
ruclips.net/video/emAhLzVG9bM/видео.html
Hi Reza, this is awesome, and I've used this in my apps. I have a unique case though where I only want to show the records from SharePoint that match the current month from any year.
I.e. Records for August 2019, August 2020, August 2021, August 2022 etc.
Is this achievable without the delegation warning?
My guess is Yes but I do not have a video reference on this scenario and would have to try it out to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Hi @Reza, how can we handle the same issue with SQL Server?
I would have to try it out with sql to provide guidance. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, how would you create a weekly time sheet report for a part time employee table {ID, Name, Date, Time In, Time Out}, I was wondering how you would group the employee names with weekly date ranges (eg.1 Jan 2021 - 7 Jan 2021), ideally it needs 3 nested galleries, what is your workaround for the limitation? This could be an idea for your next video.
Will add it to my backlog but this is the first request on this topic. I will need a lot more requests on this topic to give this a higher priority.
Hi Reza ,Can you please help me in this . Instead of converting it in to number , what is possible way to show only that day data in powerapps ? starttime = today() ?
Date columns are delegable now but not datetime from what I remember. Check documentation.
Hi Reza, One more question. What is the point to keep the Date Column if the user has to enter another "Number" column to contain the date data for querying. Can we just keep the "Number" type date data and delete the "Date" type column in SP LIST?
Yes it does but when user is filling out the form they wont enter data in numeric format
In the powerapp form - we can add a hidden number column which gets filled with the format when user picks a date
In future once Date columns are delegable all we need to do then is switch the formula to point to actual date column
@@RezaDorrani Thanks for replying. Yes, user won't know how to fill out yyyymmdd format. How to add a "Number column" that will automatically sync (filled) based on "Date column"?
I have tried the "calculated column" and choose output as Number to do the auto filled part but it is "calculated column" anyways. still have the delegation issue for "calculated column".
@@seanli6265 Calculated columns only work in context of SP. Flow and PowerApps will not be able to read values from calculated columns. If the form the user enters data in is a PowerApp, then you could add the format logic in the PowerApp, or you could also let Flow update the date column value when user enters data
Hi Reza, for some reason is not working at my end. The SortDescending1 is not changing to a blue color like in your screen. Any suggestions?
Not sure. I will have to look at your App in action to know why. I will recommend posting your issue with screenshots on the forums at
powerusers.microsoft.com
Hi Reza, i have a powerapps Application and i increase the data delegation limit to 2000. So, i'm asking whether my Gallery can't get more than that 2000 items when the data is much. Please is there a way to work around that to avoid future delegations issues?
As long as no delegation warnings, gallery will show all records as user starts scrolling to bottom of gallery.
@@RezaDorrani Thank you so much for this reply.🙏
Hey Reza,
Really enjoy your videos, you are very clear and thorough throughout the videos! I am trying to Filter a Gallery by a Date Dropdown rather than a Date Picker. I have a SharePoint list that holds records for past and future dates. I want to be able to filter by a specific date or by the whole gallery. Do you think you could provide a video to show us how to do something like that? Is that something that is even possible?
Chris
Why would you have dates in a dropdown?
I have another video on filter by date range as well - ruclips.net/video/5dSk5iOgT68/видео.html
There are a lot of posts on the forums around filter by date scenarios. I would recommend checking the forum at powerusers.Microsoft.com
@@RezaDorrani Hey Reza,
Thanks for the reply. The reason that I would like to provide the users the ability to select the date from a drop down is because I would like to give them the visibility to see a particular training date. I watched the other video you mentioned in order to select a date range, but my goal is to give them a list of dates in the SharePoint list. I also checked the forums with no luck. I can accomplish getting a drop down with the training dates, but cannot find a way to show all records. Is there a way to maybe just create a "Show All" button?
Chris
@@cperry420 Try posting your requirement on the forum and see if you get any responses.
Hi Reza, If I create a new Calculated column to Change the Date into number format in sharepoint, can it works for the sorting?
Is that any other way to do date sort like you did, if I don't have the number format date.
Yes. Working on a video on it.
Would also recommend posting on forums at powerusers.microsoft.com/
@@RezaDorrani Thank you very much
Do you have a sample video for date filter like. 2 days ago it will give you result date from todays ago. Not using the date filter only the buttons.
Not sure what you mean by buttons?
You need a direct filter (hardcoded) which shows all data that are created or modified 2 days ago?
I did one on date filter range (includes selecting date range) - ruclips.net/video/5dSk5iOgT68/видео.html
Hi Reza, I have a sharepoint list which holds nearly 4000 records, In Powerapp, I have sharepoint date column, which Iam using to filter the gallery.I have to filter the gallery based on current month and year[Text(DateValue(Check_Point_Date),"yyyy/mm")=Text(DateValue(Today()),"yyyy/mm")], I have used this formula but it shows delegation warning and not retrieving the data. Can you help me with this,please
It will not be delegable. Better option would be to query it based on date range.
So current month and year - you would do something like date >= 01/10/2022 && date
A recent update shows DateTime is delegable in Filter and Lookup for SharePoint.
That is true. Only Date columns are delegable -
ruclips.net/video/yXvDj60-3YQ/видео.html
DateTimes are not.
Thank you, Reza, for this. I guess date is still on the road map 4 years later.
Most welcome!
Did you check the documentation to see if its still on roadmap?
Reza yes, I have looked at the documentation at Microsoft and it still tells people to convert your date into a number - I didn't see mention of a road map unless I missed it. However, it doesn't tell you how to use it in conjunction with a date picker like you do. I also discovered that clear collect is also not delegable from another channel. I have a table with over 30,000 rows with a date from which I extract a diary id, the diary id is then used to filter down a table with over 1 million records. @@RezaDorrani
Thank you! Thank you! Thank you! Thank you!
Welcome! Welcome! Welcome! Welcome!
:)
Hi Reza I have added global variable with datatype value and I passed variable in items of gallery along with another filter ,still am facing delegation warning.
Appreciate your answer.
datatype value? What does your query look like?
just got confuse with more than 5K items. Are you saying if I have a List with 5K items and if I try to filter item with Title then it would not work ? if the Title column is not index
This is a SharePoint limitation.
If the view or query returns more than 5k records and if the column being queries is not indexed in SharePoint - it will throw the list view threshold limit exceeded error
@@RezaDorrani Thanks for your reply. I am trying to fetch item from a large list (which has 5003 items). When I scroll down and try to see more than 5000 items it shows error. "The requested operation is invalid.The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." Even Title is a Index column. Query : Filter(MoreThank5KRecords,StartsWith(Title, txtTitle.Text))
No - it would work since SharePoint now auto indexes. But as a best practice I would recommend making your indexes beforehand since you only get 20 for a list/library
Hi Reza, I got it to work without delegation issues. However, I am now experiencing a bizarre problem. Every second time I use the date picker, it returns no data. For example, start the app and pick 4/1 I get data pick 5/1, and no data is returned. Pick 3/1 data returned pick 4/1 no data returned but then pick 5/1 data returned. Using SQL server data.
I have not come across this issue and hence not sure what the cause could be.
I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Thanks a lot!
Plz note - Date Columns will be delegable by design very soon.
For now this hack is the only alternative
@@RezaDorraniThat is good to know. Thank you.
❤
👍
Hello Reza, i am using this
Filter('Work', StartsWith('Function',Searchbox.Text)) but i still get the delegation error. the work table is a sharepoint list which was created for data input and right only has 3 records but the function column is coming from a table with over 11k records which is also on sharepoint. i created an indexed column for the column i want to filter on and increased my delegation row to 2000 but i still get the error
StartsWith on a date column?
@@RezaDorrani its not a date column, it's a text field
@@Itsprincessa01 Confused since video is on date column. I would recommend you post your issue with screenshots on the forums at powerusers.microsoft.com/
thank you
Welcome!
also, can you tell us what your "sortdesecnding1" does? you didn't explain that.
It just helps sort the data. Check ruclips.net/video/6KlI1iZ_KD0/видео.html
Hi! How to change the date columns to date as number column in SharePoint?
You cannot change date column type.
Create an additional column of type Number and update this column with the date column value in numeric format.
Run all your queries on the numeric column.
Reza Dorrani did u mean a calculated column or normal numerical column? Coz I used a calculated column to replicate the date but in power apps it threw datatype as table..so my Filter has shown delegation
Mageshwari Ganesan Calculated columns are calculated at run time in SharePoint and cannot e interpreted by Power Apps.
You will need a Numerical column.
Hello, no need to do that anymore as SP date cols are now delegable. Check ruclips.net/video/yXvDj60-3YQ/видео.html
Date columns are now delegable. So you can avoid the workaround.
Enrolled Date Number field technique not work in my case, because i create simple Number field also calculated column which update automaticly but problem is that this filed is not recognised in powerapp Error: Filter('PQ-VTS', 'Reporting Date Number' > 20240813) Invalid Argument Type. Expecting one of the following: Number, Decimal, Date, Time, DateTime, Untyped Object but once i apply Value on Filter('PQ-VTS', Value('Reporting Date Number') > 20240813) It gives Delegation warning.
Calculated fields are NOT delegable.
This video was done years ago. Date columns are delegable now.
Date and Time are not (from what I can remember)
Thanks for explaining in detail.
How can we work with more than 2000 records
You have to use a collection for the gallery and add them by ID
If you adhere to the delegation rules, you can workin with more than 2000 records.
i really need your help in fixing this,
i have a list on sharepoint with over 5000 recprds, using your video, i created indexed columns on the field i need.
now in powerapps, i am trying to create a combo box so i can use the "find item" box to search through the long list and select multiple items from that table
problem 1: i get server error because it exceeds the limit so the combo box does not display any data
problem 2: i tried this using another column which has 11000 records but the data does not reflect in my sharepoint list.
problem 3: i need to be able to select multiple items from the combo box and save all multiple items on in the same field in my sharepoint list
problem 4: i need to auto generate ids from powerapps and save in my sharepoint list, so when i create a new item in powerapps, it auto generates an id
i am stuck, please help
Combo box does not support records greater than 2000. You will have to provide the user an option to search in a gallery.
I would have to recreate your scenario to provide any guidance. I would recommend you post your issue/query with screenshots on the forums at powerusers.microsoft.com
@@RezaDorrani thanks, i was able to resolve most of my issues but i haven't figured out how to save multiple items in a combobox on a single field in sharepoint list
@@Itsprincessa01 I dont have a specific video on that. Try posting on the forums at powerusers.microsoft.com/
When i use your method it will give error as invalid arguments
I will have to look at your App in action to provide guidance. I would recommend posting your issue/query with screenshots on the forums at powerusers.microsoft.com
Is SharePoint Filter Delegate Now, Sir?
Yes and No. Yes for Dates and No for date time.
@@RezaDorrani Thank you sir
According to documentation, the DateTime data types are delegable now.
Yes, but I believe DateTime columns still throw delegation warnings. Date columns do not.
Did you confirm with a DateTime column?
@@RezaDorrani I haven't tested it myself but the official SharePoint delegation documentation mentioned "Yes" under DateTime type. I will try to test it and confirm.
@@asifkhawaja Let me know. Last I tried, DateTime was not delegable.
Sadly it still doesn't work. thanks for the workaround.
Hi Mayank,
Date column filtering is showing a delegation warning?
@@RezaDorrani Thanks for the reply Reza, I don't remember the exact issue I was facing then but since then I was able to achieve what I wanted.
Mayank Mathur Date column is now delegable with SharePoint. This workaround is no longer needed.
Check ruclips.net/video/yXvDj60-3YQ/видео.html
@@RezaDorrani I was using a value conversion in a Collect inside a For all loop. That value function was non delegable.