Colorcoding #RM8

RM8 Color Picker Chart with Names & Codes

Colors available in RootsMagic 8 color coding. labelled with names and database code numbers.

The above chart is an adaptation of a file posted by Lynn Christiansen to facebook Dec 31, 2021. He took a screenshot of the RM8 Color Coding window color picker and labelled each patch with the name. I've added the Color Number to each corresponding to the value the program assigns to the Color field for a row in the PersonTable.

RM8 Colors Sorted by Name and Code

The following table images provide ready lookup of color samples by the Color Code number used in the RM8 database and by the label in the RM8 Color Coding color picker.

RM8 Colors sorted by Color number
RM8 Color Coding colors sorted by name

RM8 Color 'Families'

Starting with the diagonality of the RM8 Color Picker chart for the gray colours (Blank or no colour, Silver, Grey, Slate) there appears to be more or less parallel diagonalities which we might call colour families, naming them by the highest member on the chart. For example, the Mauve family comprises also Purple and Aubergine; the Aqua family includes Azure, Blue, and Navy. In the following overlay of the Color Picker, each member of a family is framed by the colour of the uppermost member and has a line connecting its centre with the other members. Hope that's clear!

Families of RM8 Colors

RM8 Color Selection Strategies

How colours will appear in isolation or in juxtaposition with other colours can be tricky. Whether it is text or filled cell or both that is coloured is another variable. Saturation and brightness contrasts are factors to consider, especially for text on a white background and especially when printed in grayscale.

Text

The following images are from a screen capture of a PDF saved from the RM8 Individual List report. The database contains only 'people' with the names of the colours from the RM8 Color Picker in their Surname and the database color number for the Given. The RIN corresponds with the color number except for Color 0 (Blank fill or Black text) which is RIN 28. The grayscale image was derived by a conversion of the colour screenshot to "black & white" in Paint.net.

RM8 Color Coded text on white background.
RM8 Color Coded text in grayscale
  • avoid most of  the top two rows in the Colour Picker for coloured text on white or light backgrounds; Blank (black text) and Red are good.

Fill

The following two images are from a screen capture of an Excel spreadsheet which computes the approximate brightness of a color's RGB values using the formula:
brightness = (red / 255.0) * 0.3 + (green / 255.0) * 0.59 + (blue / 255.0) * 0.11

Perceived brightness will vary with monitor, printer and viewer and, in the case of conversion to grayscale, the conversion software. The grayscale image was converted from the colour image by Paint.net's "black & white" Adjustment tool which may calculate brightness with a different algorithm. If you perceive some colour's grayscale to be out of order, it may be due to any one or any combination of these variables.

RM8 Color Codes by approx brightness
RM8 Color Codes by brightness - grayscale

