I have 2 lists , the first list is Products and the second list is an Accessories. One part can take multiple accessories.
I have a gallery where the users can choose the Product and all information about the product renders in the app. What I would like to do now, is have a nested gallery that will show all accessories related to the selected Product.
So far, I have a lookup field in SharePoint that links the lists using a mult-select lookup field, but am not sure how to achieve the nested filter gallery that will allow me to present the associated accessories.
Can someone please help?
A couple of different ways to go about it...
Your primary gallery can just be the Products table.
Then your nested gallery can have a data source of Filter(Accessories, accessoryProductIDColumn=ThisItem.ID)
Your column names may vary, but that's the way to go (or at least one of them).
An alternative to placing the filter for the 'child' records in the items property of the nested gallery, you can use something like the following to shape your data first:
AddColumns( RenameColumns( Product, "ID", "ProductID" ), "ProductAccessories", Filter( RenameColumns( Accessories, "ID", "AccessoryID" ), ProductID= AccessoryProductID column ) )
The second RenameColumns shouldn't really be necessary, but for some reason in my testing the formula would not return results without it. Each row of Products gets an additional field "ProductAccessories" which contains a table with all of the relevant accessory records. Set the maing gallery's items property to the above and the nested gallery's items property to ThisItem.ProductAccessories.
Comparing the two methods (based on SharePoint lists):
With the shape-data approach, initial population of the gallery may take marginally longer but once populated, scrolling is smoother as the filter-in-nested-gallery approach loads the child records only on demand (once the header is visible) which can lead to controls appearing to take a few seconds to populate with scrolling freezing during that time.
The shape-data approach is affected by delegation limits. Galleries pull back records 100 at a time. Say each parent record has 10 child records so the full data set is 1000 records. If your delegation limit is set to 500, you'll only get half the expected records returned.
In my testing, both methods gave unexpected (wrong) results are the first 100 parent records. From record 101 onwards in the gallery, only 2 or 3 (of the 10) child records were shown.
I think the safest approach is to use the filter-on-nested gallery and use a FirstN to bring in only 100 parent records. Force your users to enter some sort of search criteria to narrow down the list rather the allowing them to scroll more than 100 records.
If you are using SQL, peform a join on your header/child tables and present the results in a single view which you can then shape using the GroupBy command.
Delegation!! The evil of PowerApps.
Actually not so much when you get your head wrapped around it, but you really need to know the pitfalls.
My only advice with delegation issues is to consider it wisely. You may spend a lot of dev time trying to work around a delegation warning on a list that will never be an issue. Always have to consider the final and future results.
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Congratulations, the new Super User Season 2 for 2021 has started!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.