Power Platform Community Forum Thread Details

Hello!

I have a canvas app with a sql table as the primary source and a Sharepoint list for combo box options.

On app start, I create a collection titled reference from the Sharepoint List. In the reference collection, there is a column titled PrimaryCaseType. This column contains 9 rows of text values.

In the EditForm of the canvas app, I have a multi-select combo box (cmbCaseType) that points to the PrimaryCaseType column in the reference collection. On Update, the selected value(s) write to a SQL database column, Primary_x0020_Case_x0020_Type. If a user selects multiple case types, the selections are stored as comma separated values. (Side note: I have since learned my lesson on spaces in sql field names)

Now the issue...

I am trying to filter records in a Browse Gallery by using a multi-select combo box for Case Type (cmbCaseTypeFilter).

For records that have multiple case types (comma separated values in SQL), I want the gallery to return records where any values match what is selected in the combo box.

Example: Record 1 case type = blue, Record 2 case type = blue, green. When a user selects blue in the cmbCaseTypeFilter, I want both cases to appear in the Gallery; if a user select green in the cmbCaseTypeFilter, I only want record 2 to appear.

In the Items property of the Gallery I have the following:

Filter(Primary_x0020_Case_x0020_Type in cmbCaseTypeFilter.SelectedItems.PrimaryCaseType || IsBlank(cmbCaseTypeFilter.SelectedItems.PrimaryCaseType))

The filter correctly returns records that contain a single entry in the SQL Primary_x0020_Case_x0020_Type column but omits all records that contain comma separated values, even when the filtered selection matches one of the values for that record.

I have also tried the following:

Primary_x0020_Case_x0020_Type in Concat(cmbCaseTypeFilter.SelectedItems, PrimaryCaseType & ", ") || IsBlank(Concat(cmbCaseTypeFilter.SelectedItems, PrimaryCaseType & ", "))

and

If(Not(IsBlank(cmbCaseTypeFilter.SelectedItems.PrimaryCaseType)), Trim(Primary_x0020_Case_x0020_Type) in Concat(cmbCaseTypeFilter.SelectedItems, PrimaryCaseType & ", "))

These did not work either. Any help is GREATLY appreciated!

@AhmedSalih

@WarrenBelz

Categories:

Building Power Apps

@CarrieSperls, and I used this formula to be able to retrieve data records using ComboxBox multi-selected items as the search criteria in a Column that holds delimiter-separated values.

Clear(colFilteredTable);
ForAll(
 ComboBox1.SelectedItems As Options,
 Collect(
 colFilteredTable,
 Search(
 colTable,
 Options.Value,
 "itemType"
 )
 )
);
ClearCollect(
 ColDistinctFilter,
 Distinct(
 colFilteredTable,
 itemType
 )
)

If my reply helped you, please give a 👍 , & if it solved your issue, please give a 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

New Project.png