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?
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”