Color Coding Relatives and Spouses Without Reference To RM's Relationship Codes

#1 · 2024-07-19, 1:42 pm

Quote from thejerrybryan on 2024-07-19, 1:42 pm
I have a solution that works for me despite the new Set Relationships code in RM 10.0.1. In fact, it doesn't use RM's relationship fields at all. It's not perfect because I'm not set up to run recursive or repetitive SQLite queries.
That means that part of my process has to be repeated manually until everybody is marked. But doing so is so easy that's it not onerous. I use SQLiteSpy which uses F9 to run a script. I just hit F9 multiple times until it's done, and it only takes a few seconds to do so. I'm sure that several of you all know how to convert this into a recursive query that only has to be run once.
That in turn means that I had to break what was one script into four.
Initialization. Clear all color codes. Assign a color code of red to one person to be the base of the relationship, usually yourself. Actually, it's set up so there could be multiple base people such as yourself and your spouse. This step only has to be run once and there is no repeating.
Color code as red all the direct ancestors of the base person (or base people) as red. This step only color codes one generation at a time. Therefore, it requires hitting F9 multiple times until all the generations of ancestors are color coded.
Color code as red all the children of all the people who are color coded as red. This step only color codes one generation at a time. Therefore, it requires hitting F9 multiple times until all the generations of descendants are color coded. This specifically doesn't color code spouses of relatives, unless it happens by accident that a spouse of a relative is also a relative.
Run the rest of my original color coding script. This color codes spouses of relatives as green, parents of green people as purple, siblings of green people as brown, etc. This only has to be run once and there is no repeating. A person is color coded green only if they are a spouse of a relative without also being a relative in their own right.
That's pretty much it. There's a bit more details as comments in the source code itself.

I have a solution that works for me despite the new Set Relationships code in RM 10.0.1. In fact, it doesn't use RM's relationship fields at all. It's not perfect because I'm not set up to run recursive or repetitive SQLite queries.

That means that part of my process has to be repeated manually until everybody is marked. But doing so is so easy that's it not onerous. I use SQLiteSpy which uses F9 to run a script. I just hit F9 multiple times until it's done, and it only takes a few seconds to do so. I'm sure that several of you all know how to convert this into a recursive query that only has to be run once.

That in turn means that I had to break what was one script into four.

Initialization. Clear all color codes. Assign a color code of red to one person to be the base of the relationship, usually yourself. Actually, it's set up so there could be multiple base people such as yourself and your spouse. This step only has to be run once and there is no repeating.
Color code as red all the direct ancestors of the base person (or base people) as red. This step only color codes one generation at a time. Therefore, it requires hitting F9 multiple times until all the generations of ancestors are color coded.
Color code as red all the children of all the people who are color coded as red. This step only color codes one generation at a time. Therefore, it requires hitting F9 multiple times until all the generations of descendants are color coded. This specifically doesn't color code spouses of relatives, unless it happens by accident that a spouse of a relative is also a relative.
Run the rest of my original color coding script. This color codes spouses of relatives as green, parents of green people as purple, siblings of green people as brown, etc. This only has to be run once and there is no repeating. A person is color coded green only if they are a spouse of a relative without also being a relative in their own right.

That's pretty much it. There's a bit more details as comments in the source code itself.

Uploaded files:

#2 · 2024-07-19, 4:21 pm

I have a solution that works for me despite the new Set Relationships code in RM 10.0.1. In fact, it doesn't use RM's relationship fields at all. It's not perfect because I'm not set up to run recursive or repetitive SQLite queries.

I have thought that a recursive scripts to do coloring should be possible (Maybe challenging to combine into one long script.) My experience with recursive scripts is very limited - so maybe when I have a weekend to kill I attempt.

Thanks for sharing

#3 · 2024-07-19, 6:43 pm

My feeling is that one should always create a group that meets the criteria and then color code using the group.

