Setting Relationships to identify direct AND indirect (cousins) family members

I have an unruly 11K file of individuals that is 20 years worth of combined GEDCOM from multiple cousins and uncles and my own individual input. It’s a mess as a lot of the names do not have sources attached to them, either. I have spent a ton of time in FamilySearch connecting thousands of source references to my FamilySearch tree individuals and am now connecting one by one back to my Rootsmagic file. I would actually prefer cutting off individuals from the file who are not even related to me (direct or cousin) and not include them in my file as they add no value to me and would greatly shorten my cleanup and management process going forward. I would love if Rootsmagic had some way of including direct AND indirect relation in the People List View so I don’t have to go through each of the 11K one by one and determine if they stay or go. Is there an easy way to identify individuals without me Setting Relationship to me and checking each individual person one by one? I wish there was a way to have a identifying direct AND indirect family member via a tag like there is for direct line to make this task easier. I would appreciate any advice

If the goal is to get rid of those unrelated to you, you could drag’n’drop selecting everyone in the same tree as you to a new database. Some stuff will be lost.

Thanks for the idea. What I’m doing now and it will be very time consuming going through every person in the People List and Color Coding “Red” and selecting “Ancestors of current person” for ones not related to me and “Green” and selecting “Descendants of current person plus including spouses” for ones related to me. That way, when I look at the chart or a list I will know exactly who is or is not related. It would be awesome if Rootsmagic could do this automatically as this will only apply in a static state. In other words, there is always the possibility of an unrelated individual becoming related through a marriage that I uncover later on. To be 100% accurate, this process would need to be updated every time a person is added to the file as relationships will be potentially impacted. If Rootsmagic had a “Relationship” field in the People List that could be added, this would not be necessary, either.

So you want to keep everyone but color code those that are unrelated to you? The Set Relationships function stores results in two fields for each person in the database PersonTable. You could use sqlite to execute the color coding quickly after you run Set Relationships. For example, see

Thanks, Tom!!! I didn’t know this was even possible. Now I need to learn SQL. Am I assuming correctly that editing still occurs in the Rootsmagic database but it’s just happening through SQL rather than within the commands of the Rootsmagic program? It’s too bad Rootsmagic doesn’t integrate this feature or some basic spreadsheet commands like selecting multiple rows simultaneously within their program.

Perhaps producing a Kinship Report & an Individual List and manipulating them in excel using the RIN numbers? That at least should give you a list of those that are not related.

1 Like

Yes, in this example, you use RM to Set Relationships, open the database with a sqlite manager and execute the script to update the color codes, reopen the database in RM to view the result. Follow the Learn item on the site Menu for a basic intro…

1 Like

I’m going to need to research that how-to, as well. I wish Rootsmagic had spreadsheet functionality built in.

Tom, do you know if you can also include spouses married to direct/indirect family members in the coding? I don’t want to eliminate the unrelated spouse that is related by marriage and leave any cousins hanging without a parent, just this person’s ancestors in my tree which are no relation. If I wasn’t knee deep in this file I’d start from scratch.

In the scripts to colour code for consanguinity, spouses and in-laws were excluded by a simple WHERE condition. Delete the WHERE clause and they are included, to the extent that RM Set Relationships identifies them. If it does not do so beyond some distance, I imagine that a sql statement could be devised to assign a colour code to spouses of those for whom a relationship has been calculated.

For further discussion about using sqlite with RootsMagic, I advise doing so in the Forum, rather than here because RM Inc does not promote the use of sqlite nor provide any support should you screw up your database in so doing. And most of the user base is sql illiterate and disinterested.

I have an SQLite script that I use for color coding. The things the script does are not possible from within RM itself. It’s very similar to Tom’s “Color Code by Consanguinity Degree” except that it’s more specialized for my own specific needs. For that reason, I have not yet posted it to Tom’s SQLite web site. Perhaps I should add it anyway. So I offer the following description of what my script does mostly just for ideas of what is possible with SQLite. I can certainly post the script to Tom’s site if there is any interest.

  • Color code as yellow everybody in the database. I think of yellow as the uncolored state. I use yellow rather than black for the uncolored state because doing so enables me to distinguish people who are deliberately not color coded from people who are newly entered into my database and are still color coded as black. In all the following color coding that follows, the person is color coded only if they presently are still color yellow. That test avoids recoloring somebody after they have already been given the most desired color.
  • Color code as red everyone whose Relate1 or Relate2 fields in the RM database are not zero. There are two separate Relate fields as a part of RM’s encoding of the degree of consanguinity. For my purposes, I don’t care about the degree of consanguinity, just that they are related. This is a difference between my script and Tom’s. Also, I usually color code after running Set Relationships against one of my children instead of running Set Relationships against me in order to pick up both my family and my wife’s family.
  • Color code as green everyone who is color coded yellow and who is the spouse of someone who is color coded red. For fairly obvious reason, I want to keep the green people in my database rather than deleting them.
  • Color code as blue everyone who is color coded yellow and who is the spouse of someone who is color coded green. This is the case of a spouse of a spouse of someone to whom my children are related. I have considered long and hard whether I want these blue people in my database in the first place. Strictly speaking, I don’t really need them. But I use RM’s shared fact facility to share the Marriage and Divorce facts with each party to the marriage or divorce. I do so to make marriage and divorce show up narrative reports in the timeline for each individual. Keeping the the blue people in my database means that their marriage or divorce with a green person means that those marriages and divorces show up in narrative reports for green people.
  • Color code as purple everyone who is color coded yellow and who is the parent of someone who is color coded green, This is the case of a parent of a spouse of someone to whom my children are related. I have a Parents fact for everyone in my database who has parents, and the Parents fact is linked to the actual parents via a shared fact. This provides a place to attach evidence of parentage and it also allows a person’s parents to be listed in their timeline in narrative reports. In particular, a spouse’s marriage and divorces with other spouses therefore show up in my reports.

In theory, that’s all the color codes I need and I could delete everybody in my database who is still yellow after this color coding. I do have a few more color codes I use for more obscure purposes, but that should provide a good sense of what is possible with SQLite which is not possible from within RM itself.

1 Like

Your reply makes my head hurt, lol. You make my wonder if I’m being too extreme in wanting to eliminate relatives on the side of the spouse married to one of my direct/indirect family member.

It makes my head hurt, too. :grinning: I have struggled for years with the question of setting boundaries - who do I keep in my database and who do I not. The trouble is, if I want to keep them in my database then I want to research them properly which takes a lot of time. Over the last couple of years I have weeded my database down from about 60,000 people to about 40,000 people. But I still struggle with who to keep and who not to keep.

Your reasoning is exactly why I’m cutting my database down before I dig into validating the entries via sourcing. I too, feel the need to spend unnecessary time on individuals not related to me. I don’t want a file that reminds me of Familysearch.

I’m always learning from what you post here and on my website, Jerry. You’ve described a procedure that builds out from the Relate fields to include non-consanguinous spouses and their parents which I’ve not addressed.

The SQLite script is now uploaded to Tom’s site at Color Coding All Related People