Blog

SharePoint - Use this trick to perform a 'contains' type search in a more delegable way

With SharePoint, it's difficult to perform a 'contains' type search that returns all matching records where the search term appears anywhere within a field, due to the fact that the search function is not delegable. A possible hack is to perform the search through a look up column. This enables us to carry out a delegable 'contains' type search against lists of up to 5,000 records. This post describes this technique in more detail.

In my previous post, we looked at how to use the Choices function to filter SharePoint lookup items that start with a specified search term.

Another undocumented and powerful capability of the choices function is that it appears to be delegable when used in conjunction with the search function.

One reason why this is powerful is because it enables us to carry out a contains type search against a column. This type of operation would usually not be delegable with SharePoint data source. This post looks at this undocumented feature in more detail.

What's the problem with searching text in SharePoint?

As a demonstration, lets take a example of a SharePoint list of properties. Our aim is to return all records that include the word 'avenue' in the address field.

If we use the search function, the result is not delegable. With the default limit of 500 records, this function returns only the records that contain the word 'avenue' in the first 500 rows, as the screenshot beneath shows.
 
Search(Property, "avenue", "Title")



How does Search behave through a LookUp field?

Let's now take our SharePoint 'Property Documents' list, which includes a lookup column that's based on the 'Property' list.



Instead of using 'Property' as a data source, let's now access the property list through the 'Property Documents' list by calling the Choices function.
 
Search(Choices(PropertyDocument.Property), "avenue", "Title")



As the screenshot above shows, there is now no delegation warning and the results contain all the expected records beyond row 500.

Are there any limitations to this hack?

Are there any limitations with this technique? One limitation is that SharePoint also imposes a view threshold data retrieval limit which places a hard limitation on the number of records that we can return through the Choices function.

As an illustration, the screenshot beneath shows the application of this technique against a client list with 40,000 records. The Choices function will refuse to work when number of records exceeds the threshold limit, which by default is 5,000 rows.

There are more details about SharePoint threshold limits in my post here:
http://powerappsguide.com/blog/post/large-sharepoint-list-fix-list-view-threshold-error


Another notable point is the documentation explicitly states that Search/Choices is not delegable. It's very possible that this documentation is out of date, but we should also be cautious on relying on a technqiue that Microsoft claim to be unsupported.

The documentation for the Choices function is here, and the screenshot beneath shows an excerpt:

Conclusion

With SharePoint, it's difficult to perform a 'contains' type search that returns all matching records where the search term appears anywhere within a field. One workaround is to perform the search through a look up column. We can create a list with a look up column specifically for this purpose. This technique enables us to carry out delegable searches on lists of up to 5,000 records (due to SharePoint view threshold limits).
Previous
Next
Related posts