Contents
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:
-- Source Template List -- created by romermb 13 Feb 2010 SELECT * FROM SourceTemplateTable INNER JOIN SourceTable ON SourceTemplateTable.TemplateID = SourceTable.TemplateID
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
-- 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
Revised Query for Easier Reading
Screenshot
 |
| Separate fields for surname, suffix, prefix, given and nickname concatenated into one for more compact and easier to read results. |
Download
SourceTemplateListWithCitationDetails2.sql
Discussions & comments from Wikispaces site
Microsoft Access and Other Database Applications
15 February 2010 05:16:00
For those who are using Microsoft Access and other database applications and might want to store the sections of code between UNION ALL as separate queries, with the final one using UNION ALL to bring the various pieces together, I’ve gone ahead and retained the alias references in each section.
I propose that we adopt this standard going forward, as well.
romermb
Additional Fields?
15 February 2010 05:23:22
For those using the Source Template List with Citation Details query to consider consolidating Master Sources to create new Source Templates, please let me know if any additional fields might be helpful.
In addition, if a different sorting sequence might be useful, I’d be interested in knowing, as well.
I created the query without having gone through that exercise, so anyone with practical experience might have some unique insights or needs to better accomplish the task.
ve3meo
Good query!
15 February 2010 15:42:00
Subject says it all!
Thanks, Romer.
romermb
16 February 2010 02:13:47
Thanks — hope it helps those who find it useful.
ve3meo
TemplateID, Pre- vs User-defined
15 February 2010 15:46:58
For those of us into the tables, adding a column for the TemplateID would be useful.
For reporting to others who are not, a column indicating whether the template is Pre-defined or User-defined could be useful. The TemplateID indicates that, any >9999 are user-defined.
romermb
16 February 2010 02:22:53
Good idea. I decided to go ahead and add a Template Type indicator field to the code that I just modified on the page.
For those instead wanting TemplateID, the field that I added could easily be modified to fit your needs:
SourceTemplateTable.TemplateID AS ‘Template Number’
Instead of ordering on ‘Template Name’, you might then consider ordering on TemplateID and/or even shifting the order of those two fields in the SELECT clause.
ve3meo
SQL Error – no such function IF
01 June 2010 03:38:16
This is a surprise – both my updated versions of SQLite Developer and SQLiteSpy return this error message when compiling the long query:
/* Error message: no such function: IF */ . They both use SQLite 3.6.23. I’m sure both ran this query when it was first published.
ve3meo
01 June 2010 03:47:19
I am probably mistaken that either Developer or SQLiteSpy ran this query OK because IF is not a core SQLite function. You must have used a SQLite manager with an extension. We would have to use CASE instead for base level compatibility.
ve3meo
Correction re Alternate Name Fact
03 November 2011 20:49:53
Not sure why this was not detected before but I found that sources cited by Alternate Name facts were ascribed to the wrong person using the query SourceTemplatesUsageList.sql and others derived therefrom. This is corrected by changing the lines:
INNER JOIN NameTable AS NameTable2 ON NameTable1.OwnerID = NameTable2.NameID WHERE CitationTable.OwnerType = 7
to
INNER JOIN NameTable AS NameTable2 ON NameTable1.OwnerID = NameTable2.OwnerID WHERE CitationTable.OwnerType = 7 AND NameTable2.NameType=0
I have revised the page accordingly and will follow up on the affected files.
Tom
ve3meo
04 November 2011 01:58:55
That was not quite right because NameType could be 0 for an Alt Name if no NameType has been assigned. The correct code is:
INNER JOIN NameTable AS NameTable2 ON NameTable1.OwnerID = NameTable2.OwnerID WHERE CitationTable.OwnerType = 7 AND +NameTable2.IsPrimary = 1
romermb
15 February 2010 13:11:28
Forgot to add also that query performance is slower than I’d expect it to be. My guess is that the issue relates to SQLite3 query optimization, so I’ll have to play around with the query a bit here at some point to see if I can resolve it.