How do people use airtable when it doesn't offer left join?

[data_guy]

i am a beginning user of airtable, and i feel i may be missing something. what do the rest of you do for joining/linking/merging tables with more than say a few 10s of records?

i have a dimension table (properties) and a fact table (transactions). i need to join them so that each transaction has the right properties added. i tried multiple times to figure out how to do that with linked records, and lookup, and rollup, till it dawned on me that those only work when you, for each record, MANUALLY CHOOSE THE LINKED RECORD?! in my use case there are only a few thousand transactions going through per month, (and a few tens of properties) but that scale is obviously beyond a manual method.

i have been looking around for ‘merge’, ‘join’, ‘auto-lookup’, ‘vlookup’, etc but nothing presented itself. i am quite surprised. i thought for sure that somewhere there’d be an automation, or a script, or an extension, or something that would provide this capability, but it really doesn’t seem to be? is that so? am i just not finding it?
if true, this is a total dealbreaker for us, and i just cannot see a way around it. how do the rest of you cope with even modestly sized tables? is there a trick? please tell me there is a trick?

[ScottWorld]

Welcome to the community, @data_guy!

In Airtable, all joins (i.e. linked records) must be explicitly chosen by the user, although you may be able to automate the process of linking records with (1) Airtable’s Automations, (2) JavaScripting, and/or (3) external automation tools like Zapier.com or Make.com.

[Brent_H]

The way I have been doing it is with a key generator formula column what creates the link data then an automation to copy this into the link column. Just make sure the key column in the properties table is a formula so that any incorrect link added does not create a new record in the properties table.

[data_guy]

ScottWorld:

automate the process of linking records with (1) Airtable’s Automations

hi scott! thanks for a prompt reply! i am curious about the automation route. would you trigger that on every new record entry into the transactions table?

[ScottWorld]

You won’t be able to trigger your automation in Airtable based on new records, unless those new records are created via a form or the API.

In the majority of cases, you would trigger an automation based on a checkbox being checked or a certain option in a single-select field being chosen.

[data_guy]

hi brent!
thanks for helping. i am trying to follow, but i remain confused.

i do have a column in each table, that i want the tables to join on. i call those my key columns, but although in neither case are they assigned as the primary column for the table, so airtable may not see them as join keys). is there a way to specify for each table which column is a key?
and does that relieve the need to manually select the matching row in the property table, for every row in the transaction table?

all the best!

–
p.s. what exactly is a 'key generator formula column?

[ScottWorld]

Airtable is very different than other database systems. There is no such thing as key columns in Airtable. You always link to the primary field of the other table, so you’re always connecting to an entire record, not a particular field in that record.

Airtable is a very simple database tool, so for best success with Airtable, you’ll need to unlearn any previous database knowledge that you’ve come here with. :stuck_out_tongue_winking_eye:

[Brent_H]

If you think of the primary field of the properties table as the key column then on the transactions table a formula column that generates the primary field text you want to link to. It could be as simple as a basic concat formula.

The automation can just use updated field as the trigger if the transactions are manually input or new record if entered some other way then it just copies the formula text into the link column.

If you want to link a transaction to multiple records in the properties table then that is different and I have not done that via automation yet.