Source Templates – Convert Builtins to Editable, Split, Import #sourcetemplates

Posted on 2017-04-192022-04-29 by Tom Holden

Prior to RootsMagic 7’s new Import Lists feature, there was no way from within the program to import custom Source Templates from one database to another except through a laborious, one-at-a-time export to a .rmst file which you would then import, one-at-a-time. Or, alternatively, you would create a source against each custom template for a dummy person whom you would then drag’n’drop to the target database – a not very intuitive procedure. The 413 built-in templates are uneditable so you would have to copy them one at a time to make them editable. A workaround was developed that stored all the built in template definitions in one RootsMagicSTuser.rmst file which, when imported, created user-editable copies in the target database. Because this is a plain text XML file, revisions could be made with a text editor, especially with one intended for XML editing. Thus a user could maintain a master custom template .rmst file for import into new databases.

Import Lists made the transfer of custom source templates much easier by skipping the singular .rmst export/import process. Now it is possible to maintain a database of custom source templates, even Master Sources, and import them directly into another database. This page presents:
1. A script that creates editable copies of the 413 built-in source templates in any RootsMagic database:
SourceTemplates_CopyBuiltin2Editable.sql

2. A new database to which the script has been applied, for those who do not want to use SQLite:
SourceTemplatesEditable-normal-2017-04-19.rmgb

3. A script that converts editable (custom) source templates into ultra-split templates (no fields in Source Details):
SourceTemplateExtremeSplitConvert.sql N.B.: WARNING – use this on unused templates; it does not convert existing citations and master sources. For scripts that convert existing sources and the templates they use to ultra-split, see Sources – Adventures in Extreme Splitting.

4. The database from #2 to which the ultra-splitter has been applied:
SourceTemplatesEditable-ultrasplit-2017-04-19.rmgb

Builtin source templates converted to user-editable templates with splitsbetween Master Source level and Citation or Source Details level unchanged.

All of the standard source templates are copied and fully customisable.
Use this file as a starting point for a new database

OR,

on an existing database,

use File > Import Lists > Source Templates to import the user-editable source templates

compose a draft source in it using a custom template and copy the resulting sentences for Footnote, Short Footnote and Bibliography to a Free Form source in your working database.
These user-editable copies are split at the same level as the built-in ones.

Builtin source templates converted to user-editable and ultra-split.

All of the standard source templates are copied and fully customisable. These user-editable copies have all fields in the Master Source and none in Source Details, corresponding to ultimate splitting of sources, a surefire way for footnotes to migrate unsullied through GEDCOM and transfer to FamilySearch Family Tree (and, very likely, through the pending TreeShare with Ancestry).
Use this file as a starting point for a new database

OR,

on an existing database,

use File > Import Lists > Source Templates to import the user-editable source templates

compose a draft source in it using a custom template and copy the resulting sentences for Footnote, Short Footnote and Bibliography to a Free Form source in your working database.

Sources – Adventures in Extreme Splitting #sources #sourcetemplates #replace

Posted on 2017-04-192021-02-26 by Tom Holden

This page brings to bear some SQLite tools in support of Jerry Bryan’s discussion in the RootsMagic forums titled Adventures in Extreme Splitting. In summary, the concept is that every unique citation is a Master Source, i.e., all data fields are to be found in the Master Source, none under Source Details. Jerry’s observations are that RootsMagic behaves better as a result in these respects:

templated sources export to standard GEDCOM and thus to third-party software and websites with better integrity. See Ultimate Splitting of Sources for Better GEDCOM from RootsMagic (PDF)
sources transfer more completely to FamilySearch Family Tree (RM does not transfer Research Notes and Detail WebTags, only Source Text and first Master WebTag so this procedure moves Research Notes and Detail Web Tags to the Master Source fields)
easy duplication
one page to edit all citations of a source instead of one page per citation, if the change would otherwise be at the conventional Source Details level.
one set of images per source which covers all citations of the source
a complete source can be created in the Source Manager

