Wikispaces hosting of SQLite Tools For RootsMagic to end on or before 30 Sep 2018.
For what we are doing, read Migrating from Wikispaces. Get involved. Help! A new site will be up by the drop-dead date, one way or another.
2018-09-17 PLEASE NO NEW PAGES, FILES, EDITS, COMMENTS OR DISCUSSIONS ON THIS SITE – THEY WILL NOT LIKELY BE TAKEN TO THE NEW SITE.

2017-06-28 RootsMagic 7.5 continues with the RM6 database structure so there should be no conflict with prior scripts. Of course these scripts remain unaware of the unique Ancestry TreeShare values used in one or more tables. Further investigation will reveal what they are and the database documentation will be revised accordingly. See Ancestry TreeShare – Impact for discussion.

Applying for Membership

Be sure to confirm your email address (respond to the email requesting you to do so) else you will not receive notification that your application has been accepted nor receive notices from the wiki. You may need to set your spam filter to trust messages from wikispaces.com.

Pages since RootsMagic 7 released

2018-07-22 GEDCOM & DnD transfer losses added
2018-07-13 File Question added (re: .DAT files in C:UsersusernameAppDataRoamingRootsMagicAMT)
2018-03-19 Migrating from Wikispaces added
2018-01-28 TreeShare – Link Pasted Ancestry Sources added
2017-12-27 AllCitations.sql Error added
2017-12-16 Possible Orphaned Records in the Event Table added
2017-12-13 Database is locked added
2017-12-12 Exporting Data added
2017-11-21 Query is taking a very, very long time added
2017-11-11 SQLite Expert Personal Edition Version 5.2.2.240 (x64) problem added
2017-10-25 Where is this data? added
2017-10-18 Removing media from one person added
2017-10-17 Where is the data stashed added
2017-09-15 Alternate Names List added
2017-09-13 Query to Add Parents Events to RM Database added
2017-09-03 Identifying Events with Individual Sentence Customization added
2017-08-06 Database Design Spreadsheets added. Updated workbook to cover RM 7.5 and converted to Google Sheets.
2017-07-21 Ancestry TreeShare – Impact rev. Added: Disconnect but preserve Ancestry Sources links for next Upload
2017-07-09 Groups – Extract most everything for one to a new database added
2017-07-05 TreeShare – Rename Cryptic Filenames for Citation Media added
2017-07-01 Search – wayfinding from data tables to RootsMagic screens rev script for new table for TreeShare
2017-06-28 Ancestry TreeShare – Impact added; ongoing revs. TreeShare released in RM7.5 that day.
2017-04-20 Places – Count People and Events for a Group added
2017-04-19 Source Templates – Convert Builtins to Editable, Split, Import added
2017-04-19 Navigation Sidebar revised – removed Poll, added Nav widget and Tag Cloud
2017-04-17 A Sample Query Created with Views rev with addition of script Events-CitationsMissing-UsingViews.sql
2017-04-06 REMATCH to FamilySearch ID in REFN fact added
2017-03-30 Sources – Unreverse Author Names added
2017-03-21 Source List Query rev MasterSources.sql to show unused Master Sources
2017-02-27 Places – Delete Unused added.
2017-02-25 Names – married name in death sentences added. Adds or replaces custom local sentence to use husband’s surname..
2017-02-20 SpouseID Invalid added. Addresses cause of empty Family View symptom.
2017-02-11 Merge Duplicate Single Parent Couples added. Rev 2017-02-14 corrected bug
2017-02-11 Births – Add from Christening or Baptism added
2017-02-05 Names – Delete Duplicate Alternate Names of Type ‘Married’ added.
2017-01-03 Group – Population statistic added. Lists group names and number of people in named groups.
2016-11-20 WebTags – convert to Notes et al edited for ease-of-use, speed and reusabaility
2016-09-05 COPY FamilySearchID to REFN fact added. One use is to reliably match duplicate people for merging.
2016-08-18 A Sample Query Created with Views added. Demonstrates a modular approach to building a complex query.
2016-08-16 Reports, Narrative, Jerry Bryan Trick rev. Ver 2 reduces the instances of excessive white space, a collateral effect of the trick.
2016-08-12 Reports – New Paragraph for General Note added. Improved format for narrative reports.
2016-08-02 Quick Start for Dummies added. Know nothing about SQLite? Start here…
2016-07-30 Color Code by Consanguinity Degree added.
2016-07-29 Shared Events – Sort Witnesses by Consanguinity plus Name added. Sorts sharers of event by consanguinity, then by name.
2016-07-29 Relationships rev. Now calculates consanguinity degree.
2016-05-08 Events – Move Description to Note added. Moves the entire contents of event descriptions of a selected fact type to the Note for that event.
2016-05-08 Query – All Names in Tree added. Lists all those in the hourglass of the defined person’s tree.
2016-04-22 Source Templates – Merge Duplicates rev. to make insensitive to case and space character.
2016-04-05 Reports – Point Form Narratives Setup version 2.
2016-03-28 Maps – Geo-Pedigree, plotting your ancestors’ birth places started but aborted in favour of RootsMapper.com
2016-03-28 FTM import – restore Event description from Place Details added. Move the content from each event’s Place Detail to the event’s Description field.
2016-02-12 Statistics added. A start; reports the male and female having the maximum children total with all spouses.
2016-02-07 Media – Bulk Rename and Relink added. Converts structured media filenames to another structure and relinks them.
2016-02-04 Language other than English added. Demonstrates current limitations, working in French.
2016-02-01 Reports – Point Form Narratives Setup added. Makes the Narrative format less verbose, easier to scan.
2016-01-29 Maps – markers proportional to number of events added
2016-01-21 Relationships fast Kinship List added
2016-01-17 Facts – Split Shared to Individual edited: added version without RIN displayed
2016-01-16 RIN MRIN – add Reference Number fact to all persons added
2016-01-10 Spouse Order – Global Sort by Marriage or First-Birth SortDate added
2016-01-08 Source Citation Sort added
2016-01-07 Roles – Sort Alphabetically added
2015-12-31 Nickname Manipulations added
2015-12-19 Citations Invisible – Reveal and Revert added (of interest to migrants from FTM)
2015-11-24 Recursive Queries – Ancestors and Descendants rev (gender filter for genetic lines)
2015-10-18 Children – Set Lineage to Birth added (so RootsMagic ancestor reports follow birth lines)
2015-03-30 TMG to RootsMagic Migration added (index and summary of useful pages for the TMG emigre)
2015-02-16 Marriages, Length of added (answers how long each marriage lasted)
2015-01-25 Living Flag – Set Globally added (can correctly set/clear living flag without Death fact in most cases)
2015-01-23 Children – Needing Manual Arranging added (lists families in which one or more children lack Birth or Christen facts)
2015-01-22 Children – Set Order by Birth SortDate added (batch sorting of children in birth order)
2015-01-21 Places – Conversion of Place Details to Places added (for when you need to export to other software)
2015-01-05 SQLite Managers for Mac OS added
2014-12-11 Sources – Copy Repository Name, Address from Repository List added; rev 14 Dec.
2014-11-25 RootsMagic 7 added
Earlier queries – most work with RM7 databases; Most scripts since RM7 are applicable to RM6 but are less so with the older versions.

Welcome to SQLite Tools for RootsMagic!

This wiki style site is intended to enhance our use of RootsMagic 4 and above with queries and reports not provided from within the program. RootsMagic 4 through 7 use SQLite 3 as their database engine so the .rmgc database files each creates are readable using third party SQLite management and development tools.

We explore collaboratively the RootsMagic 4+ databases with some of these SQLite managers and develop SQL queries that attempt to answer questions that cannot be answered or are difficult to answer using the RM application. Become a Member with full privileges to create and edit pages, discuss them, upload files, and help make our wiki better! Be sure to confirm your email address when applying to join else you will not receive notice of approval or other messages from the wiki.

The site has features, such as file storage, essential to such development and not available from the RootsMagic Forums. All other RootsMagic topics should continue there.

RootsMagic SQL Queries

How to Query RootsMagic – Experiences of novice users and an example using SQLiteSpy to do a database integrity check.
RM SQLite Queries – Plunge right in, if you have an idea of what you are doing.
Problem Queries – Post your problem for discussion.
SQLite Managers – Choose from one of these tools to run your queries.
MS Access – Or connect to your database with Access and Excel for great looking reports.
Open Office – Use the same ODBC driver as described for MS Access to connect OpenOffice to a RM database. Not recommended!

Find Pages by Tag

1. 7zip
2. FamilySearch
3. TreeShare
4. ahnentafel
5. alternate names
6. ancestors
7. ancestry.com
8. application
9. backup
10. batch
11. birth
12. blanks
13. blob fields
14. broken
15. census
16. child
17. citations
18. color coding
19. config
20. conversion
21. data definitions
22. database
23. database properties
24. date
25. death
26. delete
27. descendants
28. duplicates
29. editable
30. error
31. events
32. fact types
33. family
34. filenames
35. french
36. ftm2012
37. ftm2014
38. fts()
39. gedcom
40. geo-coding
41. google maps
42. index
43. integrity
44. links
45. living
46. marriage
47. media
48. merge
49. metadata
50. mrin
51. ms access
52. ms excel
53. multimediatable
54. named group
55. names
56. notes
57. odbc
58. paragraphing
59. person
60. phantom
61. place details
62. places
63. pragma
64. private
65. rar
66. recursive
67. refno
68. relationships
69. replace
70. reports
71. repositories
72. request for help
73. rin
74. rmnocase
75. roles
76. search
77. shared event
78. sortdate
79. source templates
80. sources
81. speed
82. split tree ancestors leaves
83. spouse
84. sql
85. sqlite expert
86. sqlite managers
87. sqlitespy
88. statistics
89. sub-queries
90. tasks
91. tmg
92. to do list
93. tools
94. update
95. views
96. visual basic
97. visual c#
98. webtags
99. webtrees
100. xml

The PoorSQL formatter does a pretty good job of cleaning up scripts to make them more readable but is written for T-SQL so does not support SQLite SQL fully. It puts a space between double character operators such as <<, >>, ==; these can be readily searched and replaced in a text editor such as PSPad.

Thank you, 2016 Donors!

Wikispaces terminated free subscriptions for non-education wikis on Oct 15, 2014 and commenced charging US$50 p.a. for ongoing service. As of Nov 12, 2016, our fee jumped 100% to US$100 p.a. There was no apparent (to me, at least) free or cheaper alternative to which this wiki can be readily migrated. So, once again, I launched an appeal on 14 Sep 2016 for your financial support to keep it going for another 12 months. Within 12 hours, the target was exceeded and the campaign was terminated soon after; generous donations have extended our subscription to Jan 11, 2019. Fundraising for 2016-17 is now terminated. Full report at Future of SQLite Tools for RootsMagic wiki 2016-17