Quote from Tom Holden on 2024-03-19, 7:53 am

The first step is to import the CSV into a temporary table in your target database or into a temporary database ATTACHed to the target. It will need the RIN (PersonID) of each intended member of the group. Can you do that? Your sqlite manager may have a convenient import tool. Another method is to use a text editor to transform the CSV list into a series of INSERTs.

Quote from thejerrybryan on 2024-03-19, 9:45 am

I'm trying to understand how FTA assists with this problem. I'm sure it does, but I don't follow the process.

In any case, I have a color coding script that I think could be adapted to do more or less what you want without involving a CSV file or FTA at all. Here's a highly oversimplified description of how my script works.

Color code everybody in my database as yellow. I use color code set #1, but you can use any other color code set to avoid messing up your existing color coding. This can be done from within RM without using SQLite.

Color code everybody in my tree as red. This can be done from within RM without using SQLite.  At this point, the yellow people are the Count Trees people who are not in my own tree. If I understand your goal correctly, that is what you are trying to accomplish - namely, make a group of the people I am color coding as yellow.

I can now make a group of the yellow people. Or for that matter, I can create the "not in my tree" group without color coding at all. When making the group, I could mark everybody and then unmark people who are in my tree.

I wanted the color coding for this use case rather than making groups because color codes are immediately visible on the screen and group membership is not. It's not that I like color coding and don't like groups. I love groups and I use them heavily. It's just that color coding seemed to be a better fit for this use case than did groups. Your mileage may vary and I'm a sample size of one.

The above is dependent upon having run the Set Relationships tool with myself as the focus person for the tool. The result of running the tool and of the way I have color coded is that all the yellow people will have PersonTable.Relate1 = 0 and PersonTable.Relate2 = 0.  All the red people will have PersonTable.Relate1 != 0 or PersonTable.Relate2!= 0 or both. Except that none of the processing so far requires the use of SQLite. The Relate1 and Relate2 fields can be decoded to provide more information such as second cousin three times removed, but for my purposes, all I need to know is zero or non-zero.

Unlike the new dynamic Saved Criteria groups tool, the Set Relationship tool is static. Therefore, I periodically have to run Set Relationships again and run my color coding script again. But the rerun process works very smoothly as long as I remember to do it.

I think the above is all you need and you can quit reading. But let me continue with what else I do. The following does require SQLite.

I color code anybody who is still yellow and who is the spouse of red person as green. (spouses of red people) Uses the FamilyTable and the Relate1 and Relate2 fields of the PersonTable.

I color code anybody who is still yellow and who is spouse of a green person as blue (spouses of spouses of red people). Uses the FamilyTable and the Relate1 and Relate2 fields of the PersonTable.

I color code anybody who is still yellow and who is is the parent of a green person as purple (parents of spouses of red people - in other words, in-laws). Uses the FamilyTable and the ChildTable and the Relate1 and Relate2 fields of the PersonTable.

I color code anybody is still yellow and who is the child  of a purple person as brown (brothers-in-law and sisters-in-law of red people). Uses the ChildTable and the Relate1 and Relate2 fields of the PersonTable.

The script goes into a few more rare cases, but that's the general idea. Any of these colors could trivially be made into groups using the RM user interface.

One late addition to my script is that I found it useful to color code a few people as gray from within the RM user interface and then to have my script ignore all the gray people completely. The idea is that the yellow people are the people who are strong candidates to be deleted from my database and the gray people are people to whom I'm not (yet) related but on whom I'm still doing further research. The only color which I actually do make a group is the yellow people, and it is a Saved Criterion group. I'm constantly working on the yellow people, either to delete them from my database or to turn them into a different color. By refreshing the yellow group, it automatically adjusts itself to be only the people still of current interest for possible deletion.

Here's the script in all it's glory and all it's mess. It finishes by creating a color code report.

Quote from kevync on 2024-03-19, 10:40 am

I get the concept you are doing. Interestingly you/I  have a similar thought on coloring -- I use gray for >=15 degrees (and not direct ancestor) -- have to find that one later.

As far as FTA -- Individuals report (CSV)  has column N (Relation).

Relation has 8 types it seems (in my case anyway) -- The unknowns is what I would like to report on - which in theory should closely mass RM Count Trees

(Pivot Table for that col)

Blood Relation	6293
By Marriage	8481
Descendant	4
Direct Ancestor	863
Linked by Marriages	2040
Marr to Direct/Blood	4059
Root Person	1
Unknown	145

Using's Tom's suggestion -- I could import the filter list of PID's to a temp table and create a group so I could check things with the RM interface.  I might have to do some adaption on my current coloring method -- but your method definitely has many advantages  and I could use in one of the sets I am not using.

--  this does spouses of related into a group

--Create Named Group if it does not exist 'SQL: Related by Spouse'
--adapted by Kevin Labore

