SharePoint - Choice function now includes search capability
May 20. 2021
If you've been using Power Apps for some time, you'll be familiar with the Choices function - the function that retrieves lookup items from a SharePoint or Dataverse datasource. Following an update to Power Apps, this function now offers the built-in ability to filter the results. This post looks at how this applies to a SharePoint data source.
In a recent update to Power Apps (which is undocumented at the time of writing), the Choices function now includes a second parameter that offers the ability to provide search criteria. This post explores how this updated feature works.
Demonstration of the Choices function
To highlight how the Choices function works, let's take the example of a SharePoint list of 'Property Documents'. This list includes a lookup column that is based on a 'Property' list, as the screenshot beneath shows.
If we now add a data table control and set the items property to the formula beneath, we see how the Choices function returns a list of property records. It returns two columns - id and Value. The id value corresponds to the built-in SharePoint id column.
What type of Search can we specify with the Choices function?If we now specify a search term, what type of search can does the Choices function perform and on what target fields?
The Choices function will search on the Value column. To demonstrate, we'll set the search term to "road". The intention here is to return all property records with the word "road" in the address.
Choices(PropertyDocument.Property, "Road")The screenshot beneath shows the result. Perhaps disappointingly, it returns no records which indicates that the choice function does not apply a 'contains' type search.
When we modify the search term to search for the characters "30", the result returns all records that start with "30". This verifies that the Choices function performs a "starts with" search on the search criteria.
It's now possible to specify a search term when we call the Choices function. This will return only those records that start with the specified search value. This feature enables us to more easily filter the results from Choices function, compared to the traditional method of wrapping a call to Choices inside a call to Filter.
- Search - Applying multiple AND/OR conditions to the Search function- Workaround
- Search - How to filter records by date ranges (eg today, this week, this month, etc)
- Search - How to highlight search terms in search results
- Search - Filter records using A-Z control on a Canvas app
- Search - How to filter by multiple fields and return all records when no search term is entered