cancel
Showing results for 
Search instead for 
Did you mean: 
WarrenBelz

Many on Many Filter

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.

MCEF.pngMCAB.pngMCBDF.pngMCACE.png

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.

 

Meet Our Blog Authors
  • PowerApps Community Mentor 2018, Practicing surgeon and former Professor of Surgery, University of Illinois, Chicago.
  • Jill of All Trades (Power Apps, SharePoint, PowerShell, Exchange, Power Automate and odds & ends of assorted other things)
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • I'm Tim Leung - a PowerApps builder. In addition to PowerApps, my main interests include building software with SQL Server and Microsoft .NET. I'm the author of 'Beginning PowerApps' - the first published book on PowerApps. You can buy this from Amazon and other leading bookstores.
  • Systems developer
  • My name is Timothy Shaw and I create digital solutions using the Power Platform, Office 365, and Azure SQL and handle the IT for a small company in the energy sector. When not building the future :), I enjoy playing guitar, good (or really bad!) sci-fi, Xbox therapy, and hanging with my wife and son. Twitter: @ShortForTim