Source+Template+List+-+Query

=Source Template List - Query= toc RootsMagic 4 added the concept of Source Templates. With the new feature, no report functionality for it has yet been integrated into the Source List Print, however (RM 4.0.7.1, currently).

This information might be useful in a number of applications, including in the case in which the initial Master Source/Source Details field split resulted in too many entries and some consolidation were desired.

The following SQL code is simply designed to indicate which Sources are associated with which Source Template and brings together the information between SoureTemplateTable and SourceTable:

code format="sql" -- Source Template List -- created by romermb 13 Feb 2010 SELECT * FROM  SourceTemplateTable INNER JOIN SourceTable ON      SourceTemplateTable.TemplateID = SourceTable.TemplateID code

For purposes of this query and the one to follow, entries within SourceTable with SourceID of 0 are effectively excluded. This value refers to a Free-Form template and is not stored in SourceTemplateTable.

The code can be extended to provide citation-related details in a bit of a similar way to the Source List Print in RM4: [|SourceTemplateListWithCitationDetails.sql] **Revised 2011-11-04** code format="sql" -- Source Template List with Citation Details -- created by romermb 14 Feb 2010 -- modified by romermb 15 Feb 2010 to override SQLite query optimization routine treatment of --            IsPrimary field in order to produce faster run time, --            to add Template Type field -- 2010-06-01 rev by ve3meo to replace IF construct (not supported without a load extension) by CASE

-- Person Citations SELECT  SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name', CASE WHEN SourceTemplateTable.TemplateID < 10000 THEN 'Built-In' ELSE 'User-Defined' END AS 'Template Type', SourceTable.Name COLLATE NOCASE AS 'Source Name', 'Person' AS 'Citation Type', NULL AS 'Fact Type', NULL AS MRIN, NameTable.OwnerID AS 'RIN 1', NameTable.Surname COLLATE NOCASE AS 'Surname 1', NameTable.Suffix COLLATE NOCASE AS 'Suffix 1', NameTable.Prefix COLLATE NOCASE AS 'Prefix 1', NameTable.Given COLLATE NOCASE AS 'Given 1', NameTable.Nickname COLLATE NOCASE AS 'Nickname 1', NULL AS 'RIN 2', NULL AS 'Surname 2', NULL AS 'Suffix 2', NULL AS 'Prefix 2', NULL AS 'Given 2', NULL AS 'Nickname 2', COUNT(1) AS Records FROM    SourceTemplateTable INNER JOIN SourceTable ON        SourceTemplateTable.TemplateID = SourceTable.TemplateID INNER JOIN CitationTable ON        SourceTable.SourceID = CitationTable.SourceID INNER JOIN NameTable ON        CitationTable.OwnerID = NameTable.OwnerID WHERE   CitationTable.OwnerType = 0 AND +NameTable.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17

UNION ALL

-- Family Citations SELECT  SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name', CASE WHEN SourceTemplateTable.TemplateID < 10000 THEN 'Built-In' ELSE 'User-Defined' END AS 'Template Type', SourceTable.Name COLLATE NOCASE AS 'Source Name', 'Family' AS 'Citation Type', NULL AS 'Fact Type', FamilyTable.FamilyID AS MRIN, NameTable1.OwnerID AS 'RIN 1', NameTable1.Surname COLLATE NOCASE AS 'Surname 1', NameTable1.Suffix COLLATE NOCASE AS 'Suffix 1', NameTable1.Prefix COLLATE NOCASE AS 'Prefix 1', NameTable1.Given COLLATE NOCASE AS 'Given 1', NameTable1.Nickname COLLATE NOCASE AS 'Nickname 1', NameTable2.OwnerID AS 'RIN 2', NameTable2.Surname COLLATE NOCASE AS 'Surname 2', NameTable2.Suffix COLLATE NOCASE AS 'Suffix 2', NameTable2.Prefix COLLATE NOCASE AS 'Prefix 2', NameTable2.Given COLLATE NOCASE AS 'Given 2', NameTable2.Nickname COLLATE NOCASE AS 'Nickname 2', COUNT(1) AS Records FROM    SourceTemplateTable INNER JOIN SourceTable ON        SourceTemplateTable.TemplateID = SourceTable.TemplateID INNER JOIN CitationTable ON        SourceTable.SourceID = CitationTable.SourceID INNER JOIN FamilyTable ON        CitationTable.OwnerID = FamilyTable.FamilyID INNER JOIN NameTable AS NameTable1 ON        FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN NameTable AS NameTable2 ON        FamilyTable.MotherID = NameTable2.OwnerID WHERE   CitationTable.OwnerType = 1 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17

UNION ALL

