Hi, sorry for the long post. I’m new to Airtable, but think I’m getting the hang of Airtable’s assumptions: hide the database keys and use the first column of the table as a psuedo-primary key for the UI.
We are contemplating using airtable as a readonly front-end to our existing database, which is not very UX friendly and is increasingly perceived as a barrier.
I’m using a script that pulls my data into an airtable base with two tables. I can setup a Link field that creates the links as the records are pulled in.
Now, lets say I’m pulling in 2 tables from an sql database, Contacts and Homework. If I’m creating records in airtable by hand, its ok if I have two different contacts with the same name, because airtable is using its internal UUID, but if I pull contacts with the same name via script, the linking breaks.
If I import the primary and foreign keys into Airtable then I can use those to link the tables, but it seems like I lose some Airtabley goodness. For example, if I make the first column of the Contacts table the primary ID and in the Homework table make foreign ID column that is a lookup to the Contacts table, it works. It works but now I’m clicking on IDs to see related data.
Now, If I have Contact Name as first column in Contacts table and have Homework lookup to that, in cases where there are duplicate contact names and I run my import, it incorrectly matches all Homework to the first of the duplicate contacts.
I imagine maybe I could create the first table, keep track of the UUIDs Airtable has created and use those to link the second table?