Group Queries #namedgroup #colorcoding #rm8 #rm7

In RootsMagic 7 group names are stored in the LabelTable and the group members in GroupTable with group id and RIN. The LabelTypes in the LabelTable are 0 – groups,  and thus it is the label value field that links to the group id in the group table.

Each person can only be in one group and the colour of that group is stored not against the group, but against each person on the person table. So only the last applied colour is valid.

The colour codes are:

However being RootsMagic there’s a gotcha of course, since the GroupTable doesn’t have the obvious one entry for each person in a group but stores the start and end RIN of clusters of people in the group.
SQLite however can cope with this by using the BETWEEN keyword so we have

WHERE RIN BETWEEN StartNo AND EndNo

which is the tidy readable equivalent of

WHERE (RIN >= StartNo) AND (RIN <= EndNo)

and both methods work perfectly.

So the SQL to list all the people in all the groups is:

with n as 
(Select
         NameTable.OwnerID AS 'RIN1',
         NameTable.Surname COLLATE NOCASE AS 'Surname',
         NameTable.Given COLLATE NOCASE AS 'Given'
FROM NameTable		 
where NameTable.IsPrimary = 1)

Select LabelValue as GroupID, LabelName as GroupName, Description COLLATE RMNOCASE as Description,
n.RIN1 as RIN, n.Surname, n.Given, p.Color
FROM LabelTable l INNER JOIN GroupTable g
ON l.LabelValue = g.GroupID 
INNER JOIN PersonTable p
ON n.RIN1 = p.PersonID
LEFT OUTER JOIN n on n.RIN1 BETWEEN g.StartID AND g.EndId
WHERE LabelType = 0
ORDER BY GroupID, Surname, Given, RIN

For RootsMagic8, the table names and structures have changed a little.

The GroupTable is still the same structure but group numbers have been changed by adding 1000 to the RM7 value. The LabelTable is renamed to the TagTable and LabelID becomes TagID and LabelType becomes TagType. Groups are still TagType 0 but TagType 1 is Research Log names.

So our SQL for RM8 is

with n as 
(Select
         NameTable.OwnerID AS 'RIN1',
         NameTable.Surname COLLATE NOCASE AS 'Surname',
         NameTable.Given COLLATE NOCASE AS 'Given'
FROM NameTable		 
where NameTable.IsPrimary = 1)

Select TagValue as GroupID, TagName as GroupName, Description COLLATE RMNOCASE as Description,
n.RIN1 as RIN, n.Surname, n.Given, p.Color
FROM TagTable l INNER JOIN GroupTable g
ON l.TagValue = g.GroupID 
INNER JOIN PersonTable p
ON n.RIN1 = p.PersonID
LEFT OUTER JOIN n on n.RIN1 BETWEEN g.StartID and g.EndId
WHERE TagType = 0
ORDER BY GroupID, Surname, Given, RIN

Having done this it is quite straightforward to update an existing group from a SQL query. I have two groups that I like to update in this way, the first is all those marked as Living and the second is all those who died before they reached the age of 14 so are unlikely to have descendants. All following code is for RootsMagic 7 and will not work with version 8.

/* Update people in living group */
/* List all living */
With liv as
(Select
         n.OwnerID AS 'OwnerID', g.GroupID
	 FROM NameTable n INNER JOIN PersonTable p
ON n.OwnerID = p.PersonID		 
LEFT OUTER JOIN GroupTable g
ON g.GroupID = 1 -- replace the 1 with the correct group id
AND n.OwnerID BETWEEN g.StartID and g.EndID
where n.IsPrimary = 1 
and p.living=1
)
INSERT INTO GroupTable (GroupID, StartID, EndID) 
  SELECT 1 as GroupID, -- replace the 1 with the correct group id
	liv.OwnerID,liv.OwnerID
	FROM liv
	Where liv.GroupID IS NULL;

Now update the colour by using the same CTE

With liv as
(Select
         n.OwnerID AS 'OwnerID', g.GroupID
	 FROM NameTable n INNER JOIN PersonTable p
ON n.OwnerID = p.PersonID		 
LEFT OUTER JOIN GroupTable g
ON g.GroupID = 1 -- replace the 1 with the correct group id
AND n.OwnerID BETWEEN g.StartID and g.EndID
where n.IsPrimary = 1 
and p.living=1
)
UPDATE PersonTable 
SET p.Color = 2 –- replace the 2 with the desired colour number
WHERE EXISTS (
        SELECT OwnerID
        FROM liv
        WHERE liv.OwnerID = PersonTable.PersonID);

The final WHERE EXISTS construct is required by a quirk of SQLite that it does not have the UPDATE… FROM syntax of standard SQL in the version used by RootsMagic 7. This may change in future.

The similar code for updating the group for those who died before age 14 is

with per as
(Select
         NameTable.OwnerID AS 'OwnerID',
         NameTable.Surname COLLATE NOCASE AS 'Surname',
         NameTable.Given COLLATE NOCASE AS 'Given',
		 NameTable.BirthYear,
		 NameTable.DeathYear,
		 GroupTable.GroupID
FROM NameTable LEFT OUTER JOIN GroupTable
ON GroupTable.GroupID = 1   -- replace the 1 with the correct group id
AND NameTable.OwnerID BETWEEN GroupTable.StartID and GroupTable.EndID	
where NameTable.IsPrimary = 1 and (DeathYear > 0) and (DeathYear - BirthYear) < 14)
INSERT INTO GroupTable (GroupID, StartID, EndID) 
 SELECT 1 as GroupID, -- replace the 1 with the correct group id
per.OwnerID,per.OwnerID
	FROM per
	Where per.GroupID IS NULL;

Now update the colour by using the same CTE

with per as
(Select
         NameTable.OwnerID AS 'OwnerID',
         NameTable.Surname COLLATE NOCASE AS 'Surname',
         NameTable.Given COLLATE NOCASE AS 'Given',
		 NameTable.BirthYear,
		 NameTable.DeathYear,
		 GroupTable.GroupID
FROM NameTable LEFT OUTER JOIN GroupTable
ON GroupTable.GroupID = 1   -- replace the 1 with the correct group id
AND NameTable.OwnerID BETWEEN GroupTable.StartID and GroupTable.EndID	
where NameTable.IsPrimary = 1 and (DeathYear > 0) and (DeathYear - BirthYear) < 14)
UPDATE PersonTable 
SET Color = 2 –- replace the 2 with the desired colour number
WHERE EXISTS (
        SELECT OwnerID
        FROM per
        WHERE per.OwnerID = PersonTable.PersonID);

I use these SQL scripts to ensure that my groups are updated when I have made changes to my databases and they ensure I don’t miss adding people who should be in the groups.

Pat Jones
SQL and Business Intelligence Developer