Pull data from distantly related tables

[Brandon_Harris]

I’m new to Airtable, but have extensive experience in SQL databases. I could do something like the following in Access, but I want to make this database cloud native. Any assistance would be appreciated.

I’m working with a consulting firm that has ~50 consultants who relate to one or more legal affiliates of the firm through independent contractor agreements (ICA). Each ICA formally connects one of our legal affiliates with an “external entity” (an LLC, a sole proprietorship, etc.) to which a consultant belongs. Each consultant may have one or more external entities that they are themselves connected to.

I have built my base like this:

I want to be able to create a Consultant view (ideally a card/grid-view) that would show me the consultant’s information and a list of all the agreements that are related to him/her through the external entity table. However, I can only figure out how to display data that is from the directly related table “Consultant/Entity Relationships”.

[ScottWorld · Accepted solution]

Welcome to the community, @Brandon_Harris! :slightly_smiling_face:

Unfortunately, this is one of the (many) limitations of Airtable. Airtable is a very rudimentary database system, significantly more rudimentary than anything SQL-based, and significantly more rudimentary than Microsoft Access.

Airtable only allows you to pull data from a directly-related table, so if you want to pull from a distantly-related table, you actually have to pull the data into each & every table along the path. Is this annoying? Yes. :crazy_face:

Also, you cannot create a card/grid-view in Airtable, although you CAN expand a record in the grid view, which gives you a card-like experience, which lets you drill down further into linked tables. But in general, you have no ability to create custom views in Airtable, outside of the very mediocre Page Designer App which does not allow for data entry — it only allows for viewing data with no user interaction allowed.

For something significantly more advanced than Airtable that gives you what you’re looking for — but still easy enough to point-and-click your way through most of it — I would recommend checking out Apple’s FileMaker.

p.s. I am both an expert Airtable consultant and a Certified FileMaker Developer. If you need any help creating your system in either of these platforms, please feel free to contact me through my website at ScottWorld.com.