Using ClearCollect to avoid delegation errors in SharePoint PowerApps

The Problem

This is the typical delegation error you get in PowerApps with SharePoint Online. If SharePoint online does not know how to interpret your selection formula that you’ve specified for a PowerApps Gallery, then PowerApps will just return, by default, the first 500 items in the specified SharePoint list or Document Library, and let PowerApps apply a formula to those 500 items. You can increase this number from 500 to 2000, but if you have more than 2000 items, you’re going to run into trouble because some of them will be left out of the payload. Here is an example of a formula that produces a delegation error.

Filter(‘Contracts’,(Status.Value<>”Complete” And (IsMatch(Awaiting,User().FullName,MatchOptions.Contains) Or IsMatch(Awaiting,User().Email,MatchOptions.Contains)))

We get errors here on two fronts:

• SharePoint doesn’t know what to do with <>. You need to use =. <> is “not delegable.”
• SharePoint doesn’t know what to do with User().FullName or User().Email (those are PowerApps functions), so this, also, is “not delegable.”

Solution.

Break it up using ClearCollect and a second filter.

In the OnVisible event of the PowerApps pane containing the gallery, use this formula

ClearCollect(colOpenItems,Filter('Contracts',Status.Value="Draft" Or Status.Value="Plant Manager - Approval" Or Status.Value="In Process")

Then use the colOpenItems collection in the gallery’s Items property
Filter(colOpenItems,IsMatch(Awaiting,User().Email,MatchOptions.Contains) Or IsMatch(Awaiting,User().Email,MatchOptions.Contains))

This works now because the first ClearCollect will result in fewer than 500 items and the “=” is delegable to SharePoint (i.e. SharePoint knows what to do with and equals sign). For the second part with Filter, it works because the User.Email() and User().FullName functions are being performed on colOpenItems, which exists only in the PowerApp, not in SharePoint.

Note you could also do the usual Set(varUserEmail, User.Email()) in the onStart event for the app. If I try to use varUserEmail instead of User.Email() in the filter formula above, I get the error “Regular Expressions must be constant values.” Let me know if you know how to fix this; it seems to have something to do with being an argument to the IsMatch function. For now, the above formula works using User().Email and User().FullName