Combining a List of Lists of Refs into a List of Refs

Background

I have a base table, let’s call it Fruit and a table with another table with Categories. Since it’s a many-to-many relationship between the tables, there’s a joiner table in the middle (let’s call it Fruit_Category Relationships) and I can easily pull a list of Fruit for any Category [Category Fruit]. Most of this is irrelevant.

My Categories table is hierarchical, accomplished by having a Ref column (Parent) referencing itself, i.e. the “Citrus” category could have parent “Tree Fruit”. There’s a reverse reference column (Children) to capture all the categories that have a given category as their parent.

My Problem

I would like to create a list of all of the Fruit in the current Category, AND in the Categories that list the current Category as a parent, AND all the Categories that list those as a parent, etc.

My Solution

I added a new virtual column to Categories (Fruit Including Children, type List, base type Ref) to combine the list of Fruit from the existing Category with the Fruit of the Child Categories to make one big list of Fruit refs:

[Category Fruit]+[Children][Fruit Including Children]

This is where I’m running into the issue. I got a big gnarly type mismatch error so I narrowed it down to test something out. Can I just get one big list of the fruit associated to each child this way?

[Children][Category Fruit]

This produces a List of Lists of Fruit refs. I would like to merge all of those lists into one single list of Fruit refs. How can I accomplish this?

My question

How can I merge multiple lists of the same base type? I know how to do it with two lists LIST() + LIST() but how can I do it with a list of lists?

0 12 1,263
12 REPLIES 12

Hi @thethunderbird,

Welcome to the AppSheet community.

I believe in general your post is detailed with good information. In general, I think it is known what you wish to accomplish. However if you can share exact names of columns and structure of tables (only for relevant ref , children columns) etc. , I believe the community could share inputs in a better manner.
Screenshots of column names in table structure could help.

Is there a way for me to publish a sample app? That would probably be the easiest to poke around in.

Here is the Fruit table:

The Categories table:

The Relationships:

Here is an example Category, Citrus Fruits. As you can see it has Parent “Tree Fruits” and some associated Fruits.

I would like the associated Fruits from Citrus Fruits (and all fruits in categories with Citrus Fruits as a parent, and so on) to roll up to Tree Fruits:

Thank you. This definitely helps more. However, I think you have given two identical screenshots of the Categories table but not that of the third bridge table ?

Fixed!

Thank you. I think now I understand what you are trying to accomplish. I believe you can have a combined list under the “Parent” category as you wish.

However the approach is long drawn or a bit convoluted- includes addition of at least 3 new VCs. This is due to the fact that there is a self reference on the “Category” table and then there is one more joiner table that also has terms such as Ctegory and Fruits in it. I can describe it here , which could be a long description and at times confusing.

You may DM me so that I can share my ID at which you may wish to share the app where I can make necessary changes or I can describe the approach here.

Please let me know.

If you could describe the proposed solution I would greatly appreciate it!

Okay , sure. I will make an attempt.

Please create new VCs - all in the table “Categories”, all list type

  1. VC Name say [Related Parents]
    Expression REF_ROWS(“Categories”, “Parent”)

  2. VC named say [Parent IDs]
    Expression [Related Parents][Cat ID]

  3. VC named say [Parent Fruits]

SELECT(Fruit_Category_Relationships[Rel ID], IN([Category], [_THISROW].[Parent IDs]))

  1. a VC named say [All Fruits]

[Parent Fruits] + [Related Fruit_Category_relationships]

[Related Fruit_Category_relationships] is the system created reverse reference column in the Categories table ( column 5). In teh screenshot you have shared , the name is partially visible. Please make it correct as required.

This 4 th VC will give the row references for all the category IDs -main category as well as subcategories belonging to it.

Example below. In teh test app, the column names were a bit different from yours

Unless I’m missing something, I’ve already accomplished what you’ve described and it’s fairly straightforward. But this solution only works one hierarchical level down (i.e. on a row’s direct parent, not it’s parent’s parent and so on). The key here is that I need it to work for an unspecified number of hierarchical levels such that A could be a parent of B, which is a parent of C, and C is a parent of D and for row A I could list all the fruits for A+B+C+D, for B fruits for BCD, and so on.

I was actually able to solve for multiple hierarchical levels by using Google Sheets. My solution was to create a column in Google Sheets that creates a list of the categories above it by inheriting it’s parent’s list and adding it’s parent’s ID to its own list. Then I used a virtual column [Ancestors] in AppSheet to select all of the categories that have a given category above them. From [Ancestors] I can do a SELECT with an IN condition that matches [Category ID] to [_thisrow].[Ancestors] on the relationships table.

Okay. Got it. Thank you for the update and great to know that you solved it.

I may request that it will be great if you can be as detailed as the multilevel hierarchy you described in the latest post. In general, I believe the community could help one better with those details. Of course, it could be my oversight also in understanding your initial post where you mentioned two levels with examples but were actually looking for multilevel.

All in all, nice that you solved this tricky requirement. All the best with your apps creations.

I would be happy to! Is there a way I can submit or make a sample app?

Please take a look at the following article, if by sample app you mean sample app for public viewing.

This documentation is good but not complete. The following thread has some more detail:

Top Labels in this Space