How to search in a specific multiselect dropdown (Sharepoint) column based on the checkbox selection?

[FabianStrelow]

Dear PowerApp community,

I am quite new to Powerapp and still try to figure out possibilities and limits.

What I like to do

• Creating a search function
• based on a Sharepoint list with several columns
• The columns are all multiple choice fields
• Two columns have same choice values (column1, column2)
  (- In general, often two columns belong together and have same choices)
• I like to have a combobox (cb) allowing the user to select one of the choices (not multiple), e.g. choice1
  (since there are the same, let's take the choices of column1)
• I like to create two checkboxes (cbx1, cbx2), (which are by default "true") with the column names:
• If cbx1 is selected, I like to search in column1 for the selected choice in the cb,
• If cbx2 is selected,  I like to search in column2  for the selected choice in the cb.
• If cbx1 & cbx2 are selected,  I like to search if the selected choice in cb, can be found in one of the columns (column1 & column2)
• If no value is chosen in the combobox, it should display 'all' and all persons should be found, that have an entry in column1 or column2 (depending which cbx are clicked - see above)
• Delegation can (probably) be neglected,-since the list has just round about 350 items.
• In addition: I like to create 3 other cbx (cbxcountry1, cbxcountry2, cbxcountry3), which allow the user to filter in addition based on the persons country (There are just three countries, more won't come).

How it should look like

Here is what it should like by default:	Here is an example after users entry:

Here is what I have so far:

For the Combobox:

Items = Choices([List].'Column1')               Is working fine

For the gallery:

I wanted the gallery to display the persons responsibilites (column1), which works quite fine with the function

Text = Concat(ThisItem.'Column1', Value & ", ")

For the gallery search:

• Filter('List',If(cbx1.Value,true) And If (cbx2.Value,true),
  cb.Value in 'Column1'.Value Or cb.Selected.Value in 'Column2'.Value)
  Is just showing results, when cbx1 and cbx2 are true - What is probably the case, due to the "And" function.
• Filter('List',If(cbx1.Value,true), cb.Selected.Value in 'Column1'.Value Or If (cbx2.Value,true),cb.Selected.Value in 'Column2'.Value)
  Does just work, if the selected choice, is in column2 and if both cbx are clicked.

Can some support me, to find the correct functions?

Many thanks in advance.

[ShaheerAhmad]

To create a search function based on a SharePoint list with multiple-choice columns and perform filtering based on checkbox selections, you can use the following approach in Power Apps:

1. Combobox setup:

   • Set the Items property of the combobox (cb) to: Choices([List].'Column1')

2. Checkbox setup:

   • Create two checkboxes (cbx1 and cbx2) with the column names as their Text properties.
   • Ensure that the Default property of both checkboxes is set to true.

3. Gallery setup:

   • Set the Items property of the gallery to: Filter([List], If(cbx1.Value, cb.Selected.Value in 'Column1'.Value) || If(cbx2.Value, cb.Selected.Value in 'Column2'.Value))
   • This formula filters the list based on the checkbox selections. If cbx1 is selected, it checks if the selected value in cb is present in 'Column1'. If cbx2 is selected, it checks if the selected value in cb is present in 'Column2'. If both checkboxes are selected, it checks if the selected value is present in either 'Column1' or 'Column2'.

4. Additional country filtering:

   • To add additional filtering based on country, you can extend the formula in the gallery's Items property. For example:

Filter(
    [List],
    If(cbx1.Value, cb.Selected.Value in 'Column1'.Value) ||
    If(cbx2.Value, cb.Selected.Value in 'Column2'.Value),
    If(cbxcountry1.Value, Country = "Country1") ||
    If(cbxcountry2.Value, Country = "Country2") ||
    If(cbxcountry3.Value, Country = "Country3")
)

1. • Replace 'Column1', 'Column2', 'Country', and the country values with the actual column names and country values from your SharePoint list.

With this setup, the gallery will display the filtered results based on the checkbox selections and additional country filtering. If no value is chosen in the combobox (cb), it will display all the records that meet the checkbox and country filtering criteria. The search will work accordingly, searching in the specified columns based on the checkbox selections.

Make sure to adjust the column names, field values, and checkbox/control names to match your specific SharePoint list and Power App configuration.

[FabianStrelow]

Dear Ahmad,

many thanks for your support.
The first part is working perfectly fine., although the gallery does not show any results, when cb is not selected. 😊

Filter(
    [List],
    If(cbx1.Value, cb.Selected.Value in 'Column1'.Value) ||
    If(cbx2.Value, cb.Selected.Value in 'Column2'.Value),

 
Unfortunately, I don't get the country selection to run.
Might it be, because the datasource is also a Choice column in Sharepoint?

Best regards,
Fabian