GEDCOM & DnD transfer losses #gedcom

Posted on 2018-07-222024-06-20 by Tom Holden

Background

RM10 reportedly uses direct data transfer between databases for drag’n’drop, not GEDCOM, so it will have different issues, if any. 2024-06-20

RootsMagic Drag’n’Drop between the windows of two databases is a background GEDCOM export-import process. Both Drag’n’Drop and the explicit File>Export and Import process fail to fully transfer everything from one database to another. Drag’n’Drop and GEDCOM are governed by the settings for Export in Lists>Fact Type List so any fact type that is not enabled for “Exporting GEDCOM files” will be lost in transfer. A number of other less obvious losses have been identified Continue reading →

RMNOCASE – faking it in SQLite Expert, command-line shell et al #rmnocase

Posted on 2018-05-182021-01-26 by Tom Holden

SQLite Expert now takes the lead as the most compatible with the RootsMagic RMNOCASE collation, thanks to the C extension unifuzz.dll authored or revised by Jean-Christophe Deschamps. Unifuzz can be used with other SQLite managers that support extensions, including the command-line shell sqlite3.exe but not, regrettably, SQLiteSpy.

This is not simply a renamed equivalent of the SQLite NOCASE collation (see RMNOCASE – faking it in SQLiteSpy); rather, it is a very comprehensive compilation of the unicode character set. Against a RootsMagic database comprising 8091 different Unicode characters in Surnames, PRAGMA Integrity_Check returns 187 missing rowids from indexes idxSurname and idxSurnameGiven with SQLite Expert using this substitute collation. SQLiteSpy returns 16042 missing rowids from the same two indexes including 9 from idxSourceTemplateName with its fake collation. RootsMagic itself reports two missing rowids from each of the two Person Name indexes so there is something wrong even with the genuine RMNOCASE collation.

Download unifuzz.dll, unifuzz64.dll

unifuzz.dll or unifuzz.dll.bak (for 32-bit apps. Remove .bak extension after d/l, added due some systems’ security)

unifuzz64.dll for 64-bit apps, added 2020-03-05, tested with SQLite Expert Personal 64bit 5.3.5

Download and save to the same folder where you have the executable file for SQLite Expert or the command-line shell (or other SQLite manager that supports C extensions).

Using unifuzz.dll with SQLite Expert

Version 3.5

Under the menu item Tools > Options > Show/Hide Features, check the box labelled “Load/Unload Extensions” to reveal these selections in the File menu. The options is saved between sessions.
To load the extension, simply select File > Load Extension and use the resulting “Select extension file” window to browse to, select and open unifuzz.dll. OK the default value “sqlite_extension_init” in the Entry Point window. That’s it! You now have a very (if not perfectly) compatible RMNOCASE collation associated with the database.

Version 4 and 5

The Show/Hide Features option is missing from this version as is File > Load Extension. The only choice is to right-click on the database name in the sidebar to invoke the drop-down menu which includes Load Extension and proceed as in step 2 for ver 3.5. Because unifuzz.dll is a 32-bit extension, it will work with only the 32-bit version of SQLite Expert. Use unifuzz64.dll with the 64-bit version. The Unload Extension function is inoperable and there is no way to tell if an extension has been loaded.

There is a functional difference between the free SQLite Expert Personal edition and the $59 Professional edition. Personal requires that you load the extension for each database, each time you open the database while Pro can be set to automatically load an extension. The free SQLiteSpy can be set to auto load an extension but can only have one main database open at a time while Expert can have multiple databases open.

Using unifuzz.dll with SQLite3.exe

This C extension can be used with the command line shell, which opens up some potentially powerful possibilities with Windows command scripts (batch files). To load the extension, from the SQLite prompt enter the dot command:

sqlite> .load "unifuzz.dll"

If SQLite3.exe and unifuzz.dll are not in the same directory, then the full path to unifuzz.dll must be provided between the quotes.

The shell can be launched to execute a sql script to both load this “RMNOCASE” extension and run sql commands – here’s a simple example. At the Windows command prompt, open on the folder where the database file, sqlite3 and unifuzz are all located, enter the following or open a batch command file with the same content:

sqlite3 "RootsMagic database filename with .rmgc extension" < "sql script filename"

Example SQL script file to be called from the command line:

.LOAD "unifuzz.dll"
 
/*
 demonstrate that RMNOCASE is available by returning list of surnames
 - if not available, an error is returned
*/
 
SELECT Surname FROM NameTable ORDER BY Surname;

Unifuzz.c Source and compiling

unifuzz.c is readily compiled using the gcc compiler, mine came bundled in Dev-C++ Integrated Development Environment. I couldn’t figure out how to tell the IDE what I wanted to do but Jean-Christophe provided the command line for the gcc compiler at line 541 of his source code so I followed that. As the source is written, you want to extract to a folder the sqlite3.h and sqlite3ext.h files from the zip or tar file named sqlite-amalgamation-versionnumber on the SQLite Download Page under the heading Source Code. In a sub-folder below those .h files, place the unifuzz.c file. Then in the command line interface, issue:

