Subcategories - can't figure it out!

[tobyinla]

Hi all I am trying to set up a Google Sheet with AppSheet and im having a really difficult time working out how to make subcategories work. There doesn’t seem to be any app examples that have subcategories - anyone know of any?

I have categories, then subcategories, then subsubcategories, then subsubsubcategories. So for example the category Planet then has the subcategory Countries then subsubcategory Cities then subsubsubcategory Streets.

I can’t figure out how to do this :-((

toby

[LeventK]

Unfortunately your explanation of the issue is not clear at all. Explicitly where do you have those categories, what are they, where do you want to use or display them? What are the issues you are experiencing? Where you are experiencing those issues: Form View, Detail View etc.? Please elaborate your problem.

[tobyinla]

Sorry. I meant that I have a table called ITEMS. And in that is a big list of objects - for an inventory. I have categories for each of these items, and within those I have subcategories etc. I wasn’t sure how to do that. So at the moment inside the table ITEMS, I have a column CATEGORY, then a column CATEGORYSUB, then a column CATEGORYSUBSUB, and a column CATEGORYSUBSUBSUB. And each of those are set to be REFS, sourcing from separate tables - I have a table called CATEGORIES, a table CATEGORYSUBS, a table CATEGORYSUBSUBS and a table CATEGORYSUBSUBSUBS. That’s how I’ve built it, as I wasn’t sure how else to do it. And then inside those tables, they are ref’d to each other. Does that make sense?

For more info: Inside the “CATEGORIES” table, I have columns _RowNumber, CategoryID, CATEGORY, CATEGORY IMAGE, IMPORTANT and Related ITEMSs that last one was autogenerated by AppSheet).

I’m happy to change the way I’ve built it - I’m asking to find out how best to store and use “subcategories” and “subsubcategories” etc for items in the database.

The app I’m making then should have “CATEGORIES” as an option at the bottom, then you see a list of all the main categories. You click one, and it shows the subcategories. You click again and you see the subsubcategories. Then again to see the subsubsub categories. Ideally at each point you’d see all the products that are have no further subcategories. The way a normal inventory app does it, I guess.

[LeventK]

@tobyinla
Your approach to reference the tables with each other is correct as they actually related to each other. But however, I couldn’t understand where the problem is. In the table view, when a certain row is clicked, it goes to the Detail View of that record. Instead of the Detail view, do you want it to show the table for the next sub-category?

[tobyinla]

I’m talking about the behavior the final app - not the behavior of the AppSheet interface. When I make a view, I want it to show all categories, then a user clicks that and it shows all the subcategories - and also show all items that are in that main category but don’t have a subcategory. Etc etc Does that make sense?

[LeventK]

@tobyinla
I was talking about the same if you pay attention to what I have said. What you are expecting is totally doable and is a very simple thing to do in many aspects. It totally relies on how you want to do it actually. There are a couple of different ways to accomplish what you want.

[tobyinla]

OK great. Would love to know how to do it. Did I offend you somehow? You seem quite angry.

[LeventK]

tobyinla:

Did I offend you somehow? You seem quite angry.

No, no not at all. Sorry if it seemed that way. You can drill this in a couple of ways as I have said. The easiest and more direct way is:
Instead of the CATEGORIES table, put the PRODUCTS table. And in the table structure use GROUPED BY option and group that table as per:

• CATEGORY
• CATEGORYSUBS
• CATEGORYSUBSUBS
• CATEGORYSUBSUBSUBS

You can also apply a Format Rule to these so that it will look nice in the UI. And in the table view, when the user clicks the PRODUCT icon, the table will show in bold CATEGORY >, when clicked it will drill down and show all the names from the CATEGORYSUBS, when one of them clicked, it will show names from the CATEGORYSUBSUBS and so on. At the end the product names from the selected CATEGORYSUBSUBSUBS will be displayed.

I’m proposing this method, assuming that your PRODUCTS table contain all the ref columns regarding the CATEGORY, CATEGORYSUBS, CATEGORYSUBSUBS and CATEGORYSUBSUBSUBS.

[tobyinla]

Thanks I’ll give this a go!! Have a great day! :-)))

[LeventK]