A disadvantage is that the Bibliography may be verbose, becoming just an alphabetical sort of the complete endnotes, without duplicates and there is little opportunity to benefit from succinct Short Footnotes.
[Inline comment:

external image user_none_lg.jpg ve3meo Jun 18, 2013

This bears some scrutiny. True if the Bibliography sentence is the same as the footnote but not so if the Bibliography sentence template uses a small subset of the source fields as do many of the built-in templates.
]

Convert Existing Sources and Citations

This query converts all sources and citations and the source templates they use to extremely split sources. It’s a work in progress – feedback invited. The current version converts lumped source templates, sources and citations to extremely split duplicates for review against the original “lumpy” sources. The new split source names are prepended with the carat character (^) to the original source name and appended to it are the name of the person to whom the citation belongs followed by the fact type in parentheses and, conditionally another name for family and alternate name facts. Images, WebTags, Repositories et al are now remapped. This is highly experimental so, of course, you will not want to run it against your working database without the appropriate precautions!!! Try it and see what you get.

May be advisable to run Delete Phantoms and Source Templates – Merge Duplicates first.
Sources-CreateExtremelySplitFromCitations.sql Stop here to review the new “ultimate split” sources and citations against the originals. rev 7 Oct 2014 now handles a Source having no Master Source fields defined in its Source Template (caused a RootsMagic error)
Sources-DeleteAfterSplit.sql 30 Jun 2013. Delete the “lumpy” stuff.
Deletion of unused custom source templates yet to be done; alternatively run the Conversion of Templates procedure below on custom templates so that they are ready to be used as Ultimately Split.
Delete Phantoms: probably advisable to run this after the split.
After the deletion, use RootsMagic Database Tools to test integrity, rebuild indexes if integrity not OK, and compact to recover space.

One problem I have seen with the results is that the differentiation of the Master Source Name due to the appending of the Person Name and Fact Type prevents the Source Manager’s AutoMerge from merging otherwise identical Master Sources. Manual merging is way too laborious so until RootsMagic gives AutoMerge options, another outboard utility is needed. Stay tuned for Sources – Merge Duplicate Masters.

Conversion of Templates

A Free Form template is readily used for extreme splitting as there is but one Source Details field, [Page], which can be left empty and the Master Source fields may be filled arbitrarily with text. There are several built-in templates which are already setup for extreme splitting as they have no Source Details fields:
Artifact, archived
Artifact, Family, photographed (privately held)
Artifact, privately held
Bible Records
Broadside, original
Broadside, reprint
Family Bible Record
Genetic Test (DNA) report
Legal Document, unrecorded (family copy)
Letter, Historic, private
Maps, Historic
Maps, Topographic
Military, Muster Rolls (manuscript)
Photo, Portrait, Archived (Annotated)
Photo, Portrait, Private (Annotated with Provenance)
Photo, Portrait, Private, scanned
School Records (student transcripts)
Slip Laws, Federal
Slip Laws, state
The remaining ~400 templates have Source Details fields that preclude them from being used for extreme splitting unless modified.

The following query moves all Source Details fields into the Master Source for all sources, thus enabling them to be used for extreme splitting.

-- SourceTemplateExtremeSplitConvert.sql
/*
2013-06-18 Tom Holden ve3meo
Converts all fields in the Source Template defined as Source Details or Citation Details
to Master Source fields, thus enabling the template to be used for Extreme Spliting.
 
WARNING - use this on unused templates; it does not convert existing citations and master sources.
*/
UPDATE SourceTemplateTable
SET FieldDefs = CAST(REPLACE(CAST(FieldDefs AS TEXT), '<CitationField>True</CitationField>', '<CitationField>False</CitationField>') AS BLOB)
WHERE 1;-- Apply constraints here, otherwise all Source Templates are modified.
 
-- View FieldDefs
SELECT CAST(FieldDefs AS TEXT)
FROM SourceTemplateTable T;-- view results

