Source List Query #sources #sourcetemplates #citations #xml #date


Here are the most advanced and useful SQLite queries for reviewing citations in your RootsMagic 4-8 database. They provide some of the same info that you would get in the RootsMagic Source List report and then some you don’t… You have the advantage of sorting and filtering results in ways you cannot within RM and then finding the person and fact using that source in RM. And they provide the key ID’s with which you can find specific table records and possibly edit fields directly using SQLite (if you know what you are doing). What is yet missing is expansion of the data into sentences using the sentence templates; this requires a high level programming language and is unlikely to be added.

Two Queries

Two separate queries are needed because basic SQLite managers can only present results in tabular format and screen width is insufficient to show all the fields that make up a fully detailed RM4 Source List report. The two queries provide, respectively,:

  1. List of all the citations (relatively many) with details against the names of the Master Sources (RM4-8 as of 2022-02-21)
  2. List of all the Master Sources (relatively few) with their details but not the Source Details of each citation (RM4-8 as of 2021-03-03)

Using a SQLite manager that supports two or more queries in separate windows or tabs (e.g. SQLiteSpy), it is easy to have both queries’ results available by toggling between windows. To view the properties of media items, use the Media List Query in a third window.

1. Source List – Citations

Download SourceList.sql RMtrix_tiny_check.png for RM4 to #RM7
2011-11-06 now reports Free Form and orphaned citations; count of media items linked to citation; improved format of CitFields; compatability with managers other than SQLiteSpy.
2011-11-18 now outputs Source Fields as per MasterSources.sql plus Event Place and Event Site (Place Detail)

Download SourceList-RM8.sql #rm8 version of SourceList.sql 2022-02-21

This complex query was built on Romer’s much earlier Source Template List – Query. In so doing, I discovered errors and inconsistencies in and among it and my All Citations – Query and All Citations & Dupes Count – Query. Hopefully, these are now resolved with revisions to all of them. Major additions include date decoding from Date Decoder and the stripping of XML tags from the FIELDS field pioneered in Source Detail View (Parsing XML). The result is a very comprehensive and readable spreadsheet. Here are two examples of output displays from two different SQLite managers, a different set of columns selected for display:

From the latest version of the query, as displayed by SQLite Expert. Note the wrapping of text.

SQLite Expert requires that you comment out the ORDER BY clause in order to sort on a results column; it adds an ORDER BY clause and re-runs the query. It has excellent filtering tools but one can add one’s own WHERE clause to filter results.

Screenshot of results of the query from SQLiteSpy

At the bottom of the screen, you see the content of the highlighted cell. Clicking on any column heading will sort on that one column. The order displayed in this screenshot is defined in the query which can be readily changed to suit your particular requirements. This one is sorted on Source Name, Cit Text, Cit Comment, Person 1, Cited by, EventDate, Person 2 and would be useful for reviewing citations of a source having identical texts.

Column Definitions

Source Namename of the Master Source; primary sort field in this queryN
Source Fieldsthe field names and values entered in the Master SourceY
Cit Fieldsthe field names and values entered in the Source Details screen for a citationY
Qualcitation quality code (decoding would take up too much screen space but you’ll get the drift)Y
Cit TextSource Details textY
Cit CommentSource Details commentY
Cited byperson, family or fact type that cited the sourceN
EventDatefact/event date (helps to pick out in the Edit Person screen which of two or more facts of the same type cited the source)N
Event PlacePlace of the fact/eventN
Event SitePlace Detail of the fact/eventN
RIN 1record number of person whose fact cited the sourceN
Person 1name of person whose fact cited the sourceN
MRINFamilyID of FamilyTable, the Marriage Record Number that is invisible in RM4N
RIN 2record number of spouseN
Person 2name of spouseN
CitIDCitationID of CitationTableN
SrcIDSourceID of SourceTableN
Templatename of Source TemplateN
TpltIDTemplateID of SourceTemplateTableN
Tplt Type“OEM”=Built-in Source Template (TemplateID<10000); “USR”=user-defined Source Template (TemplateID>9999)N

2. Master Sources

Download MasterSources.sql RMtrix_tiny_check.png #rm7
2011-11-06 now reports Free Form and orphaned citations; count of media items linked to master source; improved format of SrcFields

2017-03-21 show unused Master Sources and correct citation count (was offset by 1)

2020-06-21 the offset ‘corrected’ above was for unused Master Sources and threw all others off by 1; this rev works for both used and unused Master Sources.

Download MasterSources-RM8.sql #rm8
2021-03-03 Converted to work with both RM7 and RM8 format – PJ Feb2021


Column Definitions

SrcIDSourceID of SourceTableN
Source Namename of the Master Source; primary sort field in this queryN
RefNumberMaster Source File #Y
SrcFieldsthe field names and values entered in the Master Source screenY
ActualTextMaster Source textY
CommentsMaster Source commentsY
IsPrivate1 if Master Source marked private, else 0 but currently unused (?)Y
Citationsnumber of times Master Source is citedN
Templatename of Source TemplateN
TpltIDTemplateID of SourceTemplateTableN

Discussions & comments from Wikispaces site


The Queries Don’t Run on My System

05 November 2011 23:09:17

Neither query will run. There are no error messages. It’s just that there are no results at all – almost as if my database were empty. But I can hand code very simple queries that demonstrate my chosen SQL manager SQLiteSpy is working ok and that my database is not empty.

Any ideas?



06 November 2011 00:10:08

I haven’t looked at the first query yet, but the second query won’t run because all I have are Free Form templates. Hence, the following:

FROM SourceTemplateTable
SourceTemplateTable.TemplateID = SourceTable.TemplateID

yields a NULL result because the Free Form template appears not to be in the SourceTemplateTable.


3 Replies to “Source List Query #sources #sourcetemplates #citations #xml #date

  1. The second query shown here produced exactly the results I was looking for, but there is a problem. I am using RM 7.6.3. The count of citations for each source is one less than it should be. I see that something like this was addressed earlier, but the number displayed is one less than the count of citations shown in RM in the Lists/Source List/Print option.

    I have both freeform and templated sources and the citation count is incorrect for both.

    I’m afraid I don’t know enough SQL to suggest what might be wrong or what the fix might be.

  2. I made a foolish mistake in the 2017 rev. Unused Master Sources were reported as having 1 citation which is illogical so I subtracted 1 from the COUNT(). Great! But that threw every used MS off. The 2020 rev should set it right. Thanks for pointing it out.

Leave a Reply

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