Data

Many to Many Filters

16/01/2021 / 5 Comments

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 can be done directly in a Filter, but are not Delegable. To do this, firstly a Table needs to be created using ForAll addressing each of the Combo Box selected items to see if any are contained in the multi-select Choice field

ForAll(
   ComboBox.SelectedItems As aSel,
   Filter(
      SPListName,
      aSel.Value in FieldName.Value
   )
)

The output of the Combo Box has been assumed as .Value. If it is based on a field name, change this. We are not quite there yet as this structure produces a Table field called Value for each itineration of the ForAll , so this need to be ungrouped

Ungroup(
   ForAll(
      ComboBox.SelectedItems As aSel,
      Filter(
         SPListName,
         aSel.Value in FieldName.Value
      )
   ),
   "Value"
)

This will produce a Table of all matching records in your list

5 Comments

  • Matthew L

    06/12/2021 at 9:10 pm Reply

    Hi

    Appreciate your article it’s helped me solve a many to many combobox query from a multichoice column in Sharepoint. However a couple of things are still not clear for me…

    Firstly, I will have to hold my hands up to my level of competency. This is on the edge of my understanding (hence why I’m struggling).

    “The two fields in the gallery are ID and MCT. The rest of the list data is available in the table OtherData.” – I have the ID and the “MCT” working in my gallery – I don’t really understand what is meant by the table ‘OtherData’. Where is the OtherData table? In the Collection? I Don’t see it.

    Adding a new text field to my gallery (for instance ThisItem.Title). This doesn’t work as I’m normally used to…is that because I need to add to the gallery Items?

    Also, last thing…I have changed the OnSelect(Navigate) off the shelf configuration and used Set(varCurrentRecord, ThisItem);Navigate(DetailScreen1), ScreenTransition.None) – as per the guide here http://powerappsguide.com/blog/post/best-practice-for-setting-form-item

    However, my new gallery created using your blog doesn’t work. The navigation shows an error “Invalid Formula. Expected a value compatible with ‘DataSource’

    Any guidance appreciated.

    Matt

    • Warren Belz

      06/12/2021 at 10:03 pm Reply

      Hi Matt,
      I can give you a couple of answers to your questions, but solving problems is more the job of the Power Apps Community
      Firstly OtherData (you can call it whatever you want, but it must be on the end of GroupBy fields defined) is a table field resulting from the GroupBy function where all the field records matching the grouped records are stored – it is an essential element of GroupBy
      As for the record, I always use Set(gblID, ThisItem.ID) from a gallery and then for the Form Item Lookup(ListName,ID=gblID) – it addresses the record in the list directly and refreshes automatically when any changes are made.
      My learning curve was like yours (it is just over three years), but I always (still) pause when I find something new and understand it properly before moving on.

  • Sara

    28/01/2022 at 8:06 pm Reply

    Hi Warren Belz,

    This is something I’d like to manage to do. My level of understanding is not here yet. I managed to do a collect with the fields of Email from he Users in 365. (We will only be able to choose one email.) But struggling with the filtering of those user emails in a gallery that has a many-to-many relationship with the users. SHould your above example work and do you have a more step by step guide?

    Thanks in advance,
    Sara

Leave a Reply

Your email address will not be published. Required fields are marked *