As it stands, this query modifies all the built-in templates, which is fine for experimentation but to be avoided in practice. Better to create editable templates first and then run this query with WHERE 1 changed to WHERE TemplateID > 9999. Refer to Source Templates for ways to batch import or convert to editable source templates.

Sources without Media

Needing to find Master Sources lacking media and to move media from Source Details to Master Source as part of his conversion to Extreme Splitting, Jerry posted Query for Sources Without Media.

Inline comments

Comment: This bears some scrutiny. True if the…

ve3meo
18 June 2013 20:21:51

This bears some scrutiny. True if the Bibliography sentence is the same as the footnote but not so if the Bibliography sentence template uses a small subset of the source fields as do many of the built-in templates.

REMATCH to FamilySearch ID in REFN fact #FamilySearch #refno

Posted on 2017-04-062022-04-29 by Tom Holden

An earlier script copied the FamilySearch Family Tree ID into the Reference Number fact. One user uploaded a file to an Ancestry Member Tree where it was grown through online evidence-based research and then downloaded the GEDCOM back into RootsMagic. RootsMagic’s Auto-Match feature in FamilySearch Central failed to rematch all the previously matched people. However, the FSID was preserved in the REFN fact through this round trip so his wish was to regenerate the matches from the REFN value. This script does just that.

The script is written as the complement to the one at COPY familySearchID to REFN fact , that is, the REFN value is of the form “fsid: XXXX-XXX”. If your format is different, it will have to be revised.

At time of writing, we are perhaps just weeks away from the Ancestry TreeShare update to RootsMagic 7 which may result in a change to the LinkTable that could cause the script to be rendered invalid without being adapted. However, it also follows that there should also be even less reason for someone to move their database from RM through Ancestry and out again with the attendant loss of FS matches.

REFN-RematchFSID.sql

RM’s FamilySearch Central will report there is “new information” for every one of the matches remade by the script.

The rematches in the LinkTable made by the script are like records 1 and 3 with the extVersion empty. After opening the FamilySearch Person Tools on one of the people with “new information”, the record is revised as per #2.

Sources – Unreverse Author Names #sources #tmg

Posted on 2017-03-302019-01-27 by Tom Holden

To “Unreverse” something is to restore it from a reversed state. Names inadvertently entered into a name type field in a templated source in “reverse” order, i.e., surname, given are not properly handled by RootsMagic 7 and earlier. The resulting sentences for the Footnote, Short Footnote and Bibliography are screwed up. RootsMagic wants entries for name type fields to be in normal or forward order, i.e., given surname and without commas. This would not be a problem for one at a time source creation because it will be readily detected in the sentence previews and can be corrected on the spot. However, it is a much bigger problem if that is the way that many sources were imported, as is the case for direct imports from TMG.

This script unreverses reverse names found in the Author field of templated sources. There may be other name-type fields used in templated sources, e.g., Editor, Compiler, ItemAuthor, Family, Owner, AgencyAuthor, ChapAuthor, BookAuthor, SuplAuthor, …. The script can be readily modified for each one of these.

It reverses correctly those entries having a single name with one comma. It will screw up entries having multiple persons in the one field.

On executing the script, the status display will show that a number of records have been updated. All of the temporary VIEWs that the script creates will be empty and can be manually DROPped and will be gone anyway when the SQLite manager closes the database.

By partially executing the script up but not including the UPDATE statement, the SourceFieldsNew VIEW will show the original SourceTable.Fields value, the extracted name and the unreversed name.

Sources-AuthorUnreverse.sql

Source List Query #sources #sourcetemplates #citations #xml #date

Posted on 2017-03-212025-02-13 by Tom Holden

RM8 version is compatible with #RM10

Description

Here are the most advanced and useful SQLite queries for reviewing citations in your RootsMagic 4-8 database. They provide some of the same info that you would get in the RootsMagic Source List report and then some you don’t… You have the advantage of sorting and filtering results in ways you cannot within RM and then finding the person and fact using that source in RM. And they provide the key ID’s with which you can find specific table records and possibly edit fields directly using SQLite (if you know what you are doing). What is yet missing is expansion of the data into sentences using the sentence templates; this requires a high level programming language and is unlikely to be added.

