SharePoint Contains Search Delegation Challenge in PowerApps

Navigating the intricacies of SharePoint, particularly when dealing with the ‘contains‘ search delegation issue, can be complex. This guide delves into effective strategies for bypassing the limitations posed by the non-delegable nature of SharePoint’s ‘contains‘ search in PowerApps. We will explore how using a lookup column can effectively resolve this common problem, ensuring you can retrieve comprehensive search results without hitting delegation warnings.

If at any point, clarity is required or technical difficulties arise, feel free to reach out to us.

The Limitation: Searching Text within SharePoint

Imagine a scenario where a SharePoint list of properties exists, and the goal is to extract records containing the term ‘avenue‘ in the address column. Using the traditional search function, you’d run into the delegation warning in PowerApps. This confines the result set to the initial 500 entries.

Search(Property, "avenue", "Title")

Utilizing the LookUp Field for Search

Assuming there’s a SharePoint ‘Property Documents’ list which features a lookup column derived from the ‘Property‘ list. Instead of tapping into ‘Property‘ as a data source, one could fetch data from the property list via ‘Property Documents‘, employing the Choices function.

Search(Choices(DocProperties.Property), "avenue", "Title")

Remarkably, using this method lifts the delegation warning, pulling in a comprehensive result set beyond the initial 500 rows.

Reference: Search functions

Possible Restrictions

However, this technique isn’t without its constraints. SharePoint enforces a view threshold on data retrieval, capping the record count returned via the Choices function. For instance, if applied to a client list with 40,000 entries, the Choices function will hit a wall once it crosses the 5,000-row threshold.

Moreover, it’s crucial to note that official documentation from Microsoft clearly mentions that the Search/Choices combo isn’t delegable. While the documentation might be outdated, depending on an approach that’s not officially backed demands caution.

Conclusion

While SharePoint’s built-in capabilities limit ‘contains’ type searches, ingenious methods like using lookup columns open up doors to solutions. It’s a useful technique, especially when dealing with lists under 5,000 records due to SharePoint’s view threshold. As with all workarounds, understanding its limitations and potential pitfalls is crucial. And for those times when things seem uncertain, expert assistance is just a click away.