Juxtapositions

  • because some adjacent colors in the picker are hard to distinguish (e.g., Lemon-Chartreuse; Mint-Aqua; Maroon-Brown; Olive-Forest-Teal) do not follow a horizontal pattern through the Color-Picker for adjacent generations. However, it might work for a couple...
  • the diagonals (down 1, right 2 like a knoght's move in chess) shown through each colour family could work well for juxtaposed colours
  • the opposing diagonal would contrast well (e.g., Aqua-Orange-Blue-Khaki) (down 1, left 2)
  • diagonals of -45 degrees or down-right are a bit weak around Chartreuse-Yellow and Lime-Green; diagonals of -135 degrees or down-left avoid those issues
  • vertical trails through the colour picker seem to be distinctive, e.g., start at the top of a column, working down to the bottom and then to the top of the next column.

Migration of RM4-7 Color Codes to RM8

RM8 expanded the number of color selections to 28 (codes 0-27) from 15 (codes 0-14) in earlier versions. RM7 databases imported into RM8 retain their original color code numbers but the expanded range results in some name changes and color shifts in order to accommodate the extra colors. For example, Fuschia (4) is no more and is replaced by Pink. The following table compares the colors from RM7 with those in RM8 having the same code number. Note that the switch from coloring text in RM7 to a colored block in RM8 certainly necessitated that code 0 change from black to blank (or no color).

RM7 colors and the RM8 colors having the same code number.

Naming Citations in Preparation for RM8 #RM7 #Citations

In RM8, citations are re-useable and have a name which is made by concatenating the contents of the citation fields.
So I run this script on my RM version 7 database so that it is ready for importing into RM version 8.

I have taken advantage of this concatenation to get meaningful names by inserting a new field in my own templates as the first one in a citation and then setting the value in the citations to the source name.

This will only work for user templates, not for supplied ones (SourceTable.TemplateID > 10000)

/* update citations for RM8
 add in new field - CitItemD for citation name and put in source name - creates good citation name in RM8
*/
UPDATE SourceTemplateTable
SET (FieldDefs) = cast(Replace(cast(FieldDefs as Text),'<Root><Fields>','<Root><Fields><Field><FieldName>CitItemD</FieldName><DisplayName>Item Name</DisplayName><Type>Text</Type><Hint>Item Name</Hint><LongHint>To distinguish citations</LongHint><CitationField>True</CitationField></Field>') as blob)
WHERE 
TemplateID > 10000;

-- or this could be TemplateID = a specific template number to just change one template

-- add extra field for name - at the start so it is used for citation name when converting to v8
UPDATE CitationTable 
SET (Fields) = (SELECT cast(Replace(cast(CitationTable.Fields as Text),'<Root><Fields>','<Root><Fields><Field><Name>CitItemD</Name><Value>' || SourceTable.Name ||  '</Value></Field>') as blob) 
                                FROM  SourceTable 
                                WHERE SourceTable.SourceID = CitationTable.SourceID )
WHERE
    EXISTS (
       SELECT *
       FROM SourceTable 
        WHERE (SourceTable.SourceID = CitationTable.SourceID) 
AND SourceTable.TemplateID > 10000
-- or this could be TemplateID = a specific template number, to just change one template
   );

Citation ‘Uses’ and Duplicates #rm8 #citations #duplicates

RootsMagic 8 introduced indirection between citation data (text fields) and the fact to which a citation is attached, In RM7, the citation data and linkage was combined in the CitationTable. To use the same citation data for more than one fact meant that it had to be replicated in another record, making revisions to the data repetitious and onerous. RM8 adds the CitationLinkTable which allows one set of citation data to be linked to or ‘used’ by multiple facts. The one set of citation data then becomes a ‘master’ citation whose edits then carry through to all ‘uses’ of it, just as revisions to a Master Source affect all citations of it in all versions of RM. This is an example of improved database normalisation.

Concomitant with this change is an added feature “Merge All Duplicate Citations” whose effect is to replace all records in the CitationTable having the same Master Source and matching text fields (Citation Details) with one record linked via the CitationLinkTable to all the facts that its duplicates were previously attached. RM7 has no equivalent.

However, as of RM8 Preview 7.9.310, “Merge All Duplicate Citations” can result in duplicate ‘uses’ of the merged or master citation where there were previously duplicate but independent citations linked to the same fact. Outputs, such as reports, will still show duplicate footnotes for the fact. In this case, “Merge All Duplicate Citations” fails to deliver the desired (and logically expected) result. The problem was raised and illustrated with screenshots in a 2021-07-19 post by Tom Holden to the RootsMagic 8 Community Preview Users facebook Group.

At the time of writing, there is no provision in RM8 to automatically or manually delete duplicate uses of citation. The following SQL script does the job and shows that it should be a pretty trivial addition to “Merge All Duplicate Citations” requiring no change to the User Interface.

Problems with Email Distribution #admin

Please excuse the recent behaviour of this WordPress site. You have likely heard nothing by email from it since March 2 until yesterday and then some of you may have received flurries of duplicate messages. New subscribers probably received no confirmation email nor password reset notices. This post may not be delivered to subscribers. I’m working on correcting the problem but there seem to be multiple issues and it is proving difficult to isolate the causes:

  • starting March 2, every notice failed with an error “Could not instantiate mail function”
    • appears to have been resolved, more or less, by adding a second MX record and revising the priority of the first in the sqlitetoolsforrootsmagic.com domain – that just seems weird
  • resending notices from the WP Mail Logging plugin has revealed a bug causing them to multiply; the plugin is no longer actively supported
  • resending notices such as comments on blog posts fail because the address (From:) wordpress@sqlitetoolsforrootsmagic.com is “invalid”, despite being valid until March 2; must be an issue with the Hostinger mail server. Resending notices from the Forum uses a different, valid (From:) address (but get multiplied)
  • phpmail() function is said to be unreliable; Hostinger is recommending the use of a SMTP plugin which I am looking into.

I may lose the log of blocked notices or never get to resending ones that should have gone to you, such as password resets.

Understanding the RootsMagic 8 database – Type Decodes #rm8 #datadefinitions

Many tables within RootsMagic have fields which are know to mean something but do not have lookup tables. So far I have collected the following: (more to follow)

Fields named OwnerType – see Understanding the RootsMagic 8 database – Ownership

Addresses

AddressType field

ValueMeaning
0Correspondence
1Repository

Citations

The Quality field in the Citation Link table is three characters, each character having meanings.

Character PositionValueMeaning
1PPrimary Information Quality
1SSecondary Information Quality
1~Unknown Information Quality
2DDirect Evidence Quality
2IIndirect Evidence Quality
2NNegative Evidence Quality
2~Unknown Evidence Quality
3OOriginal Source Quality
3XDerived Source Quality
3~Unknown Source Quality

Multimedia

Field MediaType defines the media format of the file

ValueMeaning
1Image
2File
3Sound
4Video

Names

The NameType field gives the type of the name

ValueMeaning
0undefined/any
1aka
2birth
3immigrant
4maiden
5married
6nickname
7other spelling

Places

PlaceType field

ValueMeaning
0Place
1LDS Temples (Built In)
2PlaceDetail

Tags

TagType field defines what is being tagged. This equates to the label table in RM7

ValueMeaning
0Group
1Task Folder

Tasks

TaskType field – this has changed from RM7

ValueMeaning
1Research Log/ Task Folder
2ToDo
3Correspondence

TaskStatus field – some changes in RM8

ValueMeaning
0New
1Open
2Completed
3Pending – On Hold
4Problem
5Cancelled

Understanding the RootsMagic 8 database – Ownership #database #datadefinitions #rm8

The RootsMagic 8 database structure has seen a few changes from RM7 and one of these is that more objects can “own” other objects in the data. A task can have media, citations and web tags for example.

To achieve this many tables have an OwnerType field and an OwnerID field and the owner type is consistent so for example 0 in owner type always means the owner is a person and the owner id is the person record number (RIN).

In order to write code against this structure I have produced the following chart where items in the left hand column can be the owner and the items across the top are owned.

Owner Type ValueOwner TypeCan Own URLCan Own PlaceCan Own Place DetailCan Own MediaCan Own TaskCan Own AddressCan Own CitationCan Own NameCan Own ChildCan Own Event
0PersonY  YYYYY Y
1Family   YYYY YY
2Event YYYY Y  
3SourceY  Y YY  
4CitationY  Y     
5PlaceY YYY    
6TaskY  Y  YY  
7Name   YY Y  
8not used (was General task in RM7 with 0 ownerid)         
14Place DetailY  YY    
15not used (was Research Item in RM7)         
18Task Folder (update of Research Log)    Y    
Ownership table – RM8

As a further note:
When it says “Tags” in RM8 application it refers to Owner Links which can be edited at this point in the program

When it says “Used” in RM8 application it refers to Owner Links but they are not editable at this point in the program

When it says “Web Tags” in RM8 application it refers to URL

Database Diagram for RM8

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:

Colour IDColour
1red
2lime
3blue
4fuschia
5yellow
6aqua
7silver
8maroon
9green
10navy
11purple
12brown
13teal
14grey

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

Common Table Expressions – The Building Blocks of SQL

Common Table Expressions (CTE) are a feature that was first introduced to SQL in about 2005 and provide a method of defining a temporary result set then using that in the query as if it were another table.

Yes, subqueries do the same thing but a CTE can be used repeatedly in the query after it has been declared without further re-declaration whereas a sub-query has to be declared every time it is used. Views could also be created but that involves a physical object within the database being created and remembering to tidy up and delete it later.

The CTE is declared at the start of a query using the WITH construct so I can write:

WITH pers AS
(
Select
         NameTable.OwnerID AS RIN1,
         NameTable.Surname COLLATE NOCASE AS Surname,
         NameTable.Given  COLLATE NOCASE AS Given
FROM NameTable		 
where NameTable.IsPrimary = 1
)

The very first word of the first CTE has to be WITH and this has to be the very start of the query, apart from comments. Then the name of the CTE is declared folowed by AS then its definition in brackets. CTE names are best kept short as they will be used further on in the query. Now I have an item I can use in the rest of my query so that continues:

SELECT 
EventID, 
EventType,
pers.RIN1,
pers.Surname,
pers.Given
FROM EventTable as ev
LEFT OUTER JOIN pers ON
ev.OwnerID = pers.RIN1 
AND ev.OwnerType = 0

so you can see pers is treated as if it were another table or view in the query.

This is just a simple example using a CTE once. A query can contain multiple CTE and once the first has been declared, subsequent CTE are declared by continuing the WITH clause and putting
, ctename AS
(
my cte code
)
rather than another WITH. So I can add another CTE to get both parent details for a family and this can (but does not have to) use the first CTE.

,fam as
(
select FamilyID, RIN1, Surname, Given
from FamilyTable f inner join pers
on f.FatherID = pers.RIN1
where f.FatherID > 0
UNION
select FamilyID, RIN1, Surname, Given
from FamilyTable f inner join pers
on f.MotherID = pers.RIN1
where f.MotherID > 0
)

All this happens before the main query and the main query simply uses those CTE as if they were tables.
So main query could now be

SELECT 
EventID, 
EventType,
f.Name COLLATE NOCASE AS FactType,
ev.OwnerType,
ev.OwnerTypeN, 
case when ev.OwnerType = 1 THEN fam.Surname
	ELSE pers.Surname
	END as Surname,
case when ev.OwnerType = 1 THEN fam.Given
	ELSE pers.Given
	END as Given,
case when ev.OwnerType = 1 THEN fam.RIN1
	ELSE pers.RIN1
	END as RIN1,
ev.Eventdate,
ev.proof,
ev.placeID,
ev.SiteID, 
ev.details
from ev /* join in person where owner type is person */
INNER JOIN FactTypeTable f ON
         e.EventType = f.FactTypeID
LEFT OUTER JOIN pers ON
         ev.OwnerID = pers.RIN1 and ev.OwnerType = 0
/* join in the family persons for owner type = family */
LEFT OUTER JOIN fam ON
         ev.OwnerID = fam.FamilyID and ev.OwnerType = 1
WHERE    ev.EventType <> 35 /* ignore reference numbers */
/* and (ev.Proof = 0) – not proven (remove surrounding comment markers /* and */ to include this restriction)*/
ORDER BY Surname, given, RIN1, eventtype