Two Queries

Two separate queries are needed because basic SQLite managers can only present results in tabular format and screen width is insufficient to show all the fields that make up a fully detailed RM4 Source List report. The two queries provide, respectively,:

List of all the citations (relatively many) with details against the names of the Master Sources (RM4-8 as of 2022-02-21)
List of all the Master Sources (relatively few) with their details but not the Source Details of each citation (RM4-8 as of 2021-03-03)

Using a SQLite manager that supports two or more queries in separate windows or tabs (e.g. SQLiteSpy), it is easy to have both queries’ results available by toggling between windows. To view the properties of media items, use the Media List Query in a third window.

1. Source List – Citations

Download SourceList.sql for RM4 to #RM7
2011-11-06 now reports Free Form and orphaned citations; count of media items linked to citation; improved format of CitFields; compatability with managers other than SQLiteSpy.
2011-11-18 now outputs Source Fields as per MasterSources.sql plus Event Place and Event Site (Place Detail)

Download SourceList-RM8.sql #rm8 version of SourceList.sql 2022-02-21

This complex query was built on Romer’s much earlier Source Template List – Query. In so doing, I discovered errors and inconsistencies in and among it and my All Citations – Query and All Citations & Dupes Count – Query. Hopefully, these are now resolved with revisions to all of them. Major additions include date decoding from Date Decoder and the stripping of XML tags from the FIELDS field pioneered in Source Detail View (Parsing XML). The result is a very comprehensive and readable spreadsheet. Here are two examples of output displays from two different SQLite managers, a different set of columns selected for display:

From the latest version of the query, as displayed by SQLite Expert. Note the wrapping of text.

SQLite Expert requires that you comment out the ORDER BY clause in order to sort on a results column; it adds an ORDER BY clause and re-runs the query. It has excellent filtering tools but one can add one’s own WHERE clause to filter results.

Screenshot of results of the query from SQLiteSpy

At the bottom of the screen, you see the content of the highlighted cell. Clicking on any column heading will sort on that one column. The order displayed in this screenshot is defined in the query which can be readily changed to suit your particular requirements. This one is sorted on Source Name, Cit Text, Cit Comment, Person 1, Cited by, EventDate, Person 2 and would be useful for reviewing citations of a source having identical texts.

Column Definitions

Column	Description	Editable
Source Name	name of the Master Source; primary sort field in this query	N
Source Fields	the field names and values entered in the Master Source	Y
Cit Fields	the field names and values entered in the Source Details screen for a citation	Y
Qual	citation quality code (decoding would take up too much screen space but you’ll get the drift)	Y
Cit Text	Source Details text	Y
Cit Comment	Source Details comment	Y
Cited by	person, family or fact type that cited the source	N
EventDate	fact/event date (helps to pick out in the Edit Person screen which of two or more facts of the same type cited the source)	N
Event Place	Place of the fact/event	N
Event Site	Place Detail of the fact/event	N
RIN 1	record number of person whose fact cited the source	N
Person 1	name of person whose fact cited the source	N
MRIN	FamilyID of FamilyTable, the Marriage Record Number that is invisible in RM4	N
RIN 2	record number of spouse	N
Person 2	name of spouse	N
CitID	CitationID of CitationTable	N
SrcID	SourceID of SourceTable	N
Template	name of Source Template	N
TpltID	TemplateID of SourceTemplateTable	N
Tplt Type	“OEM”=Built-in Source Template (TemplateID<10000); “USR”=user-defined Source Template (TemplateID>9999)	N

2. Master Sources

Download MasterSources.sql #rm7
2011-11-06 now reports Free Form and orphaned citations; count of media items linked to master source; improved format of SrcFields

2017-03-21 show unused Master Sources and correct citation count (was offset by 1)

2020-06-21 the offset ‘corrected’ above was for unused Master Sources and threw all others off by 1; this rev works for both used and unused Master Sources.

