Creating a view that combines data from 4 tables

[Fouad_El-Qassem]

Hi there,

I’m building an app separately and I’m using Airtable as my database. In my base, I have “categories”, “brands”, “products” and “inventory” tables, where the 4 tables have proper linked fields configured as one-to-many (inventory → products → brands → categories).

I need to have an API that shows records from all 4 tables based on the linked fields and using filters. After reading different help topics, it seems that the way to do this is by creating view, but I understood that creating a view with linked fields is limited to maximum 2 tables (linked and linked to tables).

How can I create a view (or an API) that retrieves data from all 4 tables?

Your help is highly appreciated.

Thanks
Fouad

[TheTimeSavingCo · Accepted solution]

Hi Fouad, hmm, what issues were you facing when attempting chaining lookups (example here)? Once I know more about the issues you faced I could try to suggest some workarounds

[Fouad_El-Qassem]

Hi Adam, I haven’t tried that, but it seems to be the way to achieve my objective. How can I do that? How can I add a field from a table that doesn’t have a direct linked field?

In your example, how did you add the field “Notes (from Table 3)” from Table 2 and the field “Notes (from Table 4) (from Table 3)” (from Table 2)?

Also, will this view be available via an API?

Thanks

[TheTimeSavingCo]

Hey Fouad, it’s a lookup of a lookup of a lookup heh. You should be able to duplicate my base to see the exact setup

Yeap, they’ll all be accessible in an API call to Table 1.

[Fouad_El-Qassem]

Brilliant :grinning: Thanks a lot Adam.

I’m a step closer, but I still haven’t achieved what I need (yet). I actually want to list down the categories for brands, for products that have inventory (based on a field in the inventory table). So do you know how I can do a lookup based on a condition (tried doing that using a filter but there is no provision for aggregation). In database terms, that would be “select * from categories… having count(inventory_stock) >=0”

[Fouad_El-Qassem]

Never mind. I figured it out. I used a “Count” field on the lookup of lookup.

Thanks for your help Adam