Gallery Issue Delegation

[johnm_]

Hi,

Thanks in advance for the help 🙂 At first my app was working then the gallery module from sharepoint list stop working now.

I got this error below

Can someone help what does means?

Here's the code for this

On left gallery On items (filter)

Sort(
      Filter(
              Visitors_Master_List,IsBlank('Checkout Datetime'
                                    )
      ),'Name (Title)', SortOrder.Descending
)

This pull names of users that haven't check out on the app

On the right is name information on visible

!IsEmpty(
         Filter(
                Visitors_Master_List,IsBlank(
                                             'Checkout Datetime'
                                     )
         )
)

Can someone help why it stop working? Thank you! Sharepoint list entry is now on 505 items if that helps.

It's not pulling and showing info from sharepoint list now with all the issue icon (issue is reference above this post) (see below)

[BCLS776]

Since IsBlank() is not delegable to SharePoint, an easy workaround is to make your Filter() condition into

'Checkout Datetime' = Blank()

This should correct the delegation issue on your Filter.

Hope that helps,

Bryan

[johnm_]

can you let me know where to insert it? sorry I'm not of a coder. Thanks!

[johnm_]

I think the issue is it went over 500 items now as per this issue above. What to do if it go over 500?

Thanks!

[BCLS776]

No problem, change your Items property to this:

Sort(
      Filter(
              Visitors_Master_List,'Checkout Datetime' = Blank()
      ),
      'Name (Title)', 
      SortOrder.Descending
)

Bryan

[johnm_]

Hi Bryan,

The warning is still there and it didn't fix the issue. Deleting entries is not an option since this is a list of all visitors in our office. Thanks!

[johnm_]

Update:

So i tried deleting 6 items and it went back. If it goes over 500 the Gallery filter stops working.

Is there a fix for this? Thanks! (see below)

[BCLS776 · Accepted solution]

Yes, this issue you are seeing is what happens when a formula is not properly delegated to the data source.

Let's try this version and see if it works better for your situation:

With({aFilter: Filter(Visitors_Master_List, 'Checkout Datetime' = Blank())},
   Sort(
      aFilter,
      'Name (Title)', 
      SortOrder.Descending
   )
)

The original statement includes what may be the 'Title' column of this SharePoint List, which cannot be handled with delegation. This new statement pre-filters the table using a delegable filter.

Hope that helps,

Bryan 

[johnm_]

Great thanks @BCL this solved the issue for a minute then it came back!  😞

One more question, when the list reach the 5k threshold sharepoint limit will it mess up my app again? If so, is there a solution for this as well? Thanks!

[BCLS776]

---

@johnm_ wrote:

Great thanks @BCL this solved the issue! 

One more question, when the list reach the 5k threshold sharepoint limit will it mess up my app again? If so, is there a solution for this as well? Thanks!

---

Glad to hear we got it working!

Yes, Sharepoint Lists can experience filtering problems when they reach 5K items. This article explains how to use indexing to overcome this issue (in your case, set up an index at least on the 'Checkout Datetime' column) and do this now to avoid issues. After that, you should be good for 30M items.

Bryan