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:
| Colour ID | Colour |
| 1 | red |
| 2 | lime |
| 3 | blue |
| 4 | fuschia |
| 5 | yellow |
| 6 | aqua |
| 7 | silver |
| 8 | maroon |
| 9 | green |
| 10 | navy |
| 11 | purple |
| 12 | brown |
| 13 | teal |
| 14 | grey |
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 EndNowhich 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
