I work full-time at a nonprofit where we use an Airtable base as a CRM. We run a few different programs, and many of the participants overlap. There's a wide variety of information coming into the base ranging from event sign-ups to donations. Some of it is entered by automated integrations, other tables are filled by public forms, and we update some parts manually.

While I loved how powerful linked records were, I was initially frustrated that I couldn't do things like match two records by multiple user emails without reconfiguring a base and its primary fields or setting up and integration which would have been trivial in Excel with a VLOOKUP. There are tens of thousands of records in total in our base, so troubleshooting linked records individually would take hours of staff time. Originally I handled this with a group of AWS Lambda functions that use the Airtable API to link records (usually by email) to a main "People" table, but this isn't sustainable for organizations without development capacity.

My goal with this block is to make this kind of record linkage easy for smaller organizations that may not have development capacity. Organizations can link volunteer sign-ups to volunteer records by email, and then volunteer records to donations to have a better sense of overall engagement without needing to put staff time into data entry that would be better spent on programs.

What it does

This custom block streamlines linking records in bulk using VLOOKUP-style matching on fields. Users can open the wizard-style block, select the table they'd like to update, the linked records they want to fill in, and the fields that should match to link records in bulk. They can also configure the options for the merge like whether all keys must merge (i.e. if you only want to link to a person if their name and email match), whether merge should be case-sensitive, and whether existing links should be replaced.

You can do all of this in a few clicks instead of setting up custom integrations or managing a small army of Zapier functions that may need to be changed at any time.

How I built it

I took a lot of inspiration from the Dedupe block as well as the guidelines around wizard blocks. The react-window library was also helpful in displaying records that would be linked without reducing performance significantly with larger amounts of records.

Challenges I ran into

Putting together the user interface in a way that was transparent about changes to the base was initially challenging, but the guidelines around "wizard" style blocks were helpful.

There were also some cases where components I expected to be available weren't, which is why I ended up using a custom component to display records that would be updated instead of RecordCard after trying to hack the display of that a bit.

Accomplishments that I'm proud of

The block runs pretty quickly and efficiently even when matching upwards of 500 records. In my opinion it also does a decent job of communicating what changes it will make and how it's making them.

What I learned

I got a good sense for how the Airtable Blocks SDK works, and I'm looking forward to creating more custom blocks with it.

What's next for Link Records by Fields Block

I'll likely use this for work and with other organizations I'm a part of that use Airtable, and I'm hoping to post this more broadly for others to take advantage of.

Built With

Share this project: