Introduction

This query introduces a radical variation on RootsMagic’s system of Source Templates. It combines Source Templates and Free Form properties into Versatile Free Form Hybrid Source Templates incorporating the best of both worlds. Sources built on older source templates will now have free form fields that can be used to create alternative sentences that will survive GEDCOM export better than the templated sentences. Sources built on Free Form are not changed but can now be converted losslessly to templated sources, the data values being transferred from the Free Form source to the Free Form-like fields in the Hybrid source. And new sources built on the Hybrid templates can begin either in Free Form or template style while the other can be created and edited from within the common Edit Source window.

This idea sprang from discussion on Jerry Bryan’s page Question with respect to Changing a Master Source to a Different Source Template.

Conversion from ordinary templates to Versatile Free Form Hybrid Source Templates

Screenshots

Source using a Hybrid built-in source template showing the default output sentences coming from the original template fields, not the added (FF) fields.

Same source but the FORCE FF field non-empty value causes the sentence outputs to come from the (FF) fields.

Restoring after Drag ‘n’ Drop to new database

Transfer of sources, based on built-in templates that have been so Hybridized, to another RM database initially appears to lose the Free Form fields. However, the data values were transferred and merely running the query on the other database will restore the transferred sources to the Hybrid state.

Conversion of Free Form sources to Hybrids

Conversion of Free Form sources to Hybrid templates is possible and not difficult. You need to replace the TemplateID value of 0 for specific sources in the SourceTable to the TemplateID value of the desired Hybrid template. The (FF) fields will be populated by the values of the Free Form source. If that template does not work out, you can readily change the source to another Hybrid template and not lose the (FF) values from which you parse values for the template fields.

Feedback

This query is EXPERIMENTAL – try it on a copy of your database. See if it works for you. Let me know what you think, what works, what doesn’t. For those who have used !MyFreeForm3, I suspect there will be complications that will be precluded in a later version – after all, I use it, but this initial version was developed on just the built-in source templates. who

Future Considerations

A later version will add the fields from !MyFreeForm3 for even better Free Form style sentences and another utility could toggle the ForceFF field to control which sentence template is used for export, i.e., Free Form for export, templated for reports and other outputs.

Download SQL Script

SourceTemplatesHybridFreeFormConvert.sql rev 2013-12-17: !MyFreeForm3 templates no longer converted; speed tweaks.

Script

--SourceTemplatesHybridFreeFormConvert.sql
/*
2013-12-16 Tom Holden ve3meo
2013-12-17 rev1. no longer converts !MyFreeForm3 templates
           rev2. possible speed up by reducing use of CAST
Marries source templates up with Free Form for the best of both worlds:
a) use the templated part as an input form to help draft a Free Form source
b) paste a citation from Ancestry, FamilySearch et al into the Free Form part to
refer to while populating the template fields.
c) toggle between sentences outputted from either the Free Form fields or the
templated fields
 
EXPERIMENTAL
The revised templates have added fields in Free Form style (FF) and a Force FF field
that determines whether the sentences outputted are from the Free Form fields or
the template fields.
 
N.B.: the built-in source templates, while converted, will not transfer
to another database. Because the target database has only the templated fields
in the template definitions, the Free Form fields will appear to be lost. Running
this utility on the target database will make them reappear with the values in
each source intact.
 
*/
 
-- backup SourceTemplateTable builtin templates (you could revert to this version
-- after executing the query the first time)
 
DROP TABLE IF EXISTS xSourceTemplateTableBak
;
CREATE TABLE IF NOT EXISTS xSourceTemplateTableBak AS
SELECT * FROM SourceTemplateTable
;
 
/*
-- Restore from backup
INSERT OR REPLACE INTO SourceTemplateTable
SELECT * FROM xSourceTemplateTableBAK
*/
 
DROP TABLE IF EXISTS xTmpUnconvertedTemplates
;
CREATE TEMP TABLE xTmpUnconvertedTemplates
AS
SELECT TemplateID , CAST(FieldDefs AS TEXT) AS FieldDefsTxt
FROM SourceTemplateTable
;
DELETE FROM xTmpUnconvertedTemplates -- delete the already Hybrid templates or !MyFreeForm3 templates
WHERE
    (
     FieldDefsTxt LIKE '%<FieldName>ForceFF</FieldName>%' -- ForceFF is added by Hybrid
     OR
     FieldDefsTxt LIKE '%<FieldName>ShortPage</FieldName>%' -- ShortPage is added by !MyFreeForm3 which does not need conversion
     )
