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.
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.
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.
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.
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!
7 thoughts on “Using Airtable to Automatically Check Predicted Relationships”
What a great tool! Thank you Marie!
This is fantastic! I incorporated it into my AirTable base. I think you left out niece and nephew from the shared cM table, though.
Thanks for the catch! I’ve added those two relationships in. I should also note the same idea can be applied in an Excel spreadsheet using the vlookup formula.
It may have been easier to pull the data directly from the chart on pages 52 and 53 of the PDF Blaine Bettinger created to go along with the Shared cM Project.
Pages 55 and 56 chart the comparison between the versions 3 and 4.
I am going to use this in my DNA Research Log! Thanks for sharing!
Thanks for the link to Blaine’s white paper, Kristal! The histograms mentioned above are included in that PDF and well worth looking at if anyone has values reaching the edges of expected ranges. I should mention that my templates were populated with cM values from v4 of the Shared cM, so if anyone has been using v3, you’ll notice some variations (e.g. great-aunt/uncle/niece/nephew average decreased from 914cM in v3 to 850cM in v4).
Love, love, love this!!💖
I don’t use the shared cm tool often but now that I’m focusing on 23andMe matches, the percentage converter to cMs has become invaluable.
I also haven’t used airtable in a while, so a great reason to revisit my research there.
I agree – the Shared cM Tool is amazing! And it’s always fun to revisit research using new tools 😊