Download MasterSources-RM8.sql #rm8
2021-03-03 Converted to work with both RM7 and RM8 format – PJ Feb2021

Column Definitions

Column	Description	Editable
SrcID	SourceID of SourceTable	N
Source Name	name of the Master Source; primary sort field in this query	N
RefNumber	Master Source File #	Y
SrcFields	the field names and values entered in the Master Source screen	Y
ActualText	Master Source text	Y
Comments	Master Source comments	Y
IsPrivate	1 if Master Source marked private, else 0 but currently unused (?)	Y
Citations	number of times Master Source is cited	N
Template	name of Source Template	N
TpltID	TemplateID of SourceTemplateTable	N

Discussions & comments from Wikispaces site

Places – Delete Unused #places #placedetails #delete #phantom

Posted on 2017-02-272020-03-03 by Tom Holden

RootsMagic 7 is very cautious about deleting Places and Place Details, allowing only one at a time and requiring user confirmation of the “Are you sure?” type. This script is the absolute opposite and deletes all unused Places without hesitation. Were you to transfer your database by drag’n’drop or via GEDCOM to a new database, unused Places (and other unused items) would not be included so what’s the big deal? See if you are comfortable with GEDCOM & DnD transfer losses. Deletion of unused Places in RootsMagic needs empowerment.

This script deletes all those Places and Place Details records from the PlaceTable if unused by any Event plus all Place Details orphaned by having no master Place. It is simply a series of statements extracted from the DeletePhantoms.sql script at the page Delete Phantoms.

After executing the script, you should run RootsMagic’s File > Database Tools to correct probable indexing errors arising from the use of the fake RMNOCASE collation.

PlacesDeleteUnused.sql

SQLite Managers for Mac OS #sqlitemanagers

Posted on 2017-02-262021-07-18 by Tom Holden

There is growing interest in the RootsMagic user community in being able to run SQLite Tools for RootsMagic scripts under the Apple MacIntosh operating system. If RootsMagic Inc succeeds in releasing a multi-platform version, then there will undoubtedly be a surge in the number of native-Mac OS users of RootsMagic and corresponding pursuit by some of them of a good SQLite manager. Neither of the SQLIte managers currently favoured by this wiki for the Windows OS are available for the Mac OS. Moreover, the only comprehensive list of SQLite Management Tools for various operating systems has not been updated since 2011. So this page is intended to consolidate info about potential candidates for the Mac OS to parallel that for Windows on this wiki’s page SQLite Managers.

Important Criteria

Based on the experience with SQLite script development so far for RootsMagic databases, these are factors that vary from manager to manager and would, ideally, be found in one:

low cost
extension for RMNOCASE collation
REGEXP() function for search and filtering
run-time parameters for user input (RTP)
FTS3, FTS4 (full text search)
high level scripting language (Lua, Qt, Tcl, …) for custom functions, more powerful, user-friendly utilities

Criterion	SQLiteStudio 3
Cost	free
RMNOCASE	auto substitutes NOCASE
REGEXP()	Y
RTP	Promised for v3.1
FTS	FTS3, FTS4
Scripting	Qt, SQL, Tcl custom functions

Merge Duplicate Single Parent Couples #merge #spouse #child #phantom

Posted on 2017-02-262019-01-27 by Tom Holden

The title of this page was really difficult to compose. It has to do with records in a RootsMagic database’s FamilyTable for which each record pairs the record numbers of a ‘husband’ and a ‘wife’, i.e., columns named “HusbandID” and “WifeID”. Set aside questions of gender.

When a person is linked to a single parent, a record is added to the ChildTable for that person with a pointer to a record added to the FamilyTable (FamilyID) in which one of HusbandID or WifeID is 0. It is possible for multiple children to be linked to more than one record (family) in the FamilyTable that have the same pairing of a person in the database (has a RIN) with a spouse having no RIN. In that case, the ID’d spouse will be seen to have multiple spouses and the children will be spread across multiple families.

