TMG –> RootsMagic Cleanup
Randy Seaver recently discussed the upcoming ability of RootsMagic to sync with Ancestry and what he is and isn’t doing to prepare for that in his blog post, “Dear Randy: What are you doing to prepare for the RootsMagic program sync with your Ancestry family tree?” While reading Randy’s blog, I realized that I was in the middle of such a preparation with my work on my census facts.
My genealogy data was migrated from The Master Genealogist version 9 to RootsMagic. When I selected The Master Genealogist (around version 4 or earlier), it was because I wanted something that allowed me to add citations for each event. Thru the TMG community, I developed my research and documentation skills. I also applied several TMG ‘hacks’ — especially if they helped visualize the events in someone’s life.
One of those ‘hacks’ was a modification to the census tag developed by Terry Reigel. It took me some time to implement this hack, but once completed, it allowed me to see the family in the timeline for the head of the household.
Since RootsMagic would not handle the ‘split sentences’ in the census-head or census-enum tags, I did have to modify the sentences. I was able to do this in TMG prior to the migration. Because, I liked how the census tags worked, I did not modify them in TMG but let them migrate into RootsMagic as custom event (fact) types.
As I began to learn to use RootsMagic with Family Search, I realized that my custom fact types were not lining up with the corresponding fact type on Family Search. Since the tree on Family Search is a community tree, I’m very hesitant about making changes – but also want to see more documentation for my ancestors. Thus, the conflict — my custom fact types would ‘foul up’ the Family Search tree but the census records have not been sourced. Because of that conflict, I decided to figure out how to revert my custom fact types (census-head and census-enum) to the standard type.
Knowing that there wasn’t an easy way to do this from within RootsMagic I turned to the SQLite Tools for RootsMagic community. There, I found directions on how to setup SQLiteSpy so that it would read and modify the tables in the RootsMagic database. Once I had this software downloaded and correctly configured, I used the SQL script, Facts – Change Fact Type to change all of my census-head and census-enum fact types to the standard census fact type. Since this SQL script directly modifies the database, I copied the database and worked with the copy FIRST. That allowed me to make sure the script was doing what I wanted without the danger of corrupting my data. Once I knew it was working, I backed up the data and then ran the script on the original copy of the data.
After running the script, the census citations in my RootsMagic database lined up with any census citations on Family Search. Step one accomplished!
Besides changing the custom fact type to the standard, I had two other potential issueds with my census facts. The first involved the sentences. It appears that what was in the memo field in TMG was dumped into the note field in RootsMagic while the sentences pull the information from the description field. Thus, all of the information I had entered about the individual wasn’t being pulled for the sentence. Since almost all of my census facts had witnesses associated with each fact, individual reports and web output was showing extra sentences/facts for other members of the household.
So, my next step was to move the info in the note field to the description field while also removing any witnesses. Since I couldn’t get the SQL scripts for this process to work (they do exist), I resorted to doing this one person at a time. With over 10,000 census entries this is no small task. I started with my ancestors who were living in 1850 and worked thru their descendants. However, I’ve been researching several neighbors and other potentially connected families and their descendants. Thus, I needed some sort of report that would help me know who was left to do.
The SQLite Tools for RootsMagic came thru again! On their site, I found a link to the “People who share a fact with a principal list” script. This particular script just creates a list, it doesn’t modify the database. However, the script must be run with the RootsMagic database closed. I have the script saved in my SQL directory. Each time I want to run it, I open the script with Notepad and then copy and paste it into SQLiteSpy. Once executed, the script will create a list. I copy the info in that list and paste it into a blank Excel spreadsheet. That way, I can close SQLiteSpy and open RootsMagic and still have a list to work with.
Once the data is in Excel, I do a multilevel sort: Fact, Surname1, Given1, RIN1. This allows me to easily delete everything but the census records. Armed with that list, I just work my way thru the records. I’m down to about 1300 census events.
Will this be worth it? Because this is cleaning up my data and making it easier to see corresponding census records on Family Search, I will continue until finished. I’m also hoping that by using the standard ‘census’ fact type, this data will also line up with Ancestry. My wish is that all of my census data will keep me from having shaky leaves for those same census records.