-- Person Fact Citations SELECT  SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name', CASE WHEN SourceTemplateTable.TemplateID < 10000 THEN 'Built-In' ELSE 'User-Defined' END AS 'Template Type', SourceTable.Name COLLATE NOCASE AS 'Source Name', 'Fact - Person' AS 'Citation Type', FactTypeTable.Name COLLATE NOCASE AS 'Fact Type', NULL AS MRIN, NameTable.OwnerID AS 'RIN 1', NameTable.Surname COLLATE NOCASE AS 'Surname 1', NameTable.Suffix COLLATE NOCASE AS 'Suffix 1', NameTable.Prefix COLLATE NOCASE AS 'Prefix 1', NameTable.Given COLLATE NOCASE AS 'Given 1', NameTable.Nickname COLLATE NOCASE AS 'Nickname 1', NULL AS 'RIN 2', NULL AS 'Surname 2', NULL AS 'Suffix 2', NULL AS 'Prefix 2', NULL AS 'Given 2', NULL AS 'Nickname 2', COUNT(1) AS Records FROM    SourceTemplateTable INNER JOIN SourceTable ON        SourceTemplateTable.TemplateID = SourceTable.TemplateID INNER JOIN CitationTable ON        SourceTable.SourceID = CitationTable.SourceID INNER JOIN EventTable ON        CitationTable.OwnerID = EventTable.EventID INNER JOIN FactTypeTable ON        EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN NameTable ON        EventTable.OwnerID = NameTable.OwnerID WHERE   CitationTable.OwnerType = 2 AND +NameTable.IsPrimary = 1 AND EventTable.OwnerType = 0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17

UNION ALL

-- Family Fact Citations SELECT  SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name', CASE WHEN SourceTemplateTable.TemplateID < 10000 THEN 'Built-In' ELSE 'User-Defined' END AS 'Template Type', SourceTable.Name COLLATE NOCASE AS 'Source Name', 'Fact - Family' AS 'Citation Type', FactTypeTable.Name COLLATE NOCASE AS 'Fact Type', FamilyTable.FamilyID AS MRIN, NameTable1.OwnerID AS 'RIN 1', NameTable1.Surname COLLATE NOCASE AS 'Surname 1', NameTable1.Suffix COLLATE NOCASE AS 'Suffix 1', NameTable1.Prefix COLLATE NOCASE AS 'Prefix 1', NameTable1.Given COLLATE NOCASE AS 'Given 1', NameTable1.Nickname COLLATE NOCASE AS 'Nickname 1', NameTable2.OwnerID AS 'RIN 2', NameTable2.Surname COLLATE NOCASE AS 'Surname 2', NameTable2.Suffix COLLATE NOCASE AS 'Suffix 2', NameTable2.Prefix COLLATE NOCASE AS 'Prefix 2', NameTable2.Given COLLATE NOCASE AS 'Given 2', NameTable2.Nickname COLLATE NOCASE AS 'Nickname 2', COUNT(1) AS Records FROM    SourceTemplateTable INNER JOIN SourceTable ON        SourceTemplateTable.TemplateID = SourceTable.TemplateID INNER JOIN CitationTable ON        SourceTable.SourceID = CitationTable.SourceID INNER JOIN EventTable ON        CitationTable.OwnerID = EventTable.EventID INNER JOIN FactTypeTable ON        EventTable.EventType = FactTypeTable.FactTypeID INNER JOIN FamilyTable ON        EventTable.OwnerID = FamilyTable.FamilyID INNER JOIN NameTable AS NameTable1 ON        FamilyTable.FatherID = NameTable1.OwnerID INNER JOIN NameTable AS NameTable2 ON        FamilyTable.MotherID = NameTable2.OwnerID WHERE   CitationTable.OwnerType = 2 AND +NameTable1.IsPrimary = 1 AND +NameTable2.IsPrimary = 1 AND EventTable.OwnerType = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17

UNION ALL

-- Alternate Name Fact Citations SELECT  SourceTemplateTable.Name COLLATE NOCASE AS 'Source Template Name', CASE WHEN SourceTemplateTable.TemplateID < 10000 THEN 'Built-In' ELSE 'User-Defined' END AS 'Template Type', SourceTable.Name COLLATE NOCASE AS 'Source Name', 'Fact - Alt Name' AS 'Citation Type', 'Alt Name' AS 'Fact Type', NULL AS MRIN, NameTable2.OwnerID AS 'RIN 1', NameTable2.Surname COLLATE NOCASE AS 'Surname 1', NameTable2.Suffix COLLATE NOCASE AS 'Suffix 1', NameTable2.Prefix COLLATE NOCASE AS 'Prefix 1', NameTable2.Given COLLATE NOCASE AS 'Given 1', NameTable2.Nickname COLLATE NOCASE AS 'Nickname 1', NULL AS 'RIN 2', NULL AS 'Surname 2', NULL AS 'Suffix 2', NULL AS 'Prefix 2', NULL AS 'Given 2', NULL AS 'Nickname 2', COUNT(1) AS Records FROM    SourceTemplateTable INNER JOIN SourceTable ON        SourceTemplateTable.TemplateID = SourceTable.TemplateID INNER JOIN CitationTable ON        SourceTable.SourceID = CitationTable.SourceID INNER JOIN NameTable AS NameTable1 ON        CitationTable.OwnerID = NameTable1.NameID INNER JOIN NameTable AS NameTable2 ON        NameTable1.OwnerID = NameTable2.OwnerID WHERE   CitationTable.OwnerType = 7 AND +NameTable1.IsPrimary = 0 AND +NameTable2.IsPrimary = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17

ORDER BY 1, 3, 7, 13, 5

code

Download
[|SourceTemplateListWithCitationDetails2.sql]