Sort Function Breaks Gallery

[Trachypithecus]

I've got a data entry app that collects information using a handful of inputs and creates a new record in a SharePoint list using the patch function.  It also contains a filtered gallery that displays the last 7 days worth of entries for an easy reference of what's been recorded recently.  It's been working perfectly fine for months until just recently where the gallery no longer displays anything.  The only thing that I can tell that may have changed is that the list crossed the threshold of 5000 records, but that shouldn't matter because the gallery filters the SharePoint list to where it should never display anything more than a dozen or so records.

Poking around, the thing that appears to be the breaking point is that I have the gallery sort by date so that the most recent entries were at the top.  If I delete the 'Sort' code the gallery populates and displays records again, but put it back in and it just goes blank without showing any errors or anything.

This is the original code that spontaneously stopped working correctly:

Sort(Filter('Data Entry', Date=DateAdd(Today(),-6,TimeUnit.Days) || Date=DateAdd(Today(),-5,TimeUnit.Days) || Date=DateAdd(Today(),-4,TimeUnit.Days) || Date=DateAdd(Today(),-3,TimeUnit.Days) || Date=DateAdd(Today(),-2,TimeUnit.Days) || Date=DateAdd(Today(),-1,TimeUnit.Days) || Date=DateAdd(Today(),0,TimeUnit.Days)), Date, SortOrder.Descending)

This is the code that successfully displays the records as desired (albeit out of order):

Filter('Data Entry', Date=DateAdd(Today(),-6,TimeUnit.Days) || Date=DateAdd(Today(),-5,TimeUnit.Days) || Date=DateAdd(Today(),-4,TimeUnit.Days) || Date=DateAdd(Today(),-3,TimeUnit.Days) || Date=DateAdd(Today(),-2,TimeUnit.Days) || Date=DateAdd(Today(),-1,TimeUnit.Days) || Date=DateAdd(Today(),0,TimeUnit.Days))

I can't think of any reason why a simple sort function should cause any issues.  Am I missing something?

[WarrenBelz · Accepted solution]

Hi @Trachypithecus ,

I suspect your list may be over your Delegation Limit - you can restructure like this to avoid any issues with Delegation

With(
   {
      _EndDate:
      DateAdd(
         Today(),
         -6,
         TimeUnit.Days
      )
   },
   Sort(
      Filter(
         'Data Entry',
         Date >= _EndDate && Date <= Today()
      ),
      Date, 
      SortOrder.Descending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Trachypithecus]

So initially trying that gave me the following error: "Error when trying to retrieve data from the network: The attempted operation is prohibited because it exceeds the list view threshold."  But then I went and indexed the Date column in the SharePoint list and it all seems to be working again as desired now.  And I figured there was probably a cleaner way write the code to filter the last 7 days, so thank you for that as well.

I guess I still don't understand why it was the inclusion of the Sort function that broke the gallery.  Isn't Sort a delegable function?  Or why it would cause issues at the list view threshold of 5000 instead of at the delegation limit of 500.

[WarrenBelz]

Hi @Trachypithecus ,

Your issue was the second one (SharePoint 5,000 item threshold) - you need to index any columns involved in queries once you exceed this. If is also a good idea to get calculations out of filters (hence my With() statement use)