Description

Here are the most advanced and useful SQLite queries for reviewing citations in your RootsMagic 4 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 RM4 and then finding the person and fact using that source in RM4. 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
  2. List of all the Master Sources (relatively few) with their details but not the Source Details of each citation
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.

Source List - Citations

Download SourceList.sql RMtrix_tiny_check.png
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)

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:

SourceListQueryScreenShotExpert.png
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.
SourceListQueryScreenShot.png
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

Column
Description
Editable
Source Name
name of the Master Source; primary sort field in this query
N
Source Fields
the field names and values entered in the Master Source
Y
Cit Fields
the field names and values entered in the Source Details screen for a citation
Y
Qual
citation quality code (decoding would take up too much screen space but you'll get the drift)
Y
Cit Text
Source Details text
Y
Cit Comment
Source Details comment
Y
Cited by
person, family or fact type that cited the source
N
EventDate
fact/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 Place
Place of the fact/event
N
Event Site
Place Detail of the fact/event
N
RIN 1
record number of person whose fact cited the source
N
Person 1
name of person whose fact cited the source
N
MRIN
FamilyID of FamilyTable, the Marriage Record Number that is invisible in RM4
N
RIN 2
record number of spouse
N
Person 2
name of spouse
N
CitID
CitationID of CitationTable
N
SrcID
SourceID of SourceTable
N
Template
name of Source Template
N
TpltID
TemplateID of SourceTemplateTable
N
Tplt Type
"OEM"=Built-in Source Template (TemplateID<10000); "USR"=user-defined Source Template (TemplateID>9999)
N

Master Sources


Download MasterSources.sql RMtrix_tiny_check.png
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)


MasterSourcesScreenShot.png

Column Definitions


Column
Description
Editable
SrcID
SourceID of SourceTable
N
Source Name
name of the Master Source; primary sort field in this query
N
RefNumber
Master Source File #
Y
SrcFields
the field names and values entered in the Master Source screen
Y
ActualText
Master Source text
Y
Comments
Master Source comments
Y
IsPrivate
1 if Master Source marked private, else 0 but currently unused (?)
Y
Citations
number of times Master Source is cited
N
Template
name of Source Template
N
TpltID
TemplateID of SourceTemplateTable
N