Avoiding delegation in nested Filter function

[acampbell39]

I'm new to Power Apps, apologies in advance if this is simple!

I have two SharePoint lists: Buildings and Contacts. Buildings can have a list of multiple contacts, and vice versa. I have a third SharePoint list which is just a list of relationships between these two datasets, e.g. (of course, in reality I'm using unique IDs, not names):

Building    Contact
--------    --------
Home        Alice
Home        Bob
Home        Charlie
Work        Alice
Work        Charlie

I've created a Gallery which the user will use to select their desired Building. The selected Building is passed to another Gallery which shows all of the Contacts associated with that Building. To do this, I have this set as the Items property of the second Gallery:

Filter(Contacts, ID in Filter(Relationships, RelationshipsBuildingID = Gallery_BuildingsBrowse.Selected.BuildingsID).RelationshipsContact)

As an example, if I click the "Work" building on the first gallery, the second gallery populates with "Alice" and "Charlie".

Unfortunately, whilst this works, I have a lot of relationships (about 3000) and this will go over the delegation limit - I think that the in operator is at fault here. I sense there might be a way around this using a collection, GroupBy or SortByCol, by I just can't get my head around the syntax!

Any help would be much appreciated. Many thanks.

[WarrenBelz]

Hi @acampbell39 ,

If your relational query need to return only one value, this should work

With(
   {
      wID:
      LookUp(
         Relationships,	  
         RelationshipsBuildingID = Gallery_BuildingsBrowse.Selected.BuildingsID
      ).RelationshipsContact
   },
   Filter(
      Contacts, 
      ID = wID
   )
)

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

[acampbell39]

Thanks for your input @WarrenBelz 

I've tried your suggestion and it returns one value, as you said. Unfortunately, this is not what I'm looking for as I want the second Gallery to display all of the associated Contacts for the selected Building

[WarrenBelz · Accepted solution]

Hi @acampbell39 ,

I think this is close - you have a many-to-many relationship and need to make a Collection, which would then be the Items of the Gallery. You should be able to run this on Gallery_BuildingsBrowse OnSelect

Clear(colContacts);
With(
   {
      wIDs:
      Filter(
         Relationships,	  
         RelationshipsBuildingID = ThisItem.BuildingsID
      )
   },
   Collect(
      colContacts,
      ForAll(
         wIDs,
         Filter(
            Contacts, 
            ID = RelationshipsContact
         )
      )
   )
)

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

[acampbell39]

Thanks @WarrenBelz that has worked!

For anyone trying this solution for themselves, this code generates is a collection of tables. To access the values (e.g. for labels in a gallery), I used the following for the label Text property (replace <Column_Header> with the header of your choice):

First(ThisItem.Value).<Column_Header>