From a user’s perspective, since nothing is known about the unidentified parent, it is desirable to combine all the children under the single known parent and to merge the duplicate unknown spouses. Consequently, the objective might be described in a few ways, different from the page title, e.g.:

Merge Unknown Spouses
Merge Single-Parent Families
Merge Duplicate Couples
…

The stimulus for this script came from the following message posted to Submit Your Problem page:

Merge unknown spouses
I have a good number of people where one of the spouses is unknown (usually the wife). When I imported to RM (from The Master Genealogist) a separate unknown spouse was assigned to each of the children. For instance, in TMG I had John Smith with 1 unknown wife and 5 children. In RM I have John Smith with 5 unknown wives, each with 1 child. I need a SQLite that will merge the children under the known spouse and combine all the unknown spouses.

The script is fairly crude and may have unexpected side effects so try it on a copy of your database and review extensively before adopting it as your new master database. One very likely effect will be incidents of the empty Family View symptom which can be fixed using the script at SpouseID Invalid.

ParentSingleMerge.sql rev 2017-02-14 corrected bug that assigned all children to 1 parent

Names – married name in death sentences #names #alternatenames #death

Posted on 2017-02-252019-01-27 by Tom Holden

RootsMagic 7 offers no means of using an Alternate Name value in its sentence template language for events for any fact type other than Alternate Name. Some users want to use a wife’s married name (husband’s surname) for her events after Marriage, especially the end-of-life events as the married name is commonly recorded or engraved on headstones. Within RootsMagic, the only workarounds are to:

customize each event sentence to incorporate the husband’s surname literally, not as a field,
or
share the event with the husband which then enables retrieval of the husband’s name in a custom default sentence.

Either way is labour intensive.

This script carries out the first workaround in a matter of seconds, applying a custom local sentence to each of the end-of-life events for a wife having a valid Marriage event. It does not touch the events of a person with no Marriage event nor one with a subsequent Annulment or Divorce event. A polygamist woman would take the name of the last groom. It ignores Alternate Names but perhaps that is a possible enhancement that would be desirable to accommodate name hybrids such as maidenname-husbandname.

Name-Married_in_DeathSentence.sql

SpouseID Invalid #spouse #error #family

Posted on 2017-02-212021-03-11 by Tom Holden

Update 2021-03-11: scripts work on #RM8.

This page stems from weird behaviour that surfaced in a database that suffered from the initial direct TMG import by RM 6.2.3 which created a single parent family for each child (see discussion – link broken in migration from Wikispaces). After running the script Merge Duplicate Single Parent Couples, inspection of the results encountered an empty Family View when going to certain persons selected in RM Explorer or navigating to them through Family View. What was then discovered was that the SpouseID in the PersonTable was invalid – there was no such couple in the FamilyTable. Setting the SpouseID to 0 cleared the empty Family View symptom.

The PersonTable.SpouseID value directs which spouse would be displayed in the Main View when one, other than some default, was selected. Its name is somewhat misleading because it contains the FamilyID in which the selected spouse is tied to the focus person, not the RIN of the spouse. In the case reported above, there were multiple FamilyIDs for a person each with a spouse of RIN 0 and all but one were deleted by the Merge process. The SpouseID could then be left pointing to a now-non-existent FamilyID, resulting in a blank or empty Family View.

Check for Invalid SpouseIDs

This script is useful for examining the behaviour of SpouseIDs as spouse selection or navigation is executed in RootsMagic.
SpouseID_Invalid-Check.sql

Reset Invalid SpouseIDs

This script resets the invalid SpouseIDs reported by the above script to 0.
SpouseID_Invalid-Reset.sql

Convert Existing Sources and Citations

Conversion of Templates

Sources without Media

Inline comments

Comment: This bears some scrutiny. True if the…

Description

Two Queries

1. Source List – Citations

Column Definitions

2. Master Sources

Column Definitions

Discussions & comments from Wikispaces site

The Queries Don’t Run on My System

Important Criteria

Check for Invalid SpouseIDs

Reset Invalid SpouseIDs