pathtogcc\gcc -O2 -lm -shared unifuzz.c -o unifuzz64.dll

The DLL file will be produced in the same folder as the unifuzz.c file.

Discussions & comments from Wikispaces site

unifuzz run time loadable extension for OS’s other than Windows

mooredan
05 September 2017 00:54:38

Hooray! This has been on my ToDo list for a long time and I finally worked on this this weekend. As mentioned above, being able to run queries from the command line opens up a host of powerful possibilities. Unfortunately, the provided DLL above only works on Windows. I run RootsMagic on macOS, but macOS being Unix-like based, the sqlite3 command line tool can be compiled and used. If you try to run a query without accommodating for RMNOCASE, you get the dreaded “Error: no such collation sequence: RMNOCASE” message. Long story short, this is now working on my MacBook Pro. The details can be found here: https://github.com/mooredan/unifuzz . This should work for a Linux system as well. If someone wants a binary, let me know if you don’t want to (or can’t) roll your own.

I hope to integrate this with PHP and GD for creation of some custom web-based charts….

momakid

momakid
20 September 2017 22:14:45

I installed SQLite Expert Personal (5.2 (x64).
I opened my RootsMagic database.
I entered the following in the SQL window:
select surname
from nametable;
I got no such collation sequence: RMNOCASE

I did a right click on my database
I selected Load Extension
I selected the the File Name (C:\Program Files\SQLite ExpertPersonal 5\unifuzz.dll)
I entered sqlite_extension_init for the Entry Point
I left auto unchecked
I clicked OK
I got a popup of The specified module could not be found

Facts – Change Fact Type #facttypes #events #rm8

Posted on 2018-05-062023-03-14 by Tom Holden

Problem

RootsMagic provides no means to change all events of one fact type to another fact type, e.g., convert Residence events to Census events or vice versa. Or convert events for a custom fact type to a standard one. All it offers as of version 7.6.3 is to do so for one person, one event at a time which is laborious and slow.

Solution

A script that converts all events of one fact type to another in a fraction of a second.

Usage

Copy the value of the Abbreviation variable for the Fact Type of interest from the Fact Type List (or from the fact name as seen in the Edit Person screen)

Enter the name of the current event as you see it in the Edit Person screen or as you see it in the Abbrev field of Lists > Fact Type List. Then the value of the Abbrev field for the fact type to which you want the events transformed. When entered, the UPDATE process proceeds.

First of two run-time parameters to be entered to change all events of one fact type to another. Enter the value exactly as found in the Abbreviation field for the Fact Type of interest. Screen clip from SQLite Expert Personal.

Requires SQLite Expert Personal with fake RMNOCASE extension loaded or an equivalent SQLite manager that also supports the entry of runtime variables.

Because this modifies your database, BACKUP FIRST! After executing, you should use RootsMagic Database Tools to check integrity and, if not OK, rebuild indexes.

Scripts

Two scripts are offered: one is for only for a fact type having no shared events and is simpler and faster. The other supports conversion of a fact type that does have shared events; use it if you are uncertain but you can check for shared events with the help of People Who Share a Fact with a Principal List and with People Who Share A Fact with a Principal, But Who Are Not in a Tree in The File List.

2021-12-30: Facts_shared-ChangeType-RM8.sql for #RM8.
Facts_shared-ChangeType.sql for prior versions.
These much more complex scripts than Facts-ChangeType.sql below do support shared events in addition to unshared ones.

Facts-ChangeType.sql This simpler script than the ones above can change the Fact Type of unshared events but not shared events. 2023-03-14: protects shared events from being changed and tested compatible with #RM9. Should be compatible right to RM4.

For the conversion of shared events, the more complex scripts look for role(s) in the target fact type that match the name and sentence template of those used by events of the originating fact type. For those that do not match, a duplicate of the current role is created for the target fact type. Sharers of the transformed event are assigned these existing or new roles. The original role definitions remain intact. Should events be converted back to their original fact type, no new roles will be created provided no change has been made in either the original or target role name or sentence.

Discussions & comments from Wikispaces site

Newbie successfully used this!

anni628
25 July 2017 18:33:32

Have never touched SQLite before today (although I do have a somewhat minimal exposure to programming) but was able to piece together the proper program, with the proper fake RMNOCASE extension, and the above script (after a couple of false starts) and within two hours from start to finish had successfully modified my RootsMagic database that was imported from FTM 2014.

I didn’t like the way the import put the FTM Description fields on major facts (Birth, Baptism, Marriage, Naturalization, Death, Burial, Probate) into Place details so, before the import, I used the built-in FTM option to move all those facts to corresponding temporary facts (TBirth, TBaptism, TMarriage, TNaturalization, TDeath, TBurial, TProbate). Imported the file to RootsMagic and used the above script to move all the data back to the original fact. Worked like a charm. I ran the Database tools after and everything worked splendidly. On 18,165 people tree. Would have taken years to do this manually person by person which is the only option within RootsMagic.

Have spot checked people and everything, including the more complex shared marriage fact is functioning exactly as they should.

Thank you Tom! Wish I had tried it back in Jan of 2016 instead of continuing with FTM but I guess things happen at the time they are meant to happen.

TreeShare – Rename Cryptic Filenames for Citation Media #TreeShare #media #citations #batch

Posted on 2018-04-202022-04-29 by Tom Holden

TreeShare downloads Ancestry media for citations with cryptic file names that mean nothing to humans. This set of scripts and batch or command-line operations prepends to the media filenames the name of one of the persons to whom the citation applies, their BirthYear-DeathYear and the name of the source cited, separated from the cryptic name by the @ symbol. Such information in the file names can help the user organise the downloaded media into folders by surname or even more finely divided. Renaming the files has no apparent effect on TreeShare operations.

A variant of the script could change the order in the new name to be source name first, which would aid in finding and reorganising by source, rather than by person.

Here is a screenshot showing results from a temporary table created by the second script which lists the Ancestry name (OldName) with its new, informative name.

Here is a screenshot of a Media Gallery after the files have been renamed.

Note that the source was cited to four facts or events about the person. and could be cited for multiple facts for multiple people, as, for example, a Census source and image could be tagged to Birth and Residence facts for all family members. However, there is only one image file for that source so the procedure takes the person and event last cited for use in the file name.

As always, use these tools at your own risk. I cannot predict all possible consequences. I saw no adverse effect in my one test case.

READ the comments at the beginning of each script and the instructions at the end, which will also be displayed on completion of the script execution. DO NOT CLOSE THE DATABASE IN THE SQLITE MANAGER UNTIL YOU NO LONGER WISH TO UNDO.

TreeShare-MediaRenameStep1.sql This script can do no harm. It is a variation of RM7_5-WaymarksViews.sql to revise the format and content of some of the Waymarks to suit this application. It generates a set of temporary SQLite Views with which one can inspect the contents of a table and from the Waymark info, navigate within RootsMagic to see where a record is used. For this application, it also generates a temporary table zMediaCitationTags needed for the following script.

TreeShare-MediaRenameStep2.sql Changes the media links so they will appear broken in the Media Gallery until you successfully rename the files with the batch commands it generates.

TreeShare-MediaRenameUNDO.sql Changes the media links back and generates batch commands for changing the media file names back to their Ancestry original.

Possible risks:

A new filename might exceed the limit supported by the file system. Didn’t happen in my test case and would require very long Ancestry file name, source name, person name (couple events would be worst case).
There is a character in one of the names that is invalid for a file name. Didn’t happen.
Some RootsMagic operations may be locked out by having the database open in the SQLite manager and some may lock out the SQLite manager. Avoid doing a backup or using the RM Database Tools while in session on the SQLite manager. I’ve not had a problem while in TreeShare or main views and Edit Person. I’d be wary about going into FamilySearch Central and perhaps having WebHints on.

TMG-RootsMagic Split Citation Details #tmg #citations #sourcetemplates

Posted on 2018-04-202022-04-29 by Tom Holden

RootsMagic 6.3.3.2 imports TMG Citation Detail memo field into CitationTable.Fields as the field [CD] defined in the SourceTemplateTable.Fields regardless of TMG memo splitter “||”. An issue which I’m sure many will detect is that sentences for sources with split Citation Detail (CD1, CD2,..) do not contain any of the Citation Detail. The full value is there in the [CD] field but the sentence templates use [CD1] etc, not [CD]. A fix is to manually edit the Source Template sentence templates to use [CD] in the sentences – there are only 3 or 4 such source templates in the Sample Project – however that changes the order and lengthens the Short Footnote and the || separator remains in the output. A much better result is to add [CD1] et al fields to the source template field definitions and then edit each citation to separate the pieces out of [CD] into the new fields. Unfortunately, every citation needs to be edited – for some users this may number in the thousands or even tens of thousands.

I’m hopeful that the RootsMagician will find the resources to automate this parsing and mapping on import. Meanwhile, here is a SQLite procedure which does the job for those Citations having footnote sentences using [CD1] and [CD2], the most common case where the Citation Detail memo is split into two parts. It splits and replaces [CD] in CitationTable.Fields into the two parts [CD1] and [CD2]. It splits and replaces Citation Detail [CD] in the fields definition in SourceTemplateTable.Fields for the source templates used by these citations. NEW! Script now handles up to CD9…2014-09-30.
.

Citation imported from TMG before running the query. Note the absence of any Citation Detail content in the preview of the Footnote, Short Footnote sentences and the “||” splitter in the Citation Detail value.

After running the query, the sentences contain the values from the split Citation Detail, now replaced by [CD1] and [CD2].

Citations_TMGimport_splitCDtoCD1-9.sql
This query needs the REGEXP function available in SQLiteSpy. but not in SQLite Expert Personal.

As usual, backup first. Be sure to inspect for adverse effects; I have checked various combinations but, obviously, cannot anticipate all the possible variations that can arise from many different users.

TMG-RootsMagic Sentence Variables & Format Codes #tmg

Posted on 2018-04-202022-04-29 by Tom Holden

RootsMagic version 6.3.2.0 and later supports the direct import of project databases from The Master Genealogist. These programs have many similarities and, of course, many differences. They both support witness tags/shared events, sentence templates for facts, footnotes and bibliography and text formatting but names of variables, syntax, codes and database structure differ. Not all can be converted: some codes are stripped and others pass through verbatim. This page offers two resources to help in the resolution of some of the differences in codes between the TMG database and the resulting RootsMagic database.

TMG-RootsMagic Sentence Variables & Format Codes

This document attempts to provide a cross-reference to help in the transition of a TMG database to RootsMagic. The TMG emigre will almost certainly need to edit the imported database or the original to achieve the most complete transfer of data and the cleanest reports and other outputs. Having a cross-reference between their codes with an explanation of their functional similarities and differences should help.

Screenshot of 1 of 17 pages.

Download 1

TMG-RootsMagic Sentence Variables & Format Codes (PDF)

Finding Extant TMG Codes

The following query finds those fields in the imported RootsMagic database that still have embedded TMG codes. A power SQLite user might go on to edit the database using a SQLite manager. A normal RootsMagic user can inspect the results for the passed through codes and use the key characteristics in a RootsMagic Search tool to find them. With the help of the above cross-reference and other yet-to-be-devised resources, either can determine whether action is best taken in the original TMG file and re-import or modify the RootsMagic data. It also may reveal a bug worth reporting to RootsMagic Technical Support.

Screenshot of results of query on the TMG Sample Project imported into RootsMagic. The full content of the highlighted row is displayed at the bottom, revealing the “||” separator in the Citation Detail (CD) field.

Download 2

CheckTMGimportforCodes 2014-09-25 rev to include Event custom sentences; exclude blank values for speedup

This query requires the REGEXP function, available in SQLiteSpy but not the free version of SQLite Expert. If this is your first time using SQLite on a RootsMagic database, please visit the Home page and read How to query RootsMagic and SQLite Managers.

TMG-RM Convert TMG_ID to Reference Number #tmg #refno

Posted on 2018-04-202022-04-29 by Tom Holden

RootsMagic 6.3.2.0 imports the TMG_ID into a custom fact type TMG_ID. This fact type cannot be displayed after the name of the person. Some users
would prefer that it did, either by having it as RIN or by a new feature specifically for TMG. (See also TMG-RM Convert TMG_ID to Record Number)

This procedure makes a clone of the TMG_ID as a Reference # fact which can optionally be displayed after the name. The virtue of both the TMG_ID custom fact and the Reference Number fact is that they are non-volatile when transferred or imported into other databases, unlike RIN.

Because multiple REF# facts are allowed for each person, only the REF# with the lowest record number in the EventTable is displayed. Therefore the procedure also relocates pre-existing REF# facts to the end of the table.

N.B. Be sure to close the database from RM before running this procedure
Otherwise, for some arcane reason, adding most any fact edits one of these new REF# facts, until the database has been closed and reopened.
Probably, some last record number register in the program has to be updated.

Sample project imported from TMG after running SQLite procedure. TMG_ID now displayed on the main screen via option to use Reference Number after the name for displays and reports. The Sidebar only shows RIN or no suffix.

TMG-RM_convertTMG_IDtoREF.sql

TMG-RM Check for False Spousal Event Sentences #tmg

Posted on 2018-04-202022-04-29 by Tom Holden

TMG has dual Principal, non-spousal events that RM does not support. Direct import converts these events to Individual type and variables of
Spousal type may get through, i.e., [Spouse], [Husband], [Wife], which are invalid for a RM Indiv event. The name of the variable is outputted instead of nothing when it is unrecognised for a type of event.

This query finds occurrences of these spousal name variables in the Sentence fields of the FactTypeTable, EventTable, RoleTable and WitnessTable. With the name of the table and its ROWID, one could edit the sentence directly from the SQLite manager. The Name of the record where the sentence is defined and the Where value will help find the sentence using RootsMagic to edit and inspect results of changes.

TMG-RM_FalseSpousalEventSentences

Search – Find Almost Everywhere #search #fts

Posted on 2018-04-202022-04-29 by Tom Holden

RootsMagic 6 has a very powerful search tool under the main menu item Search > Find Everywhere… However, it really falls short of everywhere, omitting a number of tables and fields within the tables it does search. Notably, it omits the:

AddressTable (Repositories, Contacts, Correspondence)
FactTypeTable (Fact Types)
SourceTemplateTable (Source Templates)
URLTable (WebTags)
WitnessTable (non-Principals of shared events)

This family of scripts query searches these tables in addition to all other tables which have fields into which the user may enter text. RootsMagic’s Find Everywhere has the advantage of searching within expanded sentence templates for events and footnotes and hyperlinking to the editable field in which the search term found a match. These Find Almost Everywhere queries cannot expand sentence templates and hyperlink but have wider scope; their results give guidance to the power SQLite user to locate the table and record within which its search term found a match. Moreover, their search expressions can be more complex and powerful. However, for user-friendliness, RootsMagic’s implementation is very superior. Also see Search – wayfinding from data tables to RootsMagic screens.

Requirements

SQLite Expert Personal with unifuzz.dll extension for fake RMNOCASE or an equivalent which also supports entry of the search term at run-time and the FTS4 extension.

Usage

Open the RootsMagic database in the SQLite manager.
Load the SQL script into a SQL tab and execute (F5 in SQLite Expert).
Enter your search term(s) in the Missing Parameter window.
Entry of search term for Find Almost Everywhere query.
Search results will be displayed in the results pane.
If you find that re-executing the script is time-consuming, for further queries, select/highlight the SELECT statement near the bottom of the script and execute it alone. What takes time in large databases is the building of the virtual table of texts that is required for full text searches.
If you edit a cell using the SQLite Manager, be sure to use RootsMagic’s Rebuild Indexes tool when you return to working with it.

Sample result from Find Almost Everywhere Simple Search query. One can go directly to the identified rows in the MultiMediaTable to examine further or edit directly using the SQLite manager OR use the clues to find the item in RootsMagic’s Media Gallery.

Downloads

FindAlmostEverywhere.sql	Builds Full Text Search table and index and provides a simple search. rev 2014-10-23 made FTS table temp for duration of SQLite session rev 2014-10-24 replaced collective name like “Various” with explicit field names in FieldName column
FindAlmostEverywhere-Build_FTS_table.sql	2014-10-23 Split from above to Build Full Text Search table and index only. Use one of the following separate scripts to Search so table is not rebuilt every time.
FindAlmostEverywhere-Search_Simple.sql	2014-10-23 Simple Search split out from FindAlmostEveryWhere.
FindAlmostEverywhere-Search_LocateQuery.sql	2014-10-23 Search and Locate returns the content having a match along with a SQLite statement which can be copied to the SQLite editor and executed to open and edit the actual cell. rev 2014-10-24 ROWID added to the generated SELECT; highlighted snippet instead of full content.

A result from FindAlmostEverywhere-Search_LocateQuery.sql on the search term “marr”. Copy the SELECT statement from the LocateQuery column to the SQLite manager and execute to open and edit that cell directly.

FindAlmostEverywhere-Search_LocateQuery-result of LocateQuery.PNG

Result of 2nd LocateQuery from above. As this query presents the raw fields from but one table, the fields can be edited. Select a cell and press F2. However, be very careful; it is safer to edit from within RootsMagic.

Search Terms Syntax

Find Almost Everywhere uses the SQLite 3 FTS4 extension for Full Text Search with its MATCH operator working through the index of the virtual table the script creates from the database. The syntax of the <full text search expression> you may enter as the value for the run-time parameter $SearchTerm is described in the section Full text Index Queries at http://www.sqlite.org/fts3.html but excluding §3.2. Set Operations Using The Standard Query Syntax.
In summary (the <…> brackets are not to be entered):

case insensitive except operators MUST be capitalised, else they are just terms
wildcard character: * , e.g., <joan*> matches Joan, Joanne, Joanie, Joannie
phrase: enclose in double quotes, e.g., <“Alexander Barnes”>
NEAR, e.g., <sql* NEAR query> matches “SQLite database query”, “sql is a powerful query”… The distance between the matching terms is by default a maximum of 10 intervening terms but can be overridden as NEAR/n where the integer n sets the maximum distance, e.g., NEAR/2 in the foregoing expression matches only the first of the two examples.
NOT, AND, OR, operators in the foregoing default order of precedence which can be overriden by grouping in parentheses, e.g., <(carrie OR lula) AND barnes>
implicit AND: <carrie barnes> is the same as <carrie AND barnes>
narrowed scope: prefixing the term with the name of the virtual table column restricts the match for that term to that column; normally, the search traverses all columns. <FieldName:description> restricts matches for the term “description” to the column named “FieldName”.

Alternative Structures for the Virtual Table

The FTS MATCH operator looks across all columns of the virtual table for a match to its expression, i.e., one term can match in one column, another term can match in another column; the expression need not be matched by any one column unless prefixing with the column name forces it to do so. Any combination of sub-matches in different columns can result in a full match. Given a source table having multiple text columns, one can choose a vertical or horizontal layout of the virtual table or some hybrid of the two. For example, the WitnessTable:

Vertical layout

Content	FieldName	TableName	Rownum
ghjg	Sentence	CitationTable	7
yyoyu	Note	CitationTable	7
dfhd	Given	CitationTable	7
tyit	Surname	CitationTable	7
dfghd	Prefix	CitationTable	7
yuoy	Suffix	CitationTable	7

Horizontal layout

Sentence	Note	Given	Surname	Prefix	Suffix	TableName	Rownum
ghjg	yyoyu	dfhd	tyit	dfghd	yuoy	CitationTable	7

Hybrid layout

This is the hybrid layout used by the Find Almost Everywhere script because it started from the script in TMG-RootsMagic Sentence Variables & Format Codes.

Content	FieldName	TableName	Rownum
ghjg	Sentence	CitationTable	7
yyoyu	Note	CitationTable	7
dfghd dfhd tyit yuoy	extWitness	CitationTable	7

“extWitness” = Prefix Given Surname Suffix)

