Power Platform Community Forum Thread Details

I have a Lookup column in a SharePoint list, when I add a new row directly in the SharePoint List I can select this Lookup from a drop-down and there is a "Find" option, if I type any part of a Lookup value in the box it lists all the options for me to choose from where what I type is anywhere in the Lookup value.

I've configured a custom form in Power Apps for the List, using the Customize in PowerApps" option, it has setup a Form using Data Cards and for this Lookup field it has given me a Combo Box to make my selection. This also has a "Find" option, but it only looks at the start of the Lookup options when I type.

This is much more complicated to use because I can't simply type "Coffee" and choose from the 5 categories that contain "Coffee". But it's actually very difficult to use because these Lookup values are concatenated with an ID, so I need to start typing the ID number and if I knew that I wouldn't need the Lookup at all!

So my hopefully straightforward question is, is there an alternative to the Combo Box that will perform the Lookup Search in the same way as the default SharePoint List form does?

Categories:

Building Power Apps

Hi @PaulRockliffe

How large is/will be the lookup list? - if it's not going to exceed 2000 rows you could modify the Items property in the form to something like this (swap out red for your use case):

Filter(
Choices([@YourMainList].YourLookUpColumn),
Self.SearchText in Value
)

Note: you can increase the 'Data Row Limit' to 2000 in the settings

If the lookup list exceeds 2000, the above wont work as the 'in' operator is not delegable to SharePoint. In that case you may need to change the Current lookup column to a single line text column, but in your form, you could still use a combobox and set the Items property of that combobox to something like the following:

Filter(
YourLookUpList,
StartsWith(ID, Self.SearchText) ||

StartsWith(Category 1, Self.SearchText) ||

StartsWith(Category 2, Self.SearchText) ||

StartsWith(Category 3, Self.SearchText)
)

You would need to adjust this function to suit your use case in terms of list / column names and the number of fields you want to query.

Using Startswith and the or (||) operator like this against the separate (pre-concatenated) column values allows the query to be delegated to SPO regardless of the size of your list (e.g. I have tested up to 100,000 rows with now issues) - It's not a true contains search in the sense that it wont pick up an internal element of a word (i.e. 'off' in coffee), but you will pick up the start of each word used in your identifier, which is much better than just the start of just the ID.

Also for the latter, you would need to adjust the Datacard Update property to 'combobox.Selected.ColumnName') and the DefaultSelectedItems to {ColumnName: Parent.Default}

Hope this helps