Logo Home    Page list

home

Table of Contents

Pages since RootsMagic 7 released
Welcome to SQLite Tools for RootsMagic!
RootsMagic SQL Queries
External Resources
Thank you, 2016 Donors!
Wikispaces hosting of SQLite Tools For RootsMagic ended on 30 Sep 2018.
2018-10-01 This temporary site was launched using the HTML export from Wikispaces with minimal navigation added. A WordPress site is in the works. If you wish to be notified when it is up, send an email with "subscribe" in the subject to info@sqlitetoolsforrootsmagic.com.

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.

Pages since RootsMagic 7 released

2018-07-22 GEDCOM & DnD transfer losses added
2018-07-13 File Question added (re: .DAT files in C:\Users\username\AppData\Roaming\RootsMagic\AMT)
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!

External Resources


RootsMagic
SQLite
GEDCOM 5.5.1
Home
Home
Standard
Forums
Wiki
Torture Files
Support
Management Tools


Users Mailing List


PoorSQL formatter

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

Discussions

ve3meo

What should home page have on it?

ve3meo 31 December 2009 19:17:51

I'm new to Wikispaces and not sure how best to use it. So far, every new page I have created shows up in the navigation sidebar so it seems unnecessary to have links to those pages from the home page. Then again, if there are many pages created, the sidebar list could get awfully long, calling for a tree structure with just the main branches available on the sidebar.

This messag is, in part, a first attempt at seeing how a discussion revolves around a page.

TomH

romermb
romermb 05 January 2010 17:42:57

I also wish that the sidebar were more customizable to better organize the wiki and provide some structure, but lacking that ability, perhaps we should just attempt to accomplish that feat within the body of the Home page. It looks as though we're somewhat moving in that direction, and perhaps we can continue to try to move in that way going forward.

I'm afraid that the sidebar in its current form can really only serve as a means to jump to a familiar page, but not to provide a sense of organization to the wiki.

RWells1938

Update of PlaceTable

RWells1938 05 May 2011 00:11:26

As I hopefully explained I am trying to update the place table abbrev and normalized. Both of the fields have a variation of the name field.

I used sql to update the normalized field: Select PlaceID, Name, Normalized From PlaceTable Where PlaceID > 146 AND Normalized = ""
It worked just fine.

Now I wanted to change the normalized field to remove the word "County". I tried several variations of this sql SELECT REPLACE (Normalized, ' County,' , ",") FROM PlaceTable;
but no luck.

I am using both sqlitespye and sqlite database browser both give the same results.

RM4 dose not allow you to change but one field in the place table and that is the name field unless I am missing something.

Any help you could offer or point me in a direction would be appreciated.

Thanks
Roger

ve3meo
ve3meo 05 May 2011 00:23:50

Already answered by kbens0n on the RootsMagic Forum and confirmed by me using SQLiteSpy:

UPDATE PlaceTable
SET Normalized = REPLACE(Normalized, 'County', ',')
WHERE Normalized LIKE '%County%';

Tom

ve3meo

Impact of RM5 on this wiki?

ve3meo 04 December 2011 21:17:46

How should we handle the fork to RM5?
A. Start a new wiki?
B. Incorporate it in and carry on with this wiki?

We can change the domain name but that will break every prior link published externally. I'm inclined to start a new one with references back to this one.

Tom

MarkVS

A Query to find Events that should be "Shared"

MarkVS 13 December 2011 13:44:22

I have still not tackled the task of going through my RM4 database to merge up events that were originally added separately for each individual (in RM3) but would have been added as a shared event in RM4, and RM5.

A Query that would show events that were likely to be suitable for merging would be very useful. In the vast majority of cases this would be a Census event - but it might apply to tohers.I was thinking along the lines of something that listed Census events where the following data was identical
a) the date of the event
b) Census type
c) identical address and place

Then sorted into date/address/place sequence so the likley identical events would be listed together for potential manual update in RM4/5.

Perhaps there would be a cleverer way to identify merge candidates across all Event types?

MVS.

ve3meo
ve3meo 13 December 2011 16:41:10