Comparisons

The vertical layout cannot make a combination match among the fields from the WitnessTable because they are in separate rows in the virtual table. It is inherently a narrow search. The horizontal layout is the widest potential search for a match from the WitnessTable because sub-matches can be made among all its text fields. The hybrid was built on the vertical layout by concatenating the name fields for the external Witness (person not in the database) so it can match in the Sentence, Note OR extWitness content but not in a combination of these.

Further Considerations

This script populates the Content column with values from one table at a time. Search scope could be widened by bringing in content from a JOINed table, e.g., CitationTable and SourceTable.

Another possibility is to link the search results via the TableName and Rownum values to more helpful guides to the Person, Event, Citation etc. That is where the RootsMagic implementation shines with its hyperlinking straight from the result to the corresponding edit screen. See Search – wayfinding from data tables to RootsMagic screens for a FTS query that provides waymarks to help navigate to the RootsMagic screen that controls the data in the matching fields.

Discussions & comments from Wikispaces site

Geraniums

RMNOCASE question

Geraniums
23 October 2014 12:13:46

Hi, I was going to try the FindAlmostEverywhere search tool and installed SQLite Expert Personal 3.5.60.2480. I loaded a RootsMagic 6 database, then the FindAlmostEverwhere.sql. After clicking the Execute SQL button in the SQL tab, a message came up that said, “An error occurred. no such collation sequence: RMNOCASE” etc.

I have downloaded the file RMNOCASE_fake-SQLiteSpy.dll
and it’s in the same folder as the SQLiteExpertPersSetup.exe file.

In SQLite Expert Personal, under Tools > Options, I don’t see where I can register the extension RMNOCASE_fake-SQLiteSpy.dll

Hoping someone can help. Thanks

ve3meo

ve3meo
23 October 2014 12:19:22

The extension you want is unifuzz.dll, see RMNOCASE – faking it in SQLite Expert, command-line shell et al

Tom

Geraniums

Search questions

Geraniums
23 October 2014 15:39:46

1) After getting search results, is there a way to determine which person in RM6 that the record refers to?

2) In the directions for using the search tool it says, “If you find that re-executing the script is time-consuming, for further queries, select/highlight the SELECT statement near the bottom of the script and execute it alone.” I can’t see the SELECT statement on the SQLite Expert Personal screen.

3) When a record is selected, if I type into the Content field, will this change the content in RootsMagic6?

Thanks

ve3meo

ve3meo
23 October 2014 16:46:51

1) Yes, if you know your way around the database. For example (and perhaps a trivial one), the search result indicates a match in the Note field of EventTable at Rownum 1234. Click on EventTable in the sidebar and the Data tab in the main view. Scroll down to RecNo/EventID 1234 OR click on the row “Click here to define filter” and enter “1234” under either the RecNo or EventID heading. Look under OwnerType and OwnerID. If OwnerType=0 then OwnerID is the Person with that RIN and you can look them up in RootsMagic Explorer (Ctrl-F, Alt-R). If OwnerType=1, then the OwnerID is the FamilyID in FamilyTable and you will need to look in it to get the RINs of the couple.

I have some intention of providing a more user-friendly result to save much of that manual navigation…

2) Click on the SQL tab. Pull down the divider between the SQL pane and the results pane to expose more of the script and use the scroll slider to get to the bottom of the script (or use other Windows techniques). Look for:
—–repeat the following query as often as you want—–
—————- Full Text Search query —————–
SELECT * FROM xFTStable WHERE xFTStable MATCH $SearchTerm
;

3) No, you cannot edit the results of a query. You can edit a cell displayed under the Data tab. In our example above, having found EventID 1234 in EventTable, you can double-click on any cell in that row to open the Record Editor. Scroll down to the Note heading which will have 4 buttons Auto|Blob|Image|Text. Click on Text to edit the Note.

Tom

ringozmy

Person Search According to the Place of Residence

ringozmy
28 June 2017 23:59:22

Hello,

I have a list of 10K individuals and their place of residence, and I would like to retrieve their personal information (gender, race, job, age, etc.) from 19-century US censuses. Can I do it through SQLite for RM? Could you give some suggestions? Thanks!

ve3meo

ve3meo
29 June 2017 01:31:34

Do you have in your database Residence, Race, Occupation and any other facts you wish to retrieve for these people?Or are they just in the Citation text for the person and the citation is tagged to a Census event?

RMpi GEDCOM Pre-Import Tweaker for RootsMagic #gedcom #tmg #application

Posted on 2018-04-202022-04-29 by Tom Holden

This is NOT SQLite! Rather, RMπ (RMpi) is a little application that tweaks a GEDCOM file you want imported into RootsMagic for potentially more complete data transfer. More development may come later but it could be useful now, especially to early emigres from The Master Genealogist 9.0.3 and earlier.

Perhaps equally importantly for exchanges between RootsMagic databases, it works around the serious issue of truncation at the 100th character of fact Descriptions that happens both for drag’n’drop and for GEDCOM import in RootsMagic 6.3.1.4 and earlier. Export does not truncate or, at least, not so early.

Main screen on opening. Once you have selected a GEDCOM file, the Tweak button is enabled…Click it.

Live log window displays tweaks as they happen.

Download

RM_pre-import 103.zip Download and extract all files to the folder of your choice. Click on “RM_pre-import.exe” to launch the program.

RM_pre-import 104.zip This version parses TMG Place values into RootsMagic Place, Place Details and Place Detail Notes but takes a major setback in speed.

Tweaks

For brevity, the RM_pre-import utility will be referred to as RMpi. RootsMagic behaviours described are those of v6.3.1.4. The Master Genealogist behaviours are those of v9.0.3.

NEW in 1.0.4: TMG Place values parsed into RootsMagic Place, Place Details and Place Detail Notes.
NEW in 1.0.3: Modifies HEADer block so that RootsMagic will offer the “Preserve record numbers” option on importing into a new database; line counter to show RMπ is working.
NEW in 1.0.2: RMpi now modifies Witness tags generated by Thomas Giammo’s WitnessTMG utility to create one custom “*Witness” fact type in RootsMagic. Use these to refer back to your TMG database and decide whether to create a shared event in RootsMagic, edit the *Witness event or leave it as is.
RootsMagic truncates GEDCOM event/attribute in-line values at the 100th char and does not recognise subsidiary CONC|CONT lines. TMG exports unlimited length Memo fields to certain GEDCOM event/attributes. RootsMagic itself does not limit the size of entries in its event Description fields and exports them fully. Its own drag’n’drop process uses its GEDCOM export/import processes in the background, resulting in truncation of long event descriptions. RMpi converts the excess to the EVENt NOTE which RootsMagic imports without constraint.
RootsMagic does not expose Notes, Citations and Media for the primary name of a person, the first NAME exported for the INDIvidual. RMpi shifts their level up so that they become exposed as general Notes, Citations and Media in RM.
RootsMagic does not import street addresses from the ADDR tag and a subsequent CONT tag, only from ADR1 and ADR2. In the absence of ADRn tags, RMpi adds them with the values from ADDR and the first subsequent CONT.
RootsMagic ignores the UID tag from Ancestry.com which may have been uploaded from a RootsMagic GEDCOM as a _UID tag. RMpi changes the UID tag to _UID which can then enable RootsMagic ShareMerge to identify and merge identical persons.
RootsMagic ignores the _APID tag from Ancestry.com which is key to finding a source in the Ancestry database. RMpi places the _APID value within privacy braces in the Master Source Comment or Citation Comment (GEDCOM NOTE tags) from which it can be manually retrieved or, perhaps, some future process on the RM export will regenerate the _APID lines from the NOTEs (maybe RMpe or RMpx 😉
RootsMagic ignores the Ancestor Interest (ANCI) tag and, likewise, the Descendant Interest (DESI) tag. RMpi converts them into EVENts of corresponding TYPEs and the value (currently a reference to another line in the GEDCOM) is stored in privacy braces in the NOTE, e.g. “{@SUB1}”. One can then look up that reference in the original GEDCOM and use RootsMagic Search and Replace to replace it with the interested person’s name. A future enhancement of RMpi might fetch the name.
RootsMagic supports a DATE for INDIvidual NAME tags, which is not consistent with GEDCOM specs, but allows Alternate Names to be dated similarly to events. There is talk of the use of a custom _DATE tag from other software to respect GEDCOM. RMpi converts _DATE to DATE so that RootsMagic will import it.
RootsMagic ignores the citation quality tag QUAY under some conditions not fully understood. RMpi stores the QUAY value in privacy braces in the NOTE for the SOURce value unless the NOTE already contains “Surety”.
RootsMagic treats a line 1 DEAT Y as simply a flag indicating that a person is deceased, not as an event, and ignores all subsidiary information. If there are subsidiary lines such as a NOTE, RMpi revises it to “1 DEAT”, without the “Y” so that RootsMagic imports the subsidiary values.
TMG exports under some conditions a citation (SOUR) tag under a NOTE which RM does not recognise; such citations appear to duplicate those for the parent events. RMpi deletes the unrecognised citation block.

Tips

Matching ID numbers between source database and RootsMagic

Be sure to check the option “Preserve record numbers” in the RootsMagic GEDCOM Import dialog. This option appears if the trick introduced in v1.0.3 works and only if it is a new empty RootsMagic database. It has been tested with TMG v9.03 and results in the TMG Reference field REF_ID, which is exported to the GEDCOM INDI record number, traversing to the RootsMagic record number (RN or RIN). Then, after importing, under the RootsMagic Tools > File Options > General choose “Record number (RIN)” from the drop-down listbox labelled “Number to display after name”.

RootsMagic GEDCOM Import dialog, showing the option “Preserve record numbers” which should be checked; by default it is unchecked.

RootsMagic File Options, found under the Tools menu, set to display the Record number (RIN) after the person’s name in most screens and reports.

TMG Export Settings

Export Reference field as: Reference (REFN) if you want to have the Reference number preserved in a RootsMagic fact, immune to changes in its Record Number. RMpi 103 does result in RootsMagic offering the option of preserving the TMG Reference number as its Record Number, without this setting, but the RN will change if a person is transferred to a non-empty database.

Exclusion: all checked for maximum data transferred.

Maximum GEDCOM line length: not critical. RootsMagic 6.3.1.4 and below truncates some event/attribute values at 100 characters; RMpi converts the excess to Notes.

Uncheck “Break long values between words” to preclude word concatenation.

Places: Place Levels: These settings are required for RMpi 1.0.4 or for a special fork of RMpi so that it can properly slot the TMG place fields in RootsMagic Place List fields Place, Place Detail and Place Detail Note. The GEDCOM will have lines such as:

2 PLAC , -Erwin Parsonage, Erwin, Unicoi County, Tennessee, , , , ,

The TMG Detail “-Erwin Parsonage” will end up in Place Detail while City, County, State, Country will be sent to Place. The rest of the values will go to the Place Detail Note. This can only work if there are 9 commas in each PLAC, hence all Place Levels are checked along with “Commas When Missing” and we do not want leading and trailing commas to be trimmed. Undoubtedly there will be cleanup to do in RootsMagic. Here is an example of the result from RMpi104 of an import of the TMG Sample Project:

History

2014-08-31 1.0.0 Published
2014-08-31 1.0.1 Enclosed ANCI/DESI values in NOTEs in privacy braces, e.g.: “{@SUB1@}”, because RootsMagic otherwise ignored it.
2014-09-01 1.0.2 Modifies Witness tags generated by Thomas Giammo’s WitnessTMG utility to create one custom “*Witness” fact type in RootsMagic
2014-09-02 1.0.3 Modifies HEADer block so that RootsMagic will offer the “Preserve record numbers” option on importing into a new database; line counter added
2014-09-06 1.0.4 TMG Place values parsed into RootsMagic Place, Place Details and Place Detail Notes; Live Log revised to use codes for higher speed.

Issues

Platform

RMπ was developed on the free but dated JustBasic 1.01 (website). It is not the most robust development system – much time wasted when it was corrupted by something – but a procedural language such as GW-BASIC or QBasic seemed to be appropriate for GEDCOM manipulation. And I thought the learning curve would not be so steep. Perhaps I should have tried to do it in Visual C#, which is what I used for RMtrix but I am now very rusty with what I had learned then.

Speed

Prior to v1.0.4, RMπ processed some 2000 GEDCOM lines per second on a vanilla Windows 7 laptop. The same file on RMπ 1.0.4 drops to under 1700 lps but is one that does not invoke TMG Place parsing; a small one that does knocks the processing speed down to ca 1000 lps. Replacing the Live Log with a Log file may recover some speed. However, speed is not necessarily an issue if the process is used only once (i.e., the results are acceptable).

Live Log

Not only does the Live Log affect speed of operation, its copy to clipboard capacity is small. It would be beneficial to write instead to a file which can then be opened by a more capable text editor. Moreover, the same text editor can have the original GEDCOM open with which the log directly relates via the Line Number. And the RMπ output GEDCOM can also be opened and related to the original by references such as the INDI number.

TMG Place Parsing

This may not prove to be generally reliable due to the flexibility that TMG offers and the variety of ways that users may populate its Place fields. The parser relies on a specific export setting causing a certain number of commas to be embedded in the exported PLAC value. This is of no consequence for fields lower in the list than Country but any values with commas in them at or above Country will throw it off.