;
 
-- Convert Source Template FieldDefs to Hybrids
UPDATE SourceTemplateTable
SET FieldDefs =
CAST(
     REPLACE(CAST(FieldDefs AS TEXT), '<Fields>', '<Fields><Field><FieldName>Footnote</FieldName><DisplayName>Footnote (FF)</DisplayName><Type>Text</Type><Hint>footnote sentence from Free Form source, without the source detail</Hint><LongHint/><CitationField>False</CitationField></Field><Field><FieldName>ShortFootnote</FieldName><DisplayName>Short Footnote (FF)</DisplayName><Type>Text</Type><Hint>short footnote sentence from Free Form source sans source detail</Hint><LongHint/><CitationField>False</CitationField></Field><Field><FieldName>Page</FieldName><DisplayName>Page (FF)</DisplayName><Type>Text</Type><Hint>page value from Free Form source, originally the source detail</Hint><LongHint/><CitationField>True</CitationField></Field><Field><FieldName>Bibliography</FieldName><DisplayName>Bibliography (FF)</DisplayName><Type>Text</Type><Hint>bibliography sentence from FF source</Hint><LongHint/><CitationField>False</CitationField></Field><Field><FieldName>ForceFF</FieldName><DisplayName>FORCE FF</DisplayName><Type>Text</Type><Hint>Any value will force Free Form (FF) sentences</Hint><LongHint>If left empty, the sentences will be generated from the template fields below this field. If non-empty, the sentences will come from the Free Form-like fields above this one or designated (FF).</LongHint><CitationField>False</CitationField></Field>')
     AS BLOB)
WHERE TemplateID IN (SELECT TemplateID FROM xTmpUnconvertedTemplates)
;
 
/*
Some templates already had a [Page] field so now they have two and one must go
The only one we can be sure of having a constant pattern is the one we just added
so we delete it.
*/
UPDATE SourceTemplateTable
SET FieldDefs =
CAST(REPLACE(CAST(FieldDefs AS TEXT)
     ,'<Field><FieldName>Page</FieldName><DisplayName>Page (FF)</DisplayName><Type>Text</Type><Hint>page value from Free Form source, originally the source detail</Hint><LongHint/><CitationField>True</CitationField></Field>'
     ,''
     )
     AS BLOB
    )
WHERE TemplateID
IN(
   SELECT TemplateID
   FROM  xTmpUnconvertedTemplates
   WHERE FieldDefsTxt LIKE '%<DisplayName>Page</DisplayName>%' -- template already having a [Page] field
   )
;
 
/*
Convert Source Template sentence templates to Hybrid
e.g. Footnote: <? [ForceFF]|[Footnote], [Page].|--originalfootnote--|[Footnote], [Page].>>
*/
UPDATE SourceTemplateTable
SET
Footnote =
REPLACE(Footnote, Footnote, '<? [ForceFF]|[Footnote], [Page].|' || Footnote || '|[Footnote], [Page].>')
,
ShortFootnote =
REPLACE(ShortFootnote, ShortFootnote, '<? [ForceFF]|[ShortFootnote], [Page].|' || ShortFootnote || '|[ShortFootnote], [Page].>')
,
Bibliography =
REPLACE(Bibliography, Bibliography, '<? [ForceFF]|[Bibliography].|' || Bibliography || '|[Bibliography].>')
WHERE TemplateID IN (SELECT TemplateID FROM xTmpUnconvertedTemplates)
;
 
/*
Done.
*/

Set Hybrids to Free Form for Export to GEDCOM

By using well both the (FF) fields and the more specific template fields for each source, you have both a quality sentence conforming to Evidence guidelines for use in reports AND a sentence that exports well to GEDCOM from the same source. It’s necessary to Force the (FF) sentence to be outputted, otherwise, the default is the source-specific template sentence. One does so by putting any value into the FORCE FF field. This query looks for any sources that have a non-empty Footnote (FF) sentence and an empty FORCE FF field and puts the value “GEDCOM” into the latter. Any sources already forced to output the Free Form sentence are untouched. Sources that have empty Free Form Footnote fields are also left unchanged.

