Duplicate Source Templates Redux

#1 · 2026-01-12, 11:58 am

Quote from thejerrybryan on 2026-01-12, 11:58 am
Tom already posted an excellent discussion of duplicate source templates at https://sqlitetoolsforrootsmagic.com/source-templates-merge-duplicates/ He also included a nice little script to merge duplicate source templates.
I recently needed to merge some duplicate source templates as described at https://community.rootsmagic.com/t/gedcom-and-drag-and-drop-produce-duplicate-user-defined-source-templates/15282 I ended up doing my own merge rather than using Tom's, but I wish maybe I had used his. It certainly would have been easier. But in reading his script (and not actually testing it), I think it needs a minor update to reflect changes in RM since it was written. In particular. The field in question is SourceTemplateTable.FieldDefs
RM no longer includes the XML header that reads something like <?xml version="1.0" encoding="UTF-8"?> Instead, FieldDefs starts simply with <Root><Fields><Field> So the original template and its copy will be different in this manner.
RM sometimes (maybe always?) adds a line feed (CHAR(10) in SQLite or 0x0a in hex) at the end of FieldDefs. Again, the original template and its copy will be different in this manner.
RM used to encode null tags as <Hint><Hint/> and as <LongHint><LongHint/>. It now encodes null tags as </Hint> and as </LongHint>.
My own little effort gets around problem with the following horrible looking expression. It strips out everything outside of <Root> ... <Root/> and it normalizes the coding of the null Hint and LongHint tags.
SELECT
REPLACE
(
REPLACE
(
SUBSTR
(
CAST(ST.FieldDefs AS TEXT),
INSTR(CAST(ST.FieldDefs AS TEXT), '<Root>'),
INSTR(CAST(ST.FieldDefs AS TEXT), '</Root>') - INSTR(CAST(ST.FieldDefs AS TEXT), '<Root>') + 7
), '<Hint></Hint>','<Hint/>'
), '<LongHint></LongHint>','<LongHint/>'
)
AS Fields
All my casting of the BLOB to TEXT really isn't necessary because all of the string functions cast BLOB's to TEXT implicitly. Tom used a LOWER to ignore case differences. I probably should have, but I didn't think of it and none of my duplicates templates had any case differences. Also, none of mine had any white space differences, which Tom also took care of.
I don't have a true solution like Tom posted. All I have is a query that verifies that the differences in the original templates and the copies made by drag and drop are irrelevant as described above. I then did the merge by doing the needed updates to SourceTable and SourceTemplateTable manually, whereas Tom's script automates the needed updates very nicely. So I'm not posting to replace Tom's script. I'm just posting to report on differences in the way RM handles the source templates between 2016 and now. I think some of these differences may already have been reported, but I couldn't find those reports. Mea culpa if none of this information is actually new.

Tom already posted an excellent discussion of duplicate source templates at https://sqlitetoolsforrootsmagic.com/source-templates-merge-duplicates/ He also included a nice little script to merge duplicate source templates.

I recently needed to merge some duplicate source templates as described at https://community.rootsmagic.com/t/gedcom-and-drag-and-drop-produce-duplicate-user-defined-source-templates/15282 I ended up doing my own merge rather than using Tom's, but I wish maybe I had used his. It certainly would have been easier. But in reading his script (and not actually testing it), I think it needs a minor update to reflect changes in RM since it was written. In particular. The field in question is SourceTemplateTable.FieldDefs

RM no longer includes the XML header that reads something like <?xml version="1.0" encoding="UTF-8"?> Instead, FieldDefs starts simply with <Root><Fields><Field> So the original template and its copy will be different in this manner.
RM sometimes (maybe always?) adds a line feed (CHAR(10) in SQLite or 0x0a in hex) at the end of FieldDefs. Again, the original template and its copy will be different in this manner.
RM used to encode null tags as <Hint><Hint/> and as <LongHint><LongHint/>. It now encodes null tags as </Hint> and as </LongHint>.

My own little effort gets around problem with the following horrible looking expression. It strips out everything outside of <Root> ... <Root/> and it normalizes the coding of the null Hint and LongHint tags.

SELECT

REPLACE
(
REPLACE
(
SUBSTR
(
CAST(ST.FieldDefs AS TEXT),
INSTR(CAST(ST.FieldDefs AS TEXT), '<Root>'),
INSTR(CAST(ST.FieldDefs AS TEXT), '</Root>') - INSTR(CAST(ST.FieldDefs AS TEXT), '<Root>') + 7
), '<Hint></Hint>','<Hint/>'
), '<LongHint></LongHint>','<LongHint/>'
)

AS Fields

All my casting of the BLOB to TEXT really isn't necessary because all of the string functions cast BLOB's to TEXT implicitly. Tom used a LOWER to ignore case differences. I probably should have, but I didn't think of it and none of my duplicates templates had any case differences. Also, none of mine had any white space differences, which Tom also took care of.

I don't have a true solution like Tom posted. All I have is a query that verifies that the differences in the original templates and the copies made by drag and drop are irrelevant as described above. I then did the merge by doing the needed updates to SourceTable and SourceTemplateTable manually, whereas Tom's script automates the needed updates very nicely. So I'm not posting to replace Tom's script. I'm just posting to report on differences in the way RM handles the source templates between 2016 and now. I think some of these differences may already have been reported, but I couldn't find those reports. Mea culpa if none of this information is actually new.