Source+Templates+-+Revision+Utilities

RootsMagic invited users to submit any issues they are having with the built in source templates as of version 6.3.0.3 in the Forum thread Sentence Templates To Be Fixed on 20 Sep 2013. Extraneous punctuation and text when one or more fields is left empty was identified as an issue and one that the RootsMagician intends to fix, at least for those templates so used more than rarely. It's not clear what other issues might be addressed but new/modified functionality was ruled out from this round.

In an attempt to identify the scope of the extraneous characters problem, a series of queries were developed that readily show the outputs for empty citations of empty sources for each of the built-in source templates. The result lends itself to editing the templates and may be helpful in identifying and resolving other issues.



rev 2013-09-25 code format="sql" -- SourceTemplateRevisionUtilities.sql /* 2013-09-24 ve3meo 2013-09-25 added ripple update of the name of the source template through to the citation comment

This series of queries is intended to facilitate review and editing of copies of the builtin source templates to remove extraneous punctuation and text when fields are left empty. The series assumes a clean database with one person RIN=1 to whom an empty citation using each source template will be linked. The series: 1. Creates a copy of each builtin source template which can then be edited in RM, name prefixed with * 2. Creates an empty source for each copy of the builtin source templates, source name = template name 3. Creates an empty citation of each source linked to a person whose RIN is 1. 4. Ripples the name of the source template through to citation comment for use in end notes.

The database can be opened by RM on the Citation Manager for Person 1. The names of the sources are ordered alphabetically to match the order in the Source Template List. Selecting a citation shows the three sentences resulting from the empty source citation. Using an external SQLite manager, the SourceTemplateTable sentence templates for Footnote, Short Footnote and Bibliography can be edited, thus saving much drilling up and down in RM. Simply selecting another citation in RM and returning refreshes the sentence generation to catch up to the changes in the template.

Of course, there may other ways of working effectively, such as two instances of RootsMagic (resident and portable) open on the common database, one in the Citation Manager, the other in the Source Templates window.

-- Create a copy of each builtin source template INSERT INTO SourceTemplateTable SELECT TemplateID + 10000 ,'*' || NAME ,Description ,Favorite ,Category ,Footnote ,ShortFootnote ,Bibliography ,FieldDefs FROM SourceTemplateTable WHERE TemplateID < 10000;

-- Create an empty source for each copy of the builtin source templates INSERT INTO SourceTable SELECT NULL AS SourceID ,NAME ,'' AS RefNumber ,'' AS ActualText ,'' AS Comments ,0 AS IsPrimary ,TemplateID ,(       SELECT CAST(' ' AS BLOB)       ) AS Fields FROM SourceTemplateTable ST WHERE ST.TemplateID > 10000;

-- Create an empty citation for each empty source based on copies of the builtin source templates -- all these citations are to the person with RIN=1 INSERT INTO CitationTable SELECT NULL AS CitationID ,0 AS OwnerType ,SourceID ,1 AS OwnerID ,'user:ve3meo' AS Quality ,0 AS IsPrimary ,'' AS Comments ,'' AS ActualText ,'' AS RefNumber ,0 AS Flags ,(       SELECT CAST(' ' AS BLOB)       ) AS Fields FROM SourceTable S WHERE S.TemplateID > 10000 ORDER BY S.NAME;

/* Ripple SourceTemplate name through to Citation comments via Source comments for listing in end notes. Having the name in Source comments may also be convenient. UPDATE SourceTable SET Comments = (       SELECT ST.NAME        FROM SourceTemplateTable ST        WHERE SourceTable.TemplateID = ST.TemplateID        );

UPDATE CitationTable SET Comments = CAST(x '0A' AS TEXT) || '{' || (       SELECT NAME        FROM SourceTable S        WHERE CitationTable.SourceID = S.SourceID        ) || '}'; code