Coincidentally, I volunteered to look at a Legacy Family Tree to RM import that has a similar problem. LFT does not (or did not) have the shared event feature so the op put in identical facts and now wants to merge them as a shared event. I haven't started on it but thanks for the reminder. Let's start a page: Sharable Events - Find and Convert to Shared and move the discussion and development there. (prev note deleted - suggested page name revised)

John_James

Need a starter Idiot guide.

John_James 13 February 2013 23:58:20


I am new to SQL and have been playing with SQLite, I really wish I could begin by contributing something but maybe in the future.
I can easily alter data on one table using like :

UPDATE EventTable
SET Date='D.+18700601..+00000000..', SortDate='6682427620278140940'
WHERE Date LIKE '%1870%' AND EventType='18'

But when it gets to joins I am a bit fearful of what I might change and maybe without knowing it.

What I would like to do now is clean some data where indi’s have a death event and the gedcom contributor has used a custom “Cause of Death” event so in other words “Details” from “EventType 1021” into “EventType 2” where OwnerID is the same.

The other thing I could achieve myself at present would be to change all EventType 1021 to EventType 2 but then I would have duplicate events to deal with and that is another query I would find most useful in the future.

I do hope someone can find the time to present me with a heavily commented simple routine to best achieve this, then hopefully I can do some running on my own.

Thanks in advance

John James

ve3meo
ve3meo 14 February 2013 04:43:11

Take a look at http://sqlitetoolsforrootsmagic.wikispaces.com/Events+-+Merge and continue discussion there.

Tom

Jocelyn_Brooklyn

Edit Built-In Source Templates via SQLite?

Jocelyn_Brooklyn 15 June 2014 01:28:22

After a Saturday spent trying to implement SrcTmpltsConvert2.sql (newbie here)*, I wonder if we can manipulate the built-in source templates via the SourceTemplateTable in SQLite with impunity.

Doing so on a test database seems to work: SQLite Expert --> SourceTemplateTable --> Data tab --> double clicking any row brings up Record Editor. Changes made here (in the Footnote, Short Footnote, or Bibliography boxes) followed by a Database --> Reindex All Tables carry over beautifully to the affiliated database. (To apply changes for all built-in sources templates would take time, but, in sooth, I only use thirty or so.) Am I playing with fire?

Using RootsMagic 6.3.1.0, SQLite Expert Personal 3.5.51.271, unifuzz.dll.

*Running SrcTmpltsConvert2.sql query on my database (pre-imported with RootsMagicSTuser.rmst) produces a new table "temp.tempTemplateID" with no data in the three columns. The database is not affected by the query. Any ideas how I'm loading this wrong?

Thank you for any and all wisdom thrown off my way!

Jocelyn

ve3meo
ve3meo 15 June 2014 03:24:07

Hi Jocelyn,

1. I cannot affirm that the built in templates may be modified with impunity as we have no certainty that future updates or upgrades of the software will never affect them. Certainly, sources transferred from your custom database to a standard database will revert to the standard template.

2. If your database has no cited sources framed by built in templates, tempTemplateID will remain empty.

Would appreciate if you would post questions of this sort on a relevant page, not the home page. If there is no relevant page, post it to the Problems page or start a new one.

Thanks,
Tom

chuckfilteau

MSACCESS 2010 and ODBC drivers for SQLite

chuckfilteau 20 August 2015 01:10:50

Just getting started to set up the access. The wiki in place seems to be a bit outdated for 2010 (not 2007).

I'm using Windows 7 (64 bit).

I've downloaded the driver that was recommended (64). When I executed the driver it said it was complete. But I can't find the drivers

I went to the MS info page at https://support.microsoft.com/en-us/kb/2721825
and went to both
1) 64-bit ODBC Administrator tool
%systemdrive%\Windows\System32\odbcad32.exe
*I could find the driver here *

Windows 64-bit Office 2010, 2007,
or 2003 32-bit 32-bit ODBC Administrator tool
%windir%\SysWOW64\odbcad32.exe
*I could not find it here*

In any case, I'm completely lost in trying to specify a DSN

Any help is greatly appreciated. Thanks.

thejerrybryan
thejerrybryan 20 August 2015 18:27:11

A couple of things to help get you started. First, even though you are running 64 bit Windows, RM is a 32 bit program and you need a 32 SQLite3 driver. Worse that that, you need a 32 bit ODBC configurator which may be found at C:\WINDOWS\SysWOW64\odbcad32.exe. Finally, I never got a User DSN to work. I'm probably just a complete idiot and was doing something wrong that was terribly simple. But in any case, a System DSN has always worked just fine for me.

I realize that I'm leaving out many steps. The truth is that I downloaded the SQLite3 driver so long ago I don't remember the details. But once that is done. run C:\WINDOWS\SysWOW64\odbcad32.exe, pick the System DSN tab, click Add, and select the SLQlite3 ODBC driver which should be at the bottom the list. (How to get it there is the part I can't remember right now - but I think the directions on the Wiki are correct). and click Finish. You will be asked to give your data source a name (this is basically the name of what will become a System DSN) and the name of your RM database (really, the complete file pat of your RM database). Click OK a couple of times, and you are done with that part. Then go into Access or Excel. In Access 2007, you say External Date, More, ODBC Database, Link to the data source be creating a linked table, OK, Machine Datasource, pick the Machine DSN you just made from the list, OK. At this point, you will see a list of RM's tables. Select All, OK, and you are ready to start. For example, just double click the NameTable to see all the names in your RM database. I assume that the Access 2010 process is very similar.

Jerry

Marmanton15

New SQLite ODBC drivers

Marmanton15 03 December 2015 12:25:33

I see that this WIKI needs some fresh information to be added.
I found new SQLite ODBC drivers which now already support WIndows and Linux platforms. If you are in searches of them or have problems with those you have so here is the link through which you can download new SQLite ODBC drivers https://www.devart.com/odbc/sqlite/download.html

ve3meo
ve3meo 03 December 2015 12:56:10

Thanks for the info. However the $150 list is a major deterrent. I will stick to the free ODBC drivers.

JGSackis

Confirmation email not coming through

JGSackis 28 December 2015 17:59:22

Confirmation email not coming through, have checked account spam and inbox folders in both locations (I use sbcglobal.net and outlook). Made sure account can receive emails by sending from a different (non sbcglobal) account. Have resent confirmation notice several times. Any thoughts?

ve3meo
ve3meo 28 December 2015 19:29:17

You have been a member since yesterday. If you get this message, it is where the confirmation email would have been sent.

JGSackis

Problem with RM-Import-Unlinked-Media

JGSackis 01 March 2016 21:32:35

Should I post this here or on the Rootsmagic forum addressed to vyger?

After working well, now it is not properly identifying media that is in use. It is still showing up on the Unmatched list. Don't know if I'm doing something wrong. I have a graphic image of problem but don't know if or how I can post it here.

John

ve3meo
ve3meo 01 March 2016 21:39:22

Try the RM forum, John, or even Facebook. I don't think he monitors this page.

momakid

How to get Alternate Name back into NameTable file

momakid 18 July 2017 02:29:41

I am new to Rootsmagic. I did not like seeing all of the Alternate Names on the Edit Person screen. I deleted all of the Alternate Names in the EventTable. I deleted the Alternate Name records in the NameTable whose isprimary = 0. I did not realize what they signified. I now realize the ones in the NameTable were the married names displayed in the app. Now I want to get them back. I have a list of the ones that were alternate names in the NameTable file. By doing a query over an older file. Is there a query that will get them put them back? I would think there should be a way to do it in one swoop. Can someone help? If this is not the right place, please tell me where to ask the question.

ve3meo
ve3meo 18 July 2017 02:46:40

Provided the current and earlier databases have the same Record Numbers for people, then it should be possible to retrieve the Alt Names from the older database. In broad strokes, you would open the current database and ATTACH the old. Then you can query the NameTable in both and UPDATE one from the other.

Alternatively, this script generates Alt Names with the married name. http://sqlitetoolsforrootsmagic.wikispaces.com/Names+-+Add+Married Note that it uses Mrs. in the Prefix field which is lost when uploaded to Ancestry.

Tom

www.000webhost.com