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.
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:
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.