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.
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
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...
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
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/
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
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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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
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, 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 ,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 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, 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
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.
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.
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 i am not using input as ur demo. I use formula to convert date field base on other field. Example: old column 01/10/2019 then new column is calculated by text format 20191001. So i get delegation warning in powerapps.
Ratana POUY formula to convert date field by using text format will result in delegation warning - you will have to have a separate number column which has the data In the numeric format In the powerapps form that user fills information in - you can add a hidden number field which gets populated when user fills the date using date picker
@@ratanapouy Hi Ratana, I figured out. What Reza said is not doing calculation in SP list. In List we are not able to do anything(formula) with "Number" column and get data based on Other column except for calculated column. The solution is 1. Create a new "Number column" 2. Add this column data card in Powerapps Editform as the same as other dataCard, just click on edit field (You can make it invisible later) 3. Go to your original "Date Column" find the data card properties (advanced tab). find "Update" formula, should be something like "DataCardValue11.SelectedDate" 4. Copy The formula (DataCardValue11.SelectedDate) to the new "Number" data card. so it will post the "Date Column" date that the user pick to its own (new "Number" data card) column.
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.
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 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
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
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?
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 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.
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, 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.
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) - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-5dSk5iOgT68.html
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, 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
@@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.
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
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/
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
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
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.
Mayank Mathur Date column is now delegable with SharePoint. This workaround is no longer needed. Check ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-yXvDj60-3YQ.html