INSERT OR IGNORE INTO TagTable
VALUES
(
(SELECT TagID FROM TagTable WHERE TagName LIKE 'SQL: Related by Spouse')
,0
,(SELECT IFNULL(MAX(TagValue),0)+1 FROM TagTable)
,'SQL: Related by Spouse'
,'SQLite query'
,julianday('now') - 2415018.5
)
;

-- Delete all members of the named group
DELETE FROM GroupTable
WHERE GroupID =
(SELECT TagValue
FROM TagTable
WHERE TagName
LIKE 'SQL: Related by Spouse'
)
;
-- Add members to the named group
INSERT INTO GroupTable
SELECT
null
,(SELECT TagValue
FROM TagTable
WHERE TagName
LIKE 'SQL: Related by Spouse'
)
,PID AS StartID
,PID AS EndID
,(julianday('now') - 2415018.5) AS UTCModDate

FROM
(
SELECT MotherID as PID FROM FamilyTable
LEFT JOIN PersonTable m ON m.PersonID = MotherID
LEFT JOIN PersonTable f ON f.PersonID = FatherID
WHERE (FatherID and m.Relate1+m.Relate2 = 0)
and (MotherID and f.Relate1+f.Relate2 > 0)
UNION
SELECT FatherID as PID FROM FamilyTable
LEFT JOIN PersonTable m ON m.PersonID = MotherID
LEFT JOIN PersonTable f ON f.PersonID = FatherID
WHERE (MotherID and m.Relate1+m.Relate2 = 0)
and (FatherID and f.Relate1+f.Relate2 > 0)
ORDER BY PID

)

Quote from thejerrybryan on 2024-03-19, 11:03 am

I envy your database with only 145 unconnected people. My database of about 40,000 people still has 6,011 yellow people (unconnected). Well, I have some gray people who are unconnected also, but I want to keep them for now. It's the yellow people who are candidates for deletion. But I don't want to just blindly delete them, so I need to look at them before deleting to see why they are in my database in the first place. Fortunately, I seldom have to look at them one at a time. I can usually delete a bunch of them all at the same time when they are in a family grouping.

These people got into my database mostly by importing them from other researchers (mostly from the Internet) close to 30 years ago when I was first starting out and didn't have a clue what I was doing. I have periodically been tempted to do a complete do-over, but gradually deleting these people is what seems to work best for me. So far, I have managed to get my database down from about 60,000 people to about 40,000 people. I would like to get down to about 30,000 but it's probably going to be about 35,000 people when all the yellow people are gone. And by the way, upon further review some of the yellow people do turn into red people (blood relation) as I research them a little bit, so I think the project is worth doing rather than just blindly deleting all the yellow people.

Quote from kevync on 2024-03-19, 1:56 pm

Well I try to  keep under 20 something trees.  I still rarely delete anyone. ( I delete a few people today that have been around since I started with RM  3 years ago) that. I am going to explore adapting your color method  as it in the direction I want to head. I need to mostly update to RM9 with the ten color sets .

I have 3 larger trees that I suspect have a connection that I have not yet found. (15 -60 people) the others are all small -- I wish you could exclude associations as those IMHO should not be included as less told so.

I understand what you mean by trimming your tree to make it more manageable.  My oldest daughter got married last December.  Her  mother in-law side spent basically the last 200+ year in North Carolina area and I have only started there that quickly added 300 people without much effort (

below is script I did to create the group.

Also RM Screenshot of People list -- I will likely first check the ones that have FSID for hints then other from there to see if I can find connections.

 

- Create Named Group if it does not exist 'SQL: FTA no REL'

INSERT OR IGNORE INTO TagTable
VALUES
(
(SELECT TagID FROM TagTable WHERE TagName LIKE 'SQL: FTA no REL')
,0
,(SELECT IFNULL(MAX(TagValue),0)+1 FROM TagTable)
,'SQL: FTA no REL'
,'SQLite query'
,julianday('now') - 2415018.5
)
;

-- Delete all members of the named group
DELETE FROM GroupTable
WHERE GroupID =
(SELECT TagValue
FROM TagTable a
WHERE TagName
LIKE 'SQL: FTA no REL'
)
;
-- Add members to the named group
INSERT INTO GroupTable
SELECT
null
,(SELECT TagValue
FROM TagTable
WHERE TagName
LIKE 'SQL: FTA no REL'
)
,PersonID AS StartID
,PersonID AS EndID
,(julianday('now') - 2415018.5) AS UTCModDate

FROM
(
SELECT PID
FROM 'FTA Ind List 3'

)

Quote from kevync on 2024-03-19, 8:37 pm

@thejerrybryan

so far only modified to set colors on color9(set10)

Here was the result on my tree

 

 

Quote from kevync on 2024-03-20, 1:59 pm

@thejerrybryan

thank you again for the script -- for now I only needed to slightly modify it (for 2 minor changes)  -- and overall it works better than the Consang coloring I did  a script for.  The nice approach you implemented was not leave "black" (no color) -- so if someone is black either they were just added  or color was removed some how.