cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filtering combo box

I’m trying to accomplish in setting up a combo box With filtered items. Problem is, I need the filtered expression to not just look at an exact =‘s match, but a ‘contains’ and based upon a different text box input field. Any thoughts how to accomplish that?
10 REPLIES 10
Highlighted
Super User
Super User

Re: Filtering combo box

Hi @ksagert ,

The In function can do this as well as the Match function.

I am assuming you want the Items property to be a filtered list based on a value in another control?

Highlighted
Frequent Visitor

Re: Filtering combo box

Exactly. As you said it, I’m wanting that filtered list in the combo box based on the value in the text box to display a list of options from a column in a SharePoint list where the column in SharePoint to contain, somewhere the exact value of the text box.
Highlighted
Super User
Super User

Re: Filtering combo box

Generally use:
Filter(TableName,Textfield.Text, in ColumnName)
You can replace those field names with what you have as actual name.
Highlighted
Super User
Super User

Re: Filtering combo box

Ok @ksagert ,

There are delegation issues if the data set you are querying is over 2000 records. You need to Collect a set under this number to query if you use the In operation - Match is not really an option on second thought.

I am also wondering whether there are duplicate values in the SharePoint list that also need to be rationalised to present to the dropdown. The Distinct function is also not Delegable.

So firstly assuming you can collect this list (I will call it MyList)

ClearCollect(colList, MyList)

I will call the text box SearchText and the field to be searched in MyList will be MyDDRef.

So your dropdown will have Items something like this

Distinct(
   Filter(
      ColList.MyDDRef,
      SearchText.Text in MyDDRef
   ),
   MyDDRef
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Highlighted
Resolver III
Resolver III

Re: Filtering combo box

@WarrenBelz 

 

If you collect this in a local collection you would also only fetch the first 2000 records ( depending on the advanced settings : 1-2000).

If the list contains more than 2000 items you can loop around to fetch every record ( the performance is not great ).

Tell me if this is the case and I will post some code to fetch all the data.

Highlighted
Super User
Super User

Re: Filtering combo box

Thanks @KennethNicasens ,
Yes , please add your working version of this.

I generally try to see if version “A” below applies and then see if a suitable data set can be formed with a delegable query. I have done some concatenated alphabetical exercises when necessary, but am certainly interested to see your thoughts. Also I find the bigger they get, the more issues with mobile devices.

Highlighted
Resolver III
Resolver III

Re: Filtering combo box

Here is my code: 

UpdateContext({_CurrentlyLoading: "Customers"});
UpdateContext({_ProgressText: "Calculating Data"});
//GET THE FIRST ID
UpdateContext({_MinRecID: First(SortByColumns(Companies, "ID", Ascending)).'Number ID'});
//GET THE LAST ID
UpdateContext({_MaxRecID: First(SortByColumns(Companies, "ID", Descending)).'Number ID'});
//CALCULATE THE NUMBER OF LOOPS - / 100 will mean that you will import them by 100 records
UpdateContext({_NumberOfPasses: RoundUp((_MaxRecID - _MinRecID) / 100,0)});
//DataFilterPer100 is an excel table with records like 1 - 0 - 100 / 2 - 101 - 200 /...
ClearCollect(colNumbersTable,Filter(DataFilterPer100, Number <= _NumberOfPasses)); 
//CLEAR COLLECTION
Clear(colLocalCustomers);
UpdateContext({_ProgressText: "Downloading Data"});
//LOOP ARROUND WITH FILTER ON ID ( BASED ON EXCEL TABLE )
ForAll(colNumbersTable,Collect(colLocalCustomers,Filter(Companies, 'Number ID' > MinFilterNo And 'Number ID' <=  MaxFilterNo)));
UpdateContext({_ProgressText: "Saving Data"});
// IF ON MOBILE YOU CAN SAVE THE DATA FOR OFFLINE USE
SaveData(colLocalCustomers,"Customers");
UpdateContext({_ProgressText: "Saved Data"});

 

Note that this is as you say not performant on a mobile device ( depending the device )

Highlighted
Super User
Super User

Re: Filtering combo box

Thanks Kenneth,

Without wanting to deviate further from this thread topic, thank you.

If @ksagert ends up with a Collection solution, I should be able to help, otherwise, I will defer to your expertise and tag you in on the subject.

Highlighted
Frequent Visitor

Re: Filtering combo box

@WarrenBelz , 

 

Gave it a shot but I am now encountering this:

image.png

 

With the error: Invalid schema, expected a one-column table. 

 

Which got me thinking, is is possibly an issue because I have special characters such as ; and # in MyDDRef as value separators? MyDDRef contains values such as:

Category Management;#2;#Inventory Management;#1

 

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,141)