I have recursive SQL's and a utility app on my site to do relationship grouping in one step.
Maybe it's time to add the color coding as an option

kevync has reacted to this post.

#4 · 2024-07-19, 10:30 pm

Maybe it's time to add the color coding as an option

not sure how many make use of your site -- I am sure some users would appreciate it.

#5 · 2025-01-07, 12:45 pm

This is awesome, thanks for sharing. I adapted it to one of my much more simple use cases: I have a database from someone with nearly 250,000 people from a geographical area where much of my relation originally settled. I use one color group to set all blood relation to black such that when I search the database against recent obits, it very clearly illustrates who is blood relation and potentially missing from my own tree/database. Literally black and white.

You guys are far more advanced than I in SQL. I barely know simple SELECT. I wonder if you might be willing to figure out how to do something else for me.

In many manual steps in RM, using another color set, I use 8 different colors for my 8 great-grandparents. I then color all ancestors of those 8 with their corresponding colors. I then color all descendants of those lines with a subdued similar color (great grandparent #1 has red, all ancestors of that grandparent red, and descendants of that red line is colored a lighter color, pink for example. Great grandparent 2 ancestor line is orange, and their descendants apricot, a lighter version of orange, etc.). I believe I could figure everything out to this point in SQL based on the excellent example given by Jerry.

Where things get difficult for me is when relation marries relation, ie. distant cousin1 marries distant cousin2, and the color used for their descendants. When adding new people/colors manually, I choose the color associated with the "closer" cousin, as defined by me as the lower X+Y of the two, where X=Xth cousin and Y=Yth removed. (2nd cousin = 2+0, 1st cousin 3 times removed = 1+3=4, so I choose whatever color 2nd cousin is for their descendants.)

Any of you SQL experienced people able to code this up without much effort?

Uploaded files:

#6 · 2025-01-07, 1:46 pm

Actually I am working on something related.... its does two thing-- creates a Kinship/Relationship view and than assigns colors like this

Uploaded files:

#7 · 2025-01-07, 1:50 pm

it does relationships similar to this (this was my worksheet I started with to make sure I got them all)

Uploaded files:

#8 · 2025-01-07, 1:53 pm

one extra thing I do is to review DNA table and color them differently if they do not yet have path to "home" person set by relationships

Finally in part C I will color people if they are spouse of , Sibling of , Parent of not related person accordingly to RM PersonTable

#9 · 2025-01-07, 2:44 pm

I can't figure out an easy way to access the two or more different relationships you might have with a person. If that were possible, this would be an easy project. But RM only stores one such relationship. I have not yet played with RM's new DNA feature, so I don't know if using the DNA feature would help or not.

I have not yet fully mastered recursive queries in SQLite, but I can now muddle my way through them. Using a recursive query, it might be possible to calculate the multiple relationships on the fly rather than relying on RM's relationship data in PersonTable.Relate1 and PersonTable.Relate2 and PersonTable.Flags.

I may take a look at writing a script which supports multiple relationships at some point. In fact, it has occurred to me to write a query that is my own Set Relationships because I don't like the way RM does it now where a "spouse of" relationship can take precedence over an actual relationship, There seems to be no rhyme or reason as to when this happens,

And speaking of recursive queries, I'm pleased that my color coding script has found some use. But it was written before before I had learned to muddle through a recursive script. So it's embarrassing to have to keep running the color coding script over and over again until all the color coding is done. That's another thing I need to get around to - to convert it to a recursive script so you can just run it once and be done with it.

#10 · 2025-01-07, 2:48 pm

Quote from thejerrybryan on 2025-01-07, 2:44 pm
I can't figure out an easy way to access the two or more different relationships you might have with a person. If that were possible, this would be an easy project.

Well, maybe it's easier than this thinking. It's not about multiple relationships with the same person. It's the one relationship of their father to me, and the one relationship of their mother to me, and whichever is closest, then that parent's color should be the child's color. Maybe that's not easy in SQL either.