Filtering combo box

[Anonymous]

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?

[WarrenBelz]

Hi @Anonymous ,

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?

[Anonymous]

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.

[eka24]

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

[WarrenBelz]

Ok @Anonymous ,

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.

[KennethNicasens]

@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.

[WarrenBelz]

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.

[KennethNicasens]

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 )

[WarrenBelz]

Thanks Kenneth,

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

If @Anonymous 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.

[Anonymous]

@WarrenBelz , 

Gave it a shot but I am now encountering this:

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