Filtering SharePoint lookup fields in Power Apps combo boxes by more than one related field

When we use a SharePoint lookup field in Power Apps forms, the default filter for the combo box control that is created automatically when adding a lookup field to a form will filter items only based on the primary lookup value. Sometimes it can be useful to search on more list fields, especially when we create only the custom form using Power Apps and the lookup field has projected fields configured for the list and those values are displayed on the main list.

But we can do some tricks to filter by more fields by using the StartsWith function instead of the default search.

Check this post to see how to do it.

Add the lookup source as another data source for your app/form

To achieve this, we need to add the lookup source list as another data source to the app.

The first step is to update the combo box items formula, using a new filter. Replace the default Choices formula with a Filter formula like the below (using your own fields/source list):

Filter(Cars,
    IsBlank(Self.SearchText)||
    StartsWith(Title,Self.SearchText)||
    StartsWith(Make,Self.SearchText) //Add more fields here if needed
)

This example uses a lookup field in which the source is a list named Cars and filters the items either by the Title field, or the Make field (both Single Line of Text). And if no search text is added, lists all records as usual.

In the example, we are using only two fields to filter, but more can be added if needed.

Update the Datacard ‘Update’ property

As we changed the data source now, it won’t bring the values in the format the Datacard expect to the Update property if we simply leave it as is, so we need now to pass a custom JSON object instead. What matters here is only to pass the lookup related Id, so it is saved properly:

Update the Combo Box ‘DefaultSelectedItems’ property

For the change to work, we also need to tweak the ComboBox formula for the DefaultSelectedItems as in the sample below. Construct a table with one item setting the Id and Title (or your display field) based on the value from the current item:

Update the combo box layout and Display fields

Change the combo box layout and Display fields (nicer to have both searched values displayed, so the recommendation is to use the ‘Double’ layout):

Update the Search fields

Make sure the combo box has searching enabled, sometimes by changing the Items property value it’s auto disabled by Power Apps:

And then update the values for the SearchFields (they have to be the same used in the filter formula):

Results

Now if you filter the combo box either by the first or second searched fields, you will get the desired results:

And items are saved and edited correctly.

Note: Even though you get delegation warnings, don’t worry. As the Items property will be filtered as you type in, the ‘Search‘ default function will run on only the pre-filtered items. The disadvantage of this approach is that the first filtered list (Items property) is based on the StartsWith formula, so it will work based on the items having the specified properties starting with the filtered values.

This example is targeted to single selection lookup fields but could be tweaked for multiple selection lookups too.