API Retrieving records from multiple tables

[Bill_McIntyre]

Has anyone retrieved data from multiple tables via the api? I can retrieve one set from one table no problem and I know how to to do this in SQL for muliple inner or outer joins but I have a little bit of a block doing this in node.js using the api.

Thank you

[Fernando_Amorim · Accepted solution]

Hi @Bill_McIntyre, in order to use the similar to JOINS, you have to link in the main table you are going to select to the other table, using the ‘Link to another record’ column, once you added and linked the record to the main table, you can filter it using the PRIMARY COLUMN column in the other table.

Exp:
‘Main table’, has a link to record column named as ‘Product’, the table ‘Product’, has the primary column as ‘Composed Product Name’, to make it unique, or use and AI column. So, that said, you can select from the table passing the filterByFormula values, something like this:

//in this case i'm using async/await, which doesn't require .then or done
try{
     let records = await base('Main table').select({filterByFormula: "{Product} = \"Myprimarykeyhere\""}).all();
} catch (err) { console.error(err); }

Then you can catch only the row you want, referencing it by “foreign key”. There are other formulas to use if you need and AND, OR, etc. See: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference

[Fernando_Amorim]

And for getting fields from the other table, you have to use a Lookup column, to reference the item from the other table and bring it to the ‘Main table’.

[Bill_McIntyre]

Thank you Fernando. I was focusing too much on the node.js code. This is elegant and I will just make a hidden table with all the fields linked from other tables. Spot On!