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?
