Many on Many Filter

[WarrenBelz]

This article discusses the example of a multi-choice field in SharePoint with a multi-choice Combo Box in Power Apps filtering the possibility of ANY selected field in the Combo Box matching ANY stored value in the list which is stored in a Complex (table) field.

Many on Many queries cannot be done directly in a Filter, but in the example shown below, a gallery is produced showing all cases where the fields match, I have also included the ID number of the record to demonstrate that no duplicates have been chosen.

In this example, the field queried is the same field that the combo box is linked to, however this principle is not limited to this situation.

Firstly, the top video is a demonstration of it working in real time. The below are examples of the results of various choice combinations.

So what is happening here?

Firstly, on the OnChange of the Combo Box, a Collection needs to loop through the selected items and look for matches over in the field in the list. Note something very important here – the in filter is not Delegable in SharePoint and the With() statement has only been used to get rid of the Delegation warning, so

• if the list is over 2,000 items AND
• you cannot filter the list with a Delegable filter within the With() statement to under this number AND
• you cannot do a big collection (see this blog)

then this process will not work for you.
The code on the OnChange of the Combo Box below gets the initial collection. The list name here is TestFields and the multi-choice field is MultiChoiceTest. The collection name I have used is colMyCol

Clear(colMyChoices);
With(
   {wTest: TestFields},
   ForAll(
      cbMultiChoice.SelectedItems,
      Collect(
         colMyChoices,
         Filter(
            wTest,
            Value in MultiChoiceTest.Value
         )
      )
   )
)

 It is looking at each selected item in the Combo Box and selecting any record that contains that value in the multi-choice field. This can create duplicates, as noted below.

Now the Items of the Gallery

Sort(
   GroupBy(
      AddColumns(
         colMyChoices,
         "MCT",
         With(
            {
               wMCT: 
               Concat(
                  MultiChoiceTest,
                  Value & ", "
               )
            },
            Left(
               wMCT,
               Len(wMCT) - 2
            )
         )
      ),
      "ID",
      "MCT",
      "OtherData"
   ),
  ID
)

The important thing here is the GroupBy() Statement, which Groups by ID (to delete duplicates), but also needs to Group by the record column being searched to make it available in the gallery (it will always be the same for each ID anyway).

As it is not possible to group by complex field types, these have to be turned into Text, hence the AddColumns() with the Concat() statement.

The With() statement just gets rid of the last comma and is not really necessary except for appearance.

The two fields in the gallery are ID and MCT. The rest of the list data is available in the table OtherData.

As mentioned, this process could be modified for any table of values querying another table-type field.