You’re welcome. Please report us back should you require further help.

[Bahbus]

I have built out an app with only a single level of subcategory. But I don’t have an example that I can share.

3 Tables: Items, Categories, Subcategories

Items columns: Item Name (key), Item Category (ref), Item Subcategories (EnumList of Ref)
Categories columns: Category Name (key)
Subcategories columns: Subcategory Name (key), Category Name (ref and belongs to)

Items can must contain 1 Category
Items can have multiple Subcategories (but they don’t have to be related to the Category)
Each Category can have multiple Subcategories

Starting View: Categories (card) using LINKTOFILTEREDVIEW() to go to Subcategories (card) showing just the subcategories related to the category selected. This view then LINKTOFILTEREDVIEW() to Items (card) where the selected Subcategory is in Item Subcategories. Which then goes to the Item Detail view.

This is my most bare-bones basic explanation of how I have mine setup.

[tobyinla]

Bahbus I tried to follow your instructions - I made a quick video (2 mins) showing what I’ve achieved lol.

1. How do you change the ‘Item’ table’s column “Item Subcategories” to be"EnumList of Ref"? (see 1m 08 in video where I get stuck)

2. Could you please explain what you meant by this:
   Starting View: Categories (card) using LINKTOFILTEREDVIEW() to go to Subcategories (card) showing just the subcategories related to the category selected. This view then LINKTOFILTEREDVIEW() to Items (card) where the selected Subcategory is in Item Subcategories. Which then goes to the Item Detail view.

I created a new view, called it Categories (it actually wouldn’t let me, cause i screwed one up previously and deleted it, and so it forced me to use Categories 2 for some reason), and change it to CARD. Then I looked up all the help I could find on “LINKTOFILTEREDVIEW()” to no avail. :-)))

[Bahbus]

1. Column Type: EnumList - > Base Type: Ref. Do a Save & Verify. Then open the columns settings again so you can ensure the ref is being pointed to the right table.

2. Use have to create actions to use LINKTOFILTEREDVIEW() and put them on the cards. Something like:
   LINKTOFILTEREDVIEW("Subcategories", [_THISROW].[Item Category]=[Item Category])
   This one would be to go from Categories to Subcategories.

[tobyinla]

Thanks again Bahbus for your help. I have learned all about LINKTOFILTEREDVIEW() etc etc now and that works, although have discovered it doesn’t work with Dashboard views. Argh. So I can’t, for example, so a list of subcategories at the top of a page and then a list of the items in the overall category below it (ie. if a user clicks on the ELECTRONICS category, I’d like it to show everything within it - but also give links at the top to subcategories, like “iphone cables” or whatever).

[Bahbus]

True, it doesn’t work with dashboards. I actually built it this way, because while I also wanted to use dashboard to achieve something similar to what you are also describing, there is no way to pull it off with a UI that also looks good. It’s why I used Card views since they look good on a tablet (or webpage).

[tobyinla]

Thanks Bahbus - yeah it’s all very odd the way its made. So many limitations and dead ends. I’m happy to code - even though the “no coding” thing is everywhere for this framework lol - but it seems that there’s no way to avoid certain issues like this one.

I’m at a loss really - I don’t know how to build an app with “levels” of categories. Seems like AppSheet is only built to have categories, in essence, and never to have subcategories - unless you’re willing to use Cards and just display the list of subcategory items, and go no further. 😕

[Bahbus]

Well, I mean, you can extrapolate this method to go much deeper. The reason dashboards don’t really work for this it because each set of categories are stored in separate tables with separate views. And we can’t dynamically add/remove views from the Dashboard.

In your example, Planents -> Countries -> Cities -> Streets, can still follow my example. You just need more tables. Streets has to ref the City. The City has to ref the Country. The Country has to ref the Planet. And explicitly, in this example, each table’s ref would also belong to. If the city is destroyed (deleted), the streets in it would be too.

[sydneyj0ann]

I know this is old. What action did you do to go from the subcategories to the item details?

[noeonoeon]

It's 2024 and I need to replicate something similar with categorizing and subcategorizing items for on-the-ground management. Can anyone update this thread or redirect me? Or this workaround from 2020 is still the only solution around?