Filtering a gallery(attached to a sharepoint site) based on multiple choice selections

[AD1990]

Hi,

I have a sharepoint list called 'Teachers'.

It has a choice type field called 'Subjects' and multiple selection is allowed.

Individual subjects choices are:

(Poetry)

(Music)

(Guitar, Bass and Music theory)

(Guitar design, Guitar building and Luthier)

(Robot dance)

(Nunchucks)

etc etc

So typically this is how the Teacher table looks:

Name(people)          || ID(number) || Subjects(choice-multi selection is allowed)

Mikael Akerfeldt       || 10456         || (Poetry), (Music), (Guitar, Bass and Music theory)

Buckethead              ||  10478        || (Guitar, Bass and Music theory), (Music), (Robot dance), (Nunchucks)

Tosin Abasi               || 10982         || (Guitar, Bass and Music theory), (Music), (Guitar design, Guitar building and Luthier)

Now I have a gallery where I want to filter the teachers who have the selected multiple subjects on offer.

For example:

If I select (Poetry),(Music) then Mikael Akerfeldt should show up.

If I select (Robot dance), (Nunchucks) then Buckethead should show up.

If I select (Guitar design, Guitar building and Luthier) then Tosin should show up.

If I select (Guitar, Bass and Music theory), (Music) then both Akerfeldt, Tosin and Buckethead should show up.

Previously I had to search with only one subject so filtering the gallery was easy.

Now I need to be able to search with multiple subjects and the gallery should filter data showing the teachers who have all those subjects to offer. It should show no data if there is no match(no one is offering the selected multiple subject choices).

Any suggestions?

[WarrenBelz]

Hi @AD1990 ,

You have a many-to-many relationship there and need a collection

Clear(colTeachers);
ForAll(
   ComboBoxName.SelectedItems As aTeach,
   Collect(
      colTeachers,
      Filter(
         Teachers,
         aTeach.Value in Subjects.Value
      )
   )
)

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.

Visit my blog Practical Power Apps

[AD1990]

@WarrenBelz Stupid question but where should I put the code that you wrote?

In the items property of the Gallery or in the OnSelect property of the combobox?

[AD1990]

@WarrenBelz 

I put in the code in the OnSelect property of the combobox to see if the data is getting picked up.

aTeach is storing the subject in a table format.

But the colTeacher collection is showing 'we didn't find any data'.

So it seems the comparison is not working. 

[WarrenBelz]

Hi @AD1990 ,

OnChange of the Combo Box is probably a good spot then use the Collection as the Items of the gallery or drop-down you want to display the data in. If on the OnChange, you can use Self.SelectedItems

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.

Visit my blog Practical Power Apps

[AD1990]

@WarrenBelz 

The exact code wasn't displaying any data as the collection(colTeacher) was coming up empty as I mentioned in my earlier message.

Clear(colTeachers);
ForAll(
   ComboBoxName.SelectedItems As aTeach,
   Collect(
      colTeachers,
      Filter(
         Teachers,
         aTeach.Value in Subjects
      )
   )
)

 I modified this line to:

aTeach in Subjects
or
aTeach.value in Subjects.value

Now this is displaying the same data in both the cases but it's just showing the list of all teachers now. It's not comparing and showing the exact data.

[WarrenBelz]

@AD1990 ,

Note amended code in my post as Subject is a Choice column.

The code should collect the records of all teachers where their subject is one of those selected in the Combo Box. (think about it yourself - it is looping through the selected items and finding those where they match).

[AD1990]

@WarrenBelz Subjects is a choice column and teacher can select multiple column. The collection was coming up empty with the initial code. And now with the changed code, it's comparing the subjects one by one and hence it's showing all the teachers.

The ideal result should be the collection should hold only the rows where the selected subjects(more than 1) exactly match the subjects offered by the teacher.

I think I was not able to explain it.

If the students search with more than 1 subjects(eg: Music, Guitar), the results should show exactly the teacher/s offering Music, Guitar as the subjects. And not the other teachers who are offering either one of the subjects.

[WarrenBelz]

Hi @AD1990 ,

My brain hurts thinking about it (Power Apps is not SQL). Right now, I will raise the white flag - I do not believe it is possible. Your initial post was also certainly not clear on this from a reader's point of view.

[AD1990]

My apologies for not being able to explain. 
I'll see what I can do.

Thank you for your time and responses.