Search – wayfinding from data tables to RootsMagic screens #database #fts #search #rm8

Update 2021-03-11: added version by Pat Jones for RM8.

This page brings together a set of scripts that:

  • adds ‘friendly’ Waymarks in views of each database table to aid in navigating through RootsMagic to the responsible screen and to help understand the role of each table
  • builds a Full Text Search table, first described on the page Search – Find Almost Everywhere
  • launches a Full Text Search with results combining the ‘friendly’ Waymarks and a SQLite statement that opens the table row with the matching cell for possible editing
Waymarks.PNG
Sample of WitnessWay, the temporary view of the WitnessTable with Waymarks added to facilitate navigation to the sharee in RootsMagic.
Each RootsMagic table is replicated in a temporary View named the same except ending with “Way” instead of “Table”. Hence, “WitnessWay” is the wayfinding view of “WitnessTable”. To the right, in this screenshot snippet of the SQLite Expert sidebar, we see:

  • the last three RootsMagic tables, below which are
  • the temporary virtual table for Full Text Search and
  • a one record temporary table that stores the Search Term you enter. Below these are
  • the first few wayfinding views, which, when opened, will show the Waymarks as the first column, followed by all the columns of the table being viewed, as in the WitnessWay screenshot above.

The Full Text Search query returns:

  • a snippet of the text with the match to the search term(s) highlighted by the double-asterisks (**),
  • the Waymarks to help find the snippet in RootsMagic,
  • the field names included in the block of text in which the match was found plus
  • a simple SQL query.

This query can be copied to the SQL editor and executed; then the SQLite Expert editing tool can be used to edit these fields and others in the database table it opens.

WayfindViews.PNG
Snippet from SQLite Expert sidebar showing the Wayfinder views.
Wayfinder_FTS_LocateQuery.PNG
Sample result from the Full Text Search on the term “clerk”.
WaymarksFollowed.PNG
Waymarks for result #3 in the search were followed to this screen snip from the RootsMagic Place List.

Hopefully, the Waymarks list is self-explanatory and can be easily followed through RootsMagic. Editing the database is more safely done in RootsMagic but there are circumstances which can only be addressed through SQLite or the user may prefer doing through SQLite. You are reading this because there is something you want to do with your RootsMagic database that you cannot do with RootsMagic itself!

LocateQuery.PNG
The LocateQuery for search result #3 was copied and executed giving this screen. The fields without the “_1” suffix can be edited (take all necessary precautions). With SQLite Expert, right-click on the field and select Text Editor.
EditTextBlob.PNG
Note and other fields designed for large amounts of text are stored as binary BLOBs and are so indicated by the … icon. The SQLite Expert text editor works with them, too.
SQLiteExpertEditingControls.PNG
The Post Edit button is key to affecting the database.

If you do want to edit your RootsMagic database with SQLite Expert, please read its Help pages under the topics:

  • Editing table data
  • Editing live queries

Always run the SQLite Database Tools before and after you make changes externally and make a backup before, too.

While there are several ways to start an edit, making the changes stick in the database requires a click of the Post edit button, enabled when a change has been made to an onscreen field.

Downloads

ScriptHistorySQLite Requirements
1aRM8_WaymarksViews.sql
Creates the Wayfinding views of the RM8 database tables
2021-03-11 added by Pat Jones for #RM8RMNOCASE
1bRM7_5_WaymarksViews.sql
Creates the Wayfinding views of the RM7.5 database tables.
2017-07-01 extended for RM7.5 and corrected bug for null spouse
2019-02-12 corrected omission of famiies from EventWay and dependent views
RMNOCASE
2FindAlmostEverywhere-Build_FTS_table.sql
Creates the FTS4 virtual table xFTStable for full-text searches.
RMNOCASE, FTS4
3FindAlmostEverywhere-Snippet_Waymarks_LocateQuery.sql
Stores user inputted search terms in xSearchTerm,
searches xFTStable and returns snippets, Waymarks,
field names and the SQL queries to examine the matching
records.
RMNOCASE, FTS4, runtime-parameters
SQLite Expert Personal qualifies

Usage

Script #3 is dependent on the execution of both #1 and #2 preceding its running. #1 and #2 are independent of any other script.

On very large databases, scripts 2 and 3 may consume tens of seconds. In one example 40 and 30 seconds, respectively. However, RootsMagic’s Find EveryWhere took over 180 seconds for the same search and does so for each new search. Script 2 need not be re-executed for each new search by script 3 so there is a distinct time advantage.

All the views and tables created by these scripts are temporary, in-memory and are destroyed when the SQLite manager is closed.

One Reply to “Search – wayfinding from data tables to RootsMagic screens #database #fts #search #rm8

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.