The overall layout is
WITH a AS (something) , b AS (something else) , c AS (something more) SELECT ……….

Note the left outer join syntax which allows all records from the left hand table to be included even if there is not a matching record on the right hand side. Missing data is set to null values. Inner JOIN and just plain JOIN would leave out records which do not have a match for the person id and an owner type of 0 and in fact the whole query would just mess up.

A good rule for SQL readability is to always fully specify the join type so never use just JOIN but always INNER JOIN or LEFT OUTER JOIN.
There is also a RIGHT OUTER JOIN and a FULL OUTER JOIN but SQLite does not support these constructs.

Another useful implementation of the CTE is to expand out tables in RM that contain dates and fields that need collation changes. The date expansion code is longwinded and distracting from the main query. Putting it in a CTE gets it out of the way, leaving the main query readable.

My full query example attached is a list of facts and their citations, including the owner (person or family). By altering the where clause this can list facts without citations or facts not marked proven (or both).

With the database changes between RM7 and RM8 the CTE is most useful to convert queries written for version 7 to run against a version 8 database. The CitationTable from version 7 has been split into CitationTable and CitationLinkTable in version 8 but using a CTE we can emulate the version 7 structure then just change the table name in the main query.

So we have:

With Cit8 as
(Select c.CitationID, cl.OwnerType, cl.OwnerID, c.SourceID, cl.Quality, cl.IsPrivate, c.Comments, c.ActualText, c.RefNumber, cl.Flags, c.Fields
from CitationTable c INNER JOIN CitationLinkTable cl
ON c.CitationID = cl.CitationID)

then continue with original query but change all occurrences of CitationTable to Cit8
So a simple list of citations with their sources becomes:

With Cit8 as
(Select c.CitationID, cl.OwnerType, cl.OwnerID, c.SourceID, cl.Quality, cl.IsPrivate, c.Comments, c.ActualText, c.RefNumber, cl.Flags, c.Fields

from CitationTable c INNER JOIN CitationLinkTable cl
ON c.CitationID = cl.CitationID)
SELECT * FROM Cit8 LEFT OUTER JOIN SourceTable ON CitationTable.SourceID = SourceTable.SourceID

I have built queries with many CTE to progressively build up data for complex reporting and find it a far more efficient way of coding which can be easily tested level by level.

My query to list facts and their citations is attached in two versions, cte-facts-citations-RM7.sql for RootsMagic version 7 databases
and cte-facts-citations-RM8.sql for RootsMagic version 8 databases.

Further Reading:

This article is aimed at SQL Server but SQLite uses the same syntax.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-cte-basics/

Pat Jones
SQL and Business Intelligence Developer