N.B. This is now the background page for a pair of revised scripts that supersede the conversion script here and make the process reversible. See Citations Invisible – Reveal and Revert.
First reported by Paul Harris in the RootsMagic Forums in the discussion Unused Citations after Import, we have concluded that there is a bug in RootsMagic 18.104.22.168 and, possibly, earlier versions that suppresses or fails to include a certain type of citation from appearing in key user screens and reports. Yet these citations are generated by RootsMagic, can be seen in the Source List report and survive a drag and drop transfer to a new database.
Citations, Invisible – List.sql This query will show whether there are any such ‘invisible’ citations in a RootsMagic database.
Citations, Invisible – Convert to Personals.sql This query will convert these ‘invisible’ citations to a type that is visible in the RootsMagic program.
The ‘invisible’ citations covered by these queries are for the Primary Name of a person and are converted to a Personal citation to become visible. We think an Ancestry citation that “provides evidence for Name” is exported as a citation for the “Preferred” Name, when there are Alternate Names, not for the Person. If there are no Alternate Names, we surmise that Ancestry exports the citation against the Person (top level in GEDCOM, Name is lower); these are visible. Citations for Alternate Names are visible and correctly placed in the Edit Person screen. Since there is no other place for a Primary Name citation to go than beside the name at the top of the Edit Person list and since that is where Personal citations do get flagged, then it is reasonable to convert the ‘invisible’ citations to Personal ones.
On import, RootsMagic assigns an OwnerType = 7 in the CitationTable for citations for Names, be they Preferred or Alternate, signifying that the corresponding CitationTable.OwnerID points to a unique NameID in the NameTable. Each unique NameID has a set of names for a Person in the PersonTable whose unique PersonID corresponds to the NameTable.OwnerID. For a given NameTable.OwnerID (or Person), there should be but one record with the IsPrimary column set to 1 – this is the Primary Name; all other records in the set have IsPrimary = 0, signifying they are Alternate Names. The List query lists all citations of Type 7 that point to a Primary Name.
For Personal citations, OwnerType = 0 in the CitationTable, signalling to RootsMagic to match the CitationTable.OwnerID with PersonTable.PersonID and that this is a Personal citation.
The Convert to Personals query replaces the CitationTable.OwnerID with the PersonID owning the NameID pointed to by the citation and sets OwnerType to 0 for only those citations for a Primary Name (OwnerType = 7, linked NameTable.IsPrimary = 1 or True).