Source+Templates+-+Versatile+Free+Form+Hybrids

toc =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




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
rev 2013-12-17: !MyFreeForm3 templates no longer converted; speed tweaks.

Script
code format="sql" --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 '%ForceFF%' -- ForceFF is added by Hybrid     OR     FieldDefsTxt LIKE '%ShortPage%' -- 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), '', 'FootnoteFootnote (FF)Textfootnote sentence from Free Form source, without the source detail<CitationField>False</CitationField></Field><Field>ShortFootnote</FieldName>Short Footnote (FF)</DisplayName>Text</Type>short footnote sentence from Free Form source sans source detail</Hint><LongHint/><CitationField>False</CitationField></Field><Field>Page</FieldName>Page (FF)</DisplayName>Text</Type>page value from Free Form source, originally the source detail</Hint><LongHint/><CitationField>True</CitationField></Field><Field>Bibliography</FieldName>Bibliography (FF)</DisplayName>Text</Type>bibliography sentence from FF source</Hint><LongHint/><CitationField>False</CitationField></Field><Field>ForceFF</FieldName>FORCE FF</DisplayName>Text</Type>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. code

=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.

Script
code format="sql" -- 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) code =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.

Script
code format="sql" -- 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) code