Nested lookups: several levels deep

[Aardvark]

I have a supply chain I’m looking to keep track of:

Growers
Processors
Wholesalers
Markets

These are all listed in my “Entities” table with a “Type” field distinguishing them.

Now, I’ve figured out how to link single-steps using a relationship table:

Growers <-> Processors
Name | Grower | Processor

And then use a Lookup field in the Entities table to display which growers work with which processors, and vice-versa. I also have a Processors <-> Wholesalers table that does the same thing.

Now, what I need is to use those two tables to create a list of every Grower a Wholesaler might have in their supply chain.

In MySQL it would look like:

SELECT growers 
FROM growers_processors AS gp 
WHERE gp.processor IN (
  SELECT processor 
  FROM processor_wholesaler AS pw  
  WHERE pw.wholesaler = 'Wholesaler Name'
)

Is this possible? Or should I stick with a database?

Once I get that, I’ll need to know which Market might carry product handled by which wholesaler, processor and grower.

[Aron_Korenblit]

Hey Aardvark,

Always a little difficult to try to visualize your table without having access to it but I think the issue you’ll face is that you have all of your entities in one table with a “type” field.

I think if instead of having a table of entities with “types” you create a separate table for each category (e.g. one table for Growers, Processors, Wholesalers, and Markets). From there, create linked records such as these Markets buy from these wholesalers, these wholesalers buy from these processors and these processors buy from these growers. You can then filter in each table for the pair that you’re looking for.

If you provide read access to your table, happy to give it a look!

[Aardvark]

Here’s a mockup:

Airtable

Example - Airtable

Explore the "Example" base on Airtable.

I’ve got a way see the links for each step of the process through linked tables, but what I need is to know what Markets a product by Grower “G1” ends up in (M1 and M3 through two different distributors).

And I need to go the opposite direction: what growers are selling stuff in Market M3?

[Justin_Barrett]

After a quick test, the solution as I see it lies in your topic header: nested lookups. Check out this modified version of your sample table. Instead of describing the links between entities in separate tables, I linked between the tables themselves: growers to processors, processors to wholesalers, and wholesalers to markets. The other fields marked with (LU) are lookup fields, which pull in data from the available linked fields.

Airtable

Supply Chain - Airtable

Explore the "Supply Chain" base on Airtable.

The only downside is that you get duplication of items the deeper you look things up. For example, looking at the markets tied to G1, you’ll see that M1 is listed twice because M1 connects through both D2 and D3. Unfortunately Airtable’s ARRAYUNIQUE() options—both in formulas and in rollup aggregations—do nothing to strip out the duplicates because the two M1 entries are technically coming from different arrays.

Here’s my best guess at what’s happening under the hood after some technical twiddling. First, what we know about the links:

D2 is linked to M1 and M3
D3 is linked to M1

When the lookup in [Processors] collects this info from [Distributors], it doesn’t collect three individual references: M1, M3, M1. Instead, it’s collecting two arrays, because that’s how Airtable stores collections of items, even when those collections only contain a single item. So D2 actually passes along (M1, M3), and D3 passes (M1).

The lookup then contains these arrays in yet another array of its own. So while the lookup field display shows:

M1 M3 M1

…and we naturally assume that the array form of these items is:

(M1, M3, M1)

…which would be properly processed by ARRAYUNIQUE(), it’s actually more like:

((M1), (M3, M1))

I discovered this by trying a rollup on the same data, using ARRAYJOIN(values, "--") as the aggregation function, which led to this:

M1--M3, M1

Because the array item separator was only inserted once, the surrounding pieces are most likely the sub-arrays I mentioned above.

Long story short, there’s no way to remove the duplicates until Airtable supports some method of turning a collection of nested arrays into a single array. On the plus side, you can track everything from grower to market fairly easily.

[Aardvark]

Awesome… kind of getting close this this as I fiddled more. That is unfortunate about the duplicates in the lookups, but I can manage.