Source+List+Query

toc =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,: 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) 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

Source List - Citations
Download 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:

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



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