Filter gallery to show all items of a category and all sub categories

Question

I am trying to create an inventory app using PowerApps, currently each item falls under a main category, and at least one subcategories. A small example of this is something like this:

Concessions
|    Food
|        Chips
|            Brand 1
|            Brand 2
|            Brand 3
|        Candy
|           Chocolate
|               Brand 1
|               Brand 2
|           Hard
|           Gum
|        Grill
|    Drinks
Travel Goods
|    TravelGoodsSubCat

This is held in a google sheet (along with the inventory data in another sheet inside the same book) that looks like this:

CatID    CatName            ParentCat
1        Concessions        0
2        Travel Goods       0
3        Food               1
4        Chips              3
5        Candy              3
6        TravelGoodsSubCat  2
.... And So on....

So what I'm envisioning is two galleries, one that is horizontal across the top of the screen that shows only the categories that are a child of category 0. The second is a vertical gallery that shows every item under the current category. So if the user selects Concessions in the horizontal gallery, the vertical gallery should populate with all items assigned to categories 1, 3, 4, 5, and so on.

How do I acheive what is, in my mind, a recursive search looking for all these possible sub categories?

Answer 1

This is achievable, but in order to have performance not slow down significantly you need to first cache the category table in a collection. This is particularly the case if you use Google Sheets as a data source.

Then you can create a collection with two columns: CatID and UltimateParentCat, something like this:

CatID  UltimateParentCat
1      1
2      2
3      1
4      1
5      1
6      2

This is where you will use recursion, but coding each step, so you will need to stop at the maximum depth that you expect.

Therefore you need to set the app's OnStart property or some button's OnSelect property to something like this in order to populate the collections just once.

ClearCollect(CachedCategories, GoogleSheetsTable);

ClearCollect(Step_1,
    AddColumns(
        ShowColumns(
            Filter(CachedCategories, ParentCat=0),
            "CatID"
            ),
        "UltimateParentCat", CatID
        )
    );

Clear(Step_2);

ForAll(Step_1,
    Collect(Step_2,
        AddColumns(
            ShowColumns(
                Filter(CachedCategories, ParentCat=Step_1[@CatID]),
                "CatID"
                ),
        "UltimateParentCat", Step_1[@UltimateParentCat])
        ));

Clear(Step_3);

ForAll(Step_2,
    Collect(Step_3,
        AddColumns(
            ShowColumns(
                Filter(CachedCategories, ParentCat=Step_2[@CatID]),
                "CatID"
                ),
        "UltimateParentCat", Step_2[@UltimateParentCat])
        ));

ClearCollect(CatsWithUltimate, Step_1, Step_2, Step_3)

Once you have done that, then the Items property for horizontal gallery should be

Filter(CachedCategories, ParentCat=0)

Then the Items property for the vertical gallery should be

Filter(Products,
    ProductCategory in Filter(CatsWithUltimate,
        UltimateParentCat=GalleryHorizontal.Selected.CatID).CatID)

Please let me know of any typos in the above so I can fix.

As a side note, I would suggest migrating to another data source like Azure SQL Database and just producing CatsWithUltimate as a view.