@sglund14 - some comments.
You can leverage Collections to merge multiple lists together into a single list, but this will have an adverse impact on app performance because all rows will be loaded into memory during the app session.
It would be performant to "relate" multiple lists together using a common identifier you can guarantee will be unique. In your scenario, this would be the Site ID.
Using the Location and Asset table as a basic example. I can see from the diagram that the relationship cardinality here is Many-To-One from the Location table (Many) to the Assets table (One).
For this scenario I would create two Gallery controls. Gallery1 is linked to Location, and Gallery2 is linked to Assets.
We can use the delegable Filter on Gallery1 to display only items associated to the selected Asset from Gallery2 using something along the lines of:
Filter(
Location,
SiteId = GalleryAssets.Selected.SiteID
)
If you want both Gallery controls to be able to filter in a cross-directional way, you will need to apply the same logic to the Assets Gallery, but also reset both Galleries in order to run the filters again.
Many-to-many relationships
I can see you also have a Many-to-Many relationship between the Assets table and the Providers table, as well as a Many-to-Many relationship from the Location table to the Providers table.
For Many-to-Many relationships, it is important to emphasise that SharePoint is not a relational database. A data source like Dataverse natively supports Many-to-Many relationships with an "intersect" table which is automatically provisioned in the background to manage those relationships, but SharePoint does not.
You will need to create your own "intersect" or "bridge" table per the number of related tables you have. Based on your diagram, you will need four Lists.
List 1 will record the relationships between the Locations table to Hardware, Assets and Providers
List 2 will cover the relationships between the Assets table to Locations, Hardware, Assets, and Providers
....and so on.
As you have probably gathered, it is memory intensive if you try to build many-to-many relationships across multiple tables because SharePoint is simply not designed for this. Even for a simple delegable operation like a Filter, Power Apps will send a query to SharePoint to get all the items that match that query for each table. This will throttle your data source because it is firing a query at multiple tables. You will end up with a 429 error which looks like this:
https://learn.microsoft.com/en-us/azure/logic-apps/handle-throttling-problems-429-errors?tabs=consumption
I have created a performant solution like this for my organisation, and two key lessons I learned were to ensure:
- Only delegable functions are used
- Only display a maximum of two tables/Galleries on the same screen, and then display other table relationships on different screens.