Download

Sources-HybridFF-SetFF-Export.sql

Script

-- Sources-HybridFF-SetFF-Export.sql
/*
2013-12-17 Tom Holden ve3meo
 
Sets Hybrid Free Form Templated Sources to output the Free Form footnote,
if not already so set, using the value "GEDCOM" in the ForceFF field.
 
The complementary script sets those Hybrid templates with "GEDCOM" in the
ForceFF field to output the templated sentence, as would be desirable for all
other destinations, such as reports.
*/
 
DROP TABLE IF EXISTS xTmpHybridSourcesSetFF
;
CREATE TEMP TABLE IF NOT EXISTS xTmpHybridSourcesSetFF
AS
SELECT SourceID, CAST(FIELDS AS TEXT) AS FieldsTxt
FROM SourceTable
WHERE CAST(FIELDS AS TEXT) LIKE '%<Field><Name>ForceFF</Name><Value/></Field>%' -- empty FORCE FF field
AND CAST(FIELDS AS TEXT) LIKE '%<Field><Name>Footnote</Name><Value>%'  -- non-empty Footnote (FF) field
;
 
UPDATE OR REPLACE xTmpHybridSourcesSetFF
SET FieldsTxt =
REPLACE (
         FieldsTxt
         , '<Field><Name>ForceFF</Name><Value/></Field>'
         , '<Field><Name>ForceFF</Name><Value>GEDCOM</Value></Field>'
         )
;
 
UPDATE OR REPLACE SourceTable
SET FIELDS =
(
SELECT CAST(FieldsTxt AS BLOB) FROM xTmpHybridSourcesSetFF
 WHERE SourceTable.SourceID = xTmpHybridSourcesSetFF.SourceID
)
WHERE SourceID IN (SELECT SourceID FROM xTmpHybridSourcesSetFF)
;

Set Hybrids to Source-Specific Sentences (Unset Free Form) for Reports et al

After having run the above query to set sources to export the Free Form sentences, you will want to reset them back to outputting the source-specific or source template sentences for most other uses. This script looks for all the FORCE FF fields having the value “GEDCOM” and empties them. It won’t touch those sources that have some other value in the FORCE FF field – for example, you may have sources for which you prefer the Free Form sentences to those using the source-specific fields.

Download

Sources-HybridFF-UnSetFF.sql

Script

-- Sources-HybridFF-UnSetFF.sql
/*
2013-12-17 Tom Holden ve3meo
 
Sets Hybrid Free Form Templated Sources to output the templated or non-Free Form
sentences, if set to output Free Form by the value "GEDCOM" in the ForceFF field,
as would be desirable for all other destinations, such as reports.
 
The complementary script sets those Hybrid templates with an empty ForceFF field
to output the Free Form sentence by adding setting the ForceFF field to "GEDCOM".
*/
 
DROP TABLE IF EXISTS xTmpHybridSourcesUnSetFF
;
CREATE TEMP TABLE IF NOT EXISTS xTmpHybridSourcesUnSetFF
AS
SELECT SourceID, CAST(FIELDS AS TEXT) AS FieldsTxt
FROM SourceTable
WHERE CAST(FIELDS AS TEXT) LIKE '%<Field><Name>ForceFF</Name><Value>GEDCOM</Value></Field>%'
;
 
UPDATE OR REPLACE xTmpHybridSourcesUnSetFF
SET FieldsTxt =
REPLACE (
         FieldsTxt
         , '<Field><Name>ForceFF</Name><Value>GEDCOM</Value></Field>'
         , '<Field><Name>ForceFF</Name><Value/></Field>'
         )
;
 
UPDATE OR REPLACE SourceTable
SET FIELDS =
(
SELECT CAST(FieldsTxt AS BLOB) FROM xTmpHybridSourcesUnSetFF
 WHERE SourceTable.SourceID = xTmpHybridSourcesUnSetFF.SourceID
)
WHERE SourceID IN (SELECT SourceID FROM xTmpHybridSourcesUnSetFF)
;