Long story short, I’m demoing Airtable to see if it is right for what i’m trying to do. The situation is: My company currently uses a custom Filemaker Pro database / app for scheduling and project management that is TERRIBLE. We’ve worked with the company to try to make it better for YEARS and i’m just fed up with it. It’s slow, bloated and is costing us time and money.
I’m in the process of carefully exporting the data from the database and have got almost all of it in what i think are usable excel sheets.
Basically what i’m trying to work on now is how to link the data in 2 (or more)tables.
I’ve got a table that is a list of projects, with client info, date, time, job number etc…
I’ve got another table that is a list of our deliverables for the client, Video title, length, format, etc. Several rows that also contain the same job number in the first list.
The question is how do i best go about easily accessing the appropriate data in either table from the other? I’ve been messing around with linked fields, but it doesn’t seem to work the way I’m envisioning it. I can’t go through and manually link thousands of records for legacy data. From the description it sounds like there is a way that linked records will auto-populate based on the common data in the fields, but i can’t seem to figure it out.
“Deliverables” Table, Project ID is the matching field.
I am the world’s only expert at both FileMaker and Airtable. You can learn more about me at my website: https://www.scottworld.com
Unlike FileMaker, there is no automatic linking in Airtable. All links in Airtable are done manually — either manually by the user or through an automation that you previously manually setup.
Additionally, unlike FileMaker, links always link to the primary field of the other table. You can’t directly link to another field (although you can automate the process of searching for a field’s value and then linking to the primary field after finding a matching record).
To state this more accurately: In Airtable, you are always linking to an ENTIRE RECORD in the other table. This is completely different from FileMaker where you are always linking to a specific field value.
However, there is one quick way to automatically link records in one fell swoop if you’re currently going through a migration process:
As long as your other table already has the matching values that you want to link to in its primary field, you can take any field in your current table and convert it into a linked record field which links to the other table. This is a one-time-only process that will only work when you convert the field to a linked record field.
Under normal circumstances, Airtable will even create new records in the other table on your behalf when you go through this one-time-only conversion process (when new records are needed). However, since your primary fields are formula fields, Airtable will never create new records on your behalf. But since you would only be going through this conversion process once for records that already had matches in FileMaker, this probably won’t be a huge problem for you.
Anything more complex than this would take way too long for me to type up here, but If your company has a budget and you’d like to hire the world’s only Airtable & FileMaker expert consultant to help you migrate, please feel free to contact me through my website: Airtable & FileMaker consulting — ScottWorld
Additionally, you might benefit from my free Airtable training course, which will get you completely up-to-speed with everything you need to know about Airtable: