Home Page list
Groups - Extract most everything for one to a new database #namedgroup #TreeShare
RootsMagic GEDCOM and Drag'n'Drop transfer capabilities and performance leave some things to be desired. Its own File Compare tool shows that differences crop up from the loss of trailing blanks from Notes. Other things are not transferred at all, such as custom default sentences for built-in fact types and roles. The introduction of Ancestry TreeShare motivates one to break out a subset of people from a TreeShared database to work on. I've long thought that a direct database to database transfer should be possible and would be far better than going through the machinations and limitations of GEDCOM, which DnD uses behind the scenes. This is an attempt to do just that, a demonstration of a capability that I wish was incorporated in the RootsMagic program.
The main script copies everything pertaining to a named group in an originating database to a new, empty database. Record numbers are preserved (which is why the target database must be empty).
- You need SQLite Expert Personal or equivalent with a fake RMNOCASE collation extension AND support for run-time parameters (a prompt for the GroupID else edit the SQL statement)
- In RootsMagic and in the originating or master database, create a named group that includes all the people you wish to copy over to the target database.
- In RM, create a new empty target database and record its full path and name.
- Close these databases in RM to avoid any possible conflict with the next steps.
- Open the master database with your SQLite manager and leave it open for all the following SQLite steps.
- Load and run this statement to find out the GroupID for that named group:
-- Find the GroupID for your Group
SELECT DISTINCT LabelName AS GroupName, GroupID
FROM GroupTable JOIN LabelTable ON GroupID=LabelValue AND LabelType = 0
ORDER BY GroupName;
- Load and run this statement to inform the script below which Group to copy.
-- Store that GroupID in a temp table
DROP TABLE IF EXISTS zGroupIDReg
CREATE TEMP TABLE zGroupIDReg
SELECT LabelValue AS GroupID, LabelName
WHERE LabelValue = $GroupID -- for SQLiteSpy, replace $GroupID with the desired GroupID number;
- Load and edit this statement to change the path and name between the quote marks to those of your target database:
ATTACH "C:\Users\Tom\Documents\FamilyTree\RM7\Thomas Mailing families group extract.rmgc" AS Z;
- Load and execute this SQLite script. Group-Extract.sql You will be prompted to enter the GroupID you noted earlier unless you replaced $GroupID with the actual number. Sit back and wait for the message of success (or an error!).
- Close the master database in the SQLite manager to avoid conflict with the RootsMagic program.
- Reopen the target database in RootsMagic and explore.
- Try File Compare against the master database - the people in common should perfectly match except for spouses omitted from the group and File Compare overstating mismatches when there are multiple Alternate Names.
- similar to other reasons to extract a subset of a database:
- smaller, more manageable
- for upload to a website
- for sharing with someone
- for a higher privacy level than for the master
- perfect replication, unsullied by GEDCOM transformations
- complete replication; includes related:
- Research Logs,
- Not a Problem List,
- customised built-in Fact Type and Role sentences,
- custom Source Templates,
- File Settings,
- Report Settings,
- Custom Reports
- TreeShare and FamilySearch connections
- et al
- copy multiple Groups (one by one) from the master to the target (provided the latter has not been edited) without duplication
- script can be modified to expand or contract data to be copied
- very fast