Contents
Description
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,:
- List of all the citations (relatively many) with details against the names of the Master Sources (RM4-8 as of 2022-02-21)
- 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 
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
| 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 |
2. Master Sources
Download MasterSources.sql #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
| 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 |
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.
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.
Master Source List query updated by Pat Jones to be compatible with all versions RM4-8.