One feature I really miss in Airtable is automatically linking records. I know that I can manually link records, but I really need (OK, want) automation to do this. I’m wondering if experienced Airtable users have any advice to offer.
Simplified use case: I’ve built a Quantified Self app in Airtable that uses IFTTT and Zapier (when necessary) to move data from the collecting app (Todoist, Strava, FitBit, etc) to app-specific tables. The data is ALWAYS timestamped.
I also have a table of Monthly KPIs, where I set goals and measure progress. The Monthly KPIs table has twelve records: January, February, March, etc. To aggregate the data I am manually linking records from each app-specific table to the appropriate “month” record in the Monthly KPI table. I do this in order to bring the goals and the KPI data into the same table, where it can be analyzed and visualized using Blocks (which, I believe, can only take one table as a data source).
On a per table basis, I can use Zapier to achieve automatic linking. The two-step Zap is triggered by a new record, extracts the month from the new record’s timestamp and writes it back to a linked record field. But my paid Zapier account is already near the max zap limit and I can’t do this for every data table I have.
Any suggestions for a workaround? API hack? Blockscript? Zapier alternative? Thanks, in advance!
One way to accomplish this in a simple though non-automated manner would be to create a formula field in the app-specific table with the formula
This results in the field containing the full name of the month for each timestamp — which is presumably the same value used for the primary field for each of the 12 records in the [Monthly KPIs] table. To link the records in the app-specific table to the [Monthly KPIs] table, select the header for the column of extracted month names, press Ctrl-C to copy the entire column, select the header for the linked-record field column, and press Ctrl-V to paste the entire list of month names — and, as a result, create links. (Unfortunately, there are no corresponding API functions for ‘copy field from all records’ and ‘paste field to all records,’ so this bulk process can’t be automated.) Depending on how frequently the monthly table is updated, this could be either a no-fuss method of achieving the desired goal with relatively little fuss or a royal pain in the rear. I suspect for many applications, it would be a fully workable approach.
Of course, if preferred, such links could be created a record at a time, either manually or through a third-party integration service. Note by using an Airtable formula to extract the month name, updating a record requires only a two-step process — which means, for instance, it could be performed by a two-step Zapier Zap under a free account.