If you are using Airtable for tracking DNA matches and logging documentary research, I’m excited to share an update to the RLP with DNA base template available in Airtable Universe. Each year I make updates based on my own experience and suggestions from others. I usually do this before our Research Like a Pro with DNA study group begins. The group is in full swing right now, and we are using the new base I shared for 2024. The links to the updated bases in Airtable Universe, or directly to the publicly shared bases are below.
RLP with DNA 4.0 (2024) Log or https://airtable.com/apppG92iI7elv31sm/shrytYJfQiqrdVHni
RLP with DNA 4.0 (2024) Blank or https://airtable.com/appEMarjt7OpJ20sy/shrPlBysjSEaNbvXG
In this blog post, I’m sharing an overview of each table and some of the updates from previous versions of the base.
Overview of Research Like a Pro with DNA Airtable Base
DNA Match Details
This table is for tracking matches between two test-takers at one company. If the same two people match at another company or database, you make another row. Two fields in this table are links to the “people” table – the test-taker and match. When I refer to “test-taker,” I mean someone whose DNA results you have access to. You may have just your own results, or multiple test-takers. For example, in the RLP with DNA 4.0 base, I’m using results of 8 test-takers, including myself, my father, my grandmother, and several cousins (fictitious examples). This table tracks the genetic distance or amount of shared DNA, number of segments, longest segment, match page, match’s tree, ICW, MRCAs, and so forth.
People
The people table is a place to track all the details and clues that apply to just one person. Because there are different types of people in your project, there is a field for role. This allows you to define within your project the role of the person, whether research subject, relative, test-taker, DNA match, and so forth. There are fields for line of descent from research subject, link to profile page, ethnicity, haplogroups, GEDmatch kit number, residence, permission, age, and contact information.
Kit Access
The kit access table is for tracking the test-takers, whose results you have access to. This allows you to manage which databases their results have been uploaded to, reminders for how you access their results (i.e. results shared to my Ancestry account, login credentials saved in password manager, etc.).
Correspondence
This table has a row for each message sent and response received. I recommend copying and pasting the full message into the long text fields so you don’t have to go back and find emails.
Timeline
The timeline is for tracking information about your research subject in chronological order. This focuses on details drawn from documentary research.
Research Log
The research log is for tracking all research done in documentary and DNA sources. There is a linked field to the FAN club table allowing you to track family, friends, associates, and neighbors of those in the documents.
FANs
The FAN club table includes rows for each associate of the research subject you’re tracking.
Citation Templates
This table is specifically for those taking our Research Like a Pro study group or course who are practicing make citations and saving commonly used citations for future use.
Segments
Segment information can be saved to this table, including chromosome information, start and stop points, and SNPs. There’s a checkbox for whether the segment has been added to a chromosome map at DNA Painter.
Surnames
Tracking the surnames of DNA matches can help determine the common ancestor. For matches where the connection is yet unknown, you can add a row for each surname that might be relevant.
Locations
Similar to the surnames table, the locations table is for tracking locations from DNA matches’ trees, as well as locations relevant to your research subject in the timeline and research log.
Shared cM
The shared cM table has data from Blaine Bettinger’s Shared cM Project. This allows the fields in the DNA Match details table to populate automatically and check the amount of shared DNA against the range for that relationship in the Shared cM Project.
General Updates
One of the updates I made to the 2024 base was to change the primary field type to formula fields for more of the tables. This allows you to skip data entry for the description or primary field. The primary field’s intended use in databases is to describe the record/row. Instead of typing into that field, you can now type into other parts of the row, and the formula will automatically put certain parts of the row into a phrase that describes it. For example:
“Don Jones – Nicole Dyer, AncestryDNA – 217 cM” in the DNA match details table
“Renee Hollingsworth – AncestryDNA” in the Kit Access table
“Marriage of Elam Hollingsworth, Martha Mae Keetch – 27 February 1862: in the Timeline table
“Negative Search, Quaker Meeting Records, Church Record at Ancestry for Elam Hollingsworth, United States” in the Research Log table
“Chr 5 – 6.6cM – “Garren Hollingsworth – Nicole Dyer, MyHeritage – 144.9 cM” in the Segments table
I also added more field descriptions to help you as you’re trying to figure out what to type into that field. Fields (columns) with a description have an “i” with a circle around it in the column header.
More example data was added to also help you visualize how the base is supposed to work.
Updates to the DNA Match Details Table
I made a change to the shared DNA column that allows you to use that column for both autosomal matches and Y-DNA / mitochondrial DNA matches. When adding a DNA match between two people, the genetic distance can be input into the cM/GD column. For autosomal DNA, genetic distance is measured in centimorgans (cM). For mitochondrial and Y-DNA, it’s measured in mutations, and given a number like genetic distance 2. This field is used for primary field, which is a formula describing the record. The formula is now enhanced to add the unit “cM” to any number in that field that is greater than 7. If the number is equal to or less than 7, it will use GD (genetic distance).
Primary field in DNA Match Details table includes units of GD if equal to or less than 7.
This should work for most projects – most people won’t be logging or using autosomal DNA matches that share 7cM or less. There are some cases this won’t work, for example, if you are using a Y-111 marker test and logging a match that has a genetic distance of 8-10, then it will show cM and you’ll have to adjust your formula. If you are logging an autosomal DNA match from 23andMe that shares 7 cM, then you will need to adjust your formula. The thresholds for the various companies are:
- Y-67 tests include matches up to a GD of 7
- Y-111 includes matches up to a GD of 10
- Ancestry reports matches down to 8 cM unless they were starred before the threshold change
- FTDNA reports matches down to 9 cM
- 23andMe reports matches down to 7 cM
- MyHeritage reports matches down to 8 cM
Line to MRCAs Field
Also, the field “DNA match’s line to MRCAs” was deleted in the DNA match details table. Instead of putting that information in each time one of your test-takers matches that person, you can just add the persons’ “line of descent from the research subject” in the People table. A lookup field for this field was added to the DNA match details table so you can still see their line of descent in the DNA match details table.
Match’s Tree Field
I think it’s repetitive to add the link to the DNA match’s tree to multiple rows. However, I decided to leave this field in the DNA match details table because some people will have different trees on different databases (like Ancestry and MyHeritage). So, as it is now, the table has you to add a link to the DNA match’s tree each time they match one of your test-takers. It’s easy to copy and paste this if you have, for example, 6 test-takers matching one key DNA match. Just group by DNA match, then use the cell handle to fill all rows below in that grouping.
Another option that could work is to have a long text field with rich formatting in the People table for the person’s family tree, and include text like this: Joe’s tree at Ancestry, Joe’s tree at MyHeritage. The text could then be hyperlinked to the trees.
Relationship Field
In past version of the RLP with DNA base, you could add more than one relationship to the relationship field. This is a linked record field that goes to the data from the Shared cM Project in the last table. If you add more than one relationship to this field, the formulas to check if the amount of shared DNA falls within the ranges don’t work. I changed this field to only allow one relationship. If you want to test out different relationships, just add them one at a time to see if they fall within the range. If you don’t know the relationship yet, just leave it blank.
Updates to the People Table
A few updates were made to the people table. People can now have more than one role in the project. This might be helpful if one of your people is a DNA match, and an admin of someone else’s kit. Some of the roles I’ve used for this are FAN, possible relative, research subject, test-taker, ancestor of DNA match, admin, researcher, and client.
I also moved the notes field toward the front/left of the table so it would be available to quickly type any helpful identifying information about the person. For example, with same-named individuals, you could add a note for their birth or residence to help differentiate them.
I moved the “line of descent from the research subject” column over to the left as well, to make it easier to add during data entry, and not get lost in the right of the table where there are many fields for haplogroups, contact information, and etc.
Updates to the Timeline Table
One of the most significant changes in this year’s version of the RLP with DNA base is the linking between the timeline table and the research log table. A linked record field called “source” is connected to the research log. This allows you to add multiple events and pieces of information to the timeline from once source without copying and pasting the citation.
Timeline Table with Sources as Linked Records from the Research Log
The source citation field in the timeline is now a lookup field from the research log. When you link a source from the research log to the timeline, the citation for that source is automatically added to the timeline. You can also link multiple sources to one row/event. This allows you to show all the sources that provide information about a person’s birth, for example. If there is conflicting information about a birth or other event, you can add two rows to show the information separately – it’s your choice.
There is also a lookup fields from the linked source bringing over the URL.
The FAN field was removed from the timeline table. Now the FANs are only added in the research log table. This simplifies the FAN table by only having a reciprocal linked field from the research log – instead of having two reciprocal linked fields, one from the timeline and one from the research log.
The locality field was changed into a linked record field that links to the Locations table. This allows you to correlate locations from your documentary research with location clues found in the trees of DNA matches.
I added a sortable date field to the timeline that allows the timeline to be sorted chronologically. In the past, we shied away from using this because the date field type doesn’t allow for genealogical date formats. I would just put things in chronological order myself by dragging and dropping, or inserting records above and below existing records. However, I found that using both date columns can be helpful. In the regular date column, the field type is a single line of text, so I can type vague dates like 1851 and exact dates like 4 May 1856. In the sortable date column, I can just add 1/1/1851 to get that row to sort to the top of all entries from 1851. For the 4 May 1856 date, I can type 5/4/1856 in the sortable date column. Having a sortable date column allows you to sort and group more easily. If you don’t want to use that column, you can delete it or hide it.
The timeline table has a field for the person that the information is about. You can link one or more people from the people table. If you want to see the timeline for just one individual, you can filter your timeline to include just records with that person’s name in it, then save that as a “view” of that table. Your saved views don’t take up additional space, but allow you to have various views of the table depending on your needs. In the RLP with DNA base, the example data shows four possible views of the timeline table: All, Elam Hollingsworth, Martha Keetch, and Census Data. If you’re writing your report and want to focus only on records found relating to Martha Keetch, you could look at that view.
Previously, in Research Like a Pro classes, we have talked about the timeline as a place mostly for analyzing your starting point information; with the research log being the place for new findings. Now, you can update the timeline all throughout your research, and use a “starting point” view of your research log to see what you had before the new findings.
Updates to the Research Log Table
The research log table is now the home for all research that needs to be logged – whether it’s a documentary source you had at the start of the project, a documentary source found while following your research plan, or a DNA tool used.
The locality field was changed into a linked record field that links to the Locations table, like the timeline table.
The date field was changed to be an “automatic date/time” field type, so when that row is created/added, it automatically populates with the date and time. This reduces data entry and makes your life easier!
The template with example data shows how you can have multiple views of the research log, including all, starting point, findings for this phase, the research subjects (Elam and Martha), and DNA matches & ancestors.
Research Log Table with Views in Left Panel
Workflow between the Timeline and Research Log
When making your timeline, you can first add the source in the research log. Then, you can go to the timeline and add the events and details that are specified or inferred in the source. Another option is to start making the timeline, then when you get to the source column, you can “add a new record” to the research log table, using the expanded page-like view, while staying in the timeline table. You can add details about the source, the citation, etc., then close that and continue making your timeline.
Expanded record from the Research Log open in the Timeline Table
Analysis in the Timeline
In Research Like a Pro, we teach that you should analyze the source, information, and evidence in your project, especially at the beginning when you make your timeline. This is also important to do during the research step, when you discover new sources and information.
The source analysis field was removed from the timeline, and added to the research log table. Conceptually, the research log is now the home to all the sources, and that’s where you analyze the source – whether original, derivative, or authored. There is a single select field for those source labels, and a long text field for additional analysis. For example, “Headstone photo is original, which is how I’m citing it. The memorial is authored.” The information and evidence columns are still in the timeline table. This allows you to evaluate each detail or event separately, for example, the 1856 passenger list for Martha Keetch is has this note in the information analysis column: “Primary about their immigration, unknown information for their age/birth year.”
Can’t Combine the Timeline and Research Log
The main difference between the timeline and log and the reason why they can’t be combined, is the timeline allows you to arrange pieces of information in chronological order, while the research log is a list of sources consulted. If you have 5 pieces of information all from the same source, you’ll have five rows in a combined timeline/log where you’re copying and pasting the source citation. If you fix an error in the citation in one of those rows, but not the other, then you have more than one version of the citation and might get confused about which is correct.
Updates to the Correspondence Table
The only update for this table is that there are now links to the people table for the “to” and “from” fields. In the past, only the column “DNA match” was linked to the people table, and assumed that all relevant correspondence would be regarding a DNA match. Now there is more flexibility.
Updates to the FAN Club Table
The FAN club table has a new field for “identified person.” This is a link to the People table and allows you to link together multiple FANs who have been determined to be the same individual. Take for example Alfred G. Keetch, Alfred Keetch, and A.G. Keetch. If you decide these are all Alfred Greenwood Keetch, you can add a link to Alfred Greenwood Keetch in that field.
To maintain the integrity of the original record when entering FANs, it’s recommended to type their name exactly as it appears on the record. It’s also recommended to add a new row for each instance of a FAN club member on a different document, even if their name is spelled the same exact way. This allows you to see all the instances of a person’s associations with the research subject as separate rows.
The FAN club table now includes formula fields for first name and last name. This allows you to separate the name as it was entered in the primary field into two columns for better sorting and grouping.
The FAN role column is where you choose what type of association existed between the research subject and the FAN on that particular record, for example, witness, neighbor, godparent, recorder, brother-in-law, etc. This was changed to a multiple select field type in case a person was acting in more than one role in one source. For example, a person in a will might be listed as a guardian and an executor.
Learn More
If you’d like to learn more about using Airtable, please join us in our Research Like a Pro courses or study groups. We also have a PDF Airtable guide available for purchase in our shop.
6 Comments
Leave your reply.