Source Template List – Query #sources #sourcetemplates

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

SourceTemplateListWithCitationDetails2-screenshot.png
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


romermb

Microsoft Access and Other Database Applications

romermb
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?

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


romermb

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.


ve3meo

Good query!

ve3meo
15 February 2010 15:42:00

Subject says it all!
Thanks, Romer.


romermb

romermb
16 February 2010 02:13:47

Thanks — hope it helps those who find it useful.


ve3meo

TemplateID, Pre- vs User-defined

ve3meo
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

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

ve3meo
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

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

ve3meo
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

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.