Alternative to the ‘in’ operator for Power Apps when filtering SharePoint data

Many of you may already have faced some delegation limitations when using SharePoint as a data source for your canvas apps.

When the operation or filter is delegable, it means the query results are processed by the data source and returned to Power App rather than being processed locally in the App, with a maximum of 2000 items to be returned per call. For performance reasons, the default data row limit for non-delegable queries is only 500 items per call but can be increased to 2000 items. If a data source has more than the configured limit and a query with non-delegable operators or filters is executed, inaccurate results will likely be retrieved.

An operator that is not delegable and is missed is the ‘in’ operator (as a test of membership).

I have seen some approaches around the web to simulate the behaviour of an ‘in’ operator in Canvas apps with SharePoint as a data source, but some of them are not based on dynamic selection or are based on hiding or showing content and not based on techniques to filter data differently.

An example of content to be filtered where issues may happen

In the example below, a demo list was created with almost 60000 items.

There are more than 2000 items related to each Ireland county (it’s a lookup field to another list) so that it’s possible to visualise how the filters with delegation issues don’t work properly and how the approach shown later in the article can help to overcome this limitation.

In the following examples, this list will be filtered by the County field, based on a multiple selection combo box. The idea is to filter list items that have any of the chosen filters as County.

Filter example with the delegation issues: “in” Operator

When default ‘in’ operator based in a combo box selection is used in a formula to filter SharePoint list items, the ‘in’ operator is highlighted with the delegation warning:

In all Power App examples used in this post, the count of items will be shown in a label on top of the gallery for better understanding. When selecting a county among the first results returned by the data source, the proper results are retrieved (limited to 2000 items). But if any other county is selected, zero items are returned.

This is due to the issue explained above, Power Apps gets the first results from the data source (first 2000 items from the SharePoint list), and then applies the filter locally in the records that were retrieved. As ‘Cavan’ was not among the first 2000 results, nothing is retrieved.

How to implement a membership filter what will work as the ‘in’ operator

The idea of this approach is to iterate through the chosen filters using a ForAll function. Then query the data source using the delegable equals ‘=’ filter for each of the filter values and return the query results (it works like a ‘map’ operator).

By only using the ForAll and the combination of the filter, it works but the results are obtained in groups (per county filtered). This could be easily used with nested galleries, as in the picture below (the inner gallery uses the child records collection as items):

To make the results become a flat collection, we can use the Ungroup function. By doing this, the result of the ForAll loop can be ungrouped and all items will then lie at the same level.
The limitation is that a maximum of 2000 items can be retrieved per data source call. The batch load that normally works for galleries when they are scrolled will not work when using the formula below. This is due to the results being retrieved and generated once, as even the Filter function is delegable, ForAll is not.

By using the above formula as the items property for a gallery, when filtering the list by multiple filters thousands of values can be retrieved.

This approach also has the limitation of the maximum of 2000 items per specified value in the collection of values to be compared as a filter. If the real number of items in the list is more than 2000 obtained by PowerApps, only the first 2000 among the matching records will be retrieved.

For example, the following SharePoint list view for the demo list is filtered by County ‘Cavan’. The item count has more than the results retrieved by the app (2294).

If the list is filtered by County ‘Cavan’ and status ‘Active’, 803 items are found:

If filtered by two counties (‘Cavan’ and ‘Carlow’) and Status ‘Active’, 1806 items would be found:

By applying additional filters in the Power App formula, the query will be more precise, and the right results are obtained, for example, you could modify the formula to formula mentioned earlier to filter by Status ‘Active’:

If this formula is used as the items property in a filtered gallery, the correct number of items is loaded:

Conclusion:

The approach demonstrated in this post works better than using the ‘in’ operator but has some drawbacks, so use it carefully.

For each item used as a filter in the loop, a data source call will be executed. Which means that for a big collection of filters it may not work fine due to the number of items retrieved, and the number of calls done.

Imagine a case with 20 values selected as filter and 2000 items retrieved per item…40000 items would be returned in the full operation, which would be a heavy operation for both the app and the data source. For better performance, it is recommended to use some delegable filters in combination with this technique in the Filter function call or reduce the number of items in the collection used in the ForAll function.

Even though SharePoint online has automatic indexes activated by default on new lists, which allows your queries to overcome the SharePoint 5000 items threshold by filtering on indexed fields, there is a hard limit of 20 indices per list. Plan your indices accordingly so you make sure the most important ones will be set up.