Using Airtable to Automatically Check Predicted Relationships

Share This

With some DNA cases, I use a table to organize genetic matches relating to a specific research question. I aim for a snapshot of my matches: how much DNA they share with me (or my subject), and their predicted relationship to me based on preliminary documentary research. By itself, this information isn’t all that useful so I also include columns listing the expected amount of shared cM based on the predicted relationship. By comparing the actual shared cM to the values from the Shared cM Project, I can quickly see if I’m on the right track with my predicted relationships. This table could also be extended into a larger DNA research log.

In the example above, Louisa shares 450cM with me and my initial research indicated she could be a 2nd cousin once removed (2C1R). A quick look at the Shared cM Project tells me I should dig deeper since a 2C1R typically shares between 14 – 353cM. It’s a flag that my initial research may not be accurate or that Louisa might be related to me on an additional line. Louisa may also be an outlier so I’d also want to check the histogram for a 2C1R.

In doing this, I find myself spending a lot of time going back and forth between my list of matches and the Shared cM project. I copy and paste values into my table and though I’m happy with the end result and gain a lot of insight from it, the process is tedious and susceptible to error.

Airtable

This gave me an excuse to experiment with Airtable, an online tool that Nicole Dyer at Family Locket has written about extensively. She generously shared her Research Log templates that look both slick and functional. I decided to see if Airtable could automatically generate expected cM ranges for predicted relationships, and hooray! It does!

To do this, I built a separate table (“Shared cM Data”) that I populated with data from the Shared cM tool. With that out of the way, I created a new table for my matches and inserted a function to grab the correct data based on the predicted relationship. I also added a column to highlight matches who fell outside of predicted ranges.

What Now?

I’ve only just started experimenting with this tool and need to play a bit more to see if it’s something I’ll use regularly. Some concerns that jump to mind:

Exporting to MS Word

I use Word for all of my research reports so I want to easily transfer data from Airtable. For a smaller table such as this, I can easily export the data as a .csv file and pull it into Excel. From there I might copy the data into a table in a Word research report. It’s a two-step process, but works if I need the data in a report and lets me format it the way I want.

This might be trickier with a larger database with extensive amounts of information, such as a Research Log, which is something I also like to include with client reports. It’s still do-able with the Excel in-between step, but time will be spent on formatting the content to fit nicely into a report. Airtable does offer customized printing and options to save to a PDF file, but this comes at a cost: $240/year or $24/month.

Privacy

Airtable data is visible to you only (unless you share it), but anytime you create content online, that content is at risk. This is something to consider seriously if you’re adding information about DNA matches.

How long is free free?

I can accomplish almost everything I need using the Free version of Airtable, but a free version isn’t guaranteed forever. Am I prepared to pay if I become dependent on it? I also want to support good products, so a subscription is something to consider if I do make extensive use of Airtable.

Try It!

You can experiment with this yourself – all you need is a free account at Airtable and then you can make a copy of one or both of the templates below:

There is much room for improvement – I’m excited to see what else it can do!