This is my first post so i apologize if it's structured poorly. I have some familiarity with PowerApps but i'm still learning.
I'm trying to create a collection on the onvisible property. until i can get it working correctly, i ave the functionality on a button so it can be easily tested. This is what i currently have for the ClearCollect:
'Lots of Data',
CPMName = currentUser, 'WE Date' = currentWEDate//(but equals doesnt work??????????????
currentWEDate is determined by this:
whats happening above is the currentWEDate is always being set to the date of the previous sunday. like right now, the current WE date is 12/29 because the most recent sunday was yesterday.
My gallery items are:
and the error i get is "the requested operation is invalid. Server Response: The expression "(CPMName eq 'Kyle Johnston')and(WE_x0020_Date eq 2019-12-29)" is not valid. clientRequestID:addfbf8c.............."
I believe it has something to do with - and / being used but if i try a substitute it changes the property type and still gives errors. please help.
Two points to make.
1) Only filtering for a NULL date is delegable in SharePoint. Otherwise filtering on dates is always non-delegable, even with '='. Here's the footnote from the documenation. Null assignment is delegable for a DateTime and Date field. Actual DateTime and Date values are not delegable. Filtering a DateTime or Date field for Blank is supported. Filter on not null is not supported. Only the = (not the <>) operator is delegated to SharePoint (the Not operator is not delegated.)
2) The other problem is that Date values are always Date and Time values. So even if the dates are equal the time portion probably isn't, so they aren't equal.
Here's the way to get around those issues.
Use a Nested filter that Filters the records in two steps. First, but Current User, which is delegable, and then filter the results, which are hopefully now below the limit, by the non-delegable date. To filter the date put both sides of the filter equation to get just the date as a string. Like this
Text('WE Date',ShortDate) = Text(currentWEDate,ShortDate)
The filter for current user alone is not enough to push under the delegation limit (500) it's possible that within just the first week the returned values for the currentUser alone could reach above 500 (approx 650) so even raising the limit to 2000 would only work for 4 weeks or less. Thats why i wanted it to only display results for a specified date AND for a specified user.
there is a third value that i'll be filtering for as well, that'll be branch Number. But If i leave the filter for only CurrentUser and Branch number, it would still push about 2600 results over 52 weeks. I'm pretty sure i need that Date to be part of the filter.
If you have control of the underlying SharePoint list you can create a column that is automatically populated when a record is created or changed with the string representation of the date entered. Then you can filter for it using a delegable filter.
I tried... but im horrible at the syntax for creating a calculated column on sharepoint.
i go to List settings > create Column
Give it a Name
Type of Info - Calculated
Formula - ????? (guessing something like "=Text(['WE Date'])"
Data Returned - Single line of text
Unfortunately, calculated columns can't be used in a delegable filter either. You would need to have something like a Power App that creates or edits records and have it generate the value to be stored in a regular text column.
I solved it by making my date column a single line of text column. whenever the date is patched, it is patched as a text value. This made the "date" delegable as a filter because I'm filtering a text value now.
What happens, is onvisible the collection is populated for the specific user with the specific date range. those two filters should absolutely never yield more than 250 results. if for any reason i need the date value of the text, i can more easily convert it over then.
Hi @KyleJ ,
Thanks for sharing your resolution here.
This will be a conference that you do not want to miss!
Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.
Featuring samples like Return to the Workplace and Emergency Response Applications
Features releasing from October 2020 through March 2021