Bundled Utilities – Groupings #application

This page is intended to group existing SQLite queries and future functions
under a menu structure that parallels that of RootsMagic itself, with the
idea that similar naming, order and, possibly, locations on screen may
facilitate user navigation to the utilities most relevant to their area of
interest or concern. This is a first draft.

Rmutils(working name)January 10, 2012
Main Menu5:55 PM
SQLiteToolsForRootsMagic query or other description
SortMainSub1Sub2Query NameDescriptionPageComment


1.1FileOpenOpen Windows Explorer browser on *.rmgc files.Get database version, possibly do SQLite Quick Integrity Check
“PRAGMA quick_check(integer);”
1.2FileOpen Recent
1.3FileSearch for Files
1.5.2FileCopyLess PeopleCustomDatabaseShellMakes an empty Master from current database, preserving custom
fact types and source templates, Places, Master Sources and
associated Repositories and Addresses, and Place/Source Media
but keep Customs, Places, Sources
1.5.3FileCopyTo vanilla DB3RM#_CREATE_as_DB3_NOCASE | RM#_Copy_Data_to_DB3_NOCASEClones a RM4/5 database without the RMNOCASE collation so that
we’re free to do what we want to the data. Even works with RM but it
might trip up on non-English alphabets.
Database to NOCASE
1.6FileDatabase tools…
1.6.1FileIntegrity checkCheck RootsMagic Database IntegrityExplains why an integrity check is needed and how, using PRAGMA
RootsMagic Database Integrity
1.6.1FileIntegrity checkPRAGMA integrity_check(integer);
1.6.3FileCleanTBDDeletes unused rows from tables as reported by RMGC_Properties.
May need checkbox options to select what tables are cleaned. RM4
& 5 are different
Conceivably, Properties & Clean could be one form with
checkboxes for tables that have unused rows & a Clean button
1.6.3aFileCleanDelete Phantom CitationsDeletes citations of non-existent Sources from database that
manifest themselves in the Edit Persons screen as a citation
(counted and checkmarked) but return nothing when opened.
Phantom Citations – Query
1.7FileExportTBDpossible route to a control of Fact settings for GEDCOM export
1.8.1FileDatabase file only
1.8.2FileInclude mediaRMfullbackup.batBacks up the database file along with all the media files
referenced by it to one file. Uses the command versions of SQLite3
(free), WinRAR ($fee) and 7-Zip (free). The 7-Zip version produces a
.rmgb file that RootsMagic can restore, media and database.
Media with Database – RAR
1.8.2FileInclude mediaRMfullbackup.batBacks up the database file along with all the media files
referenced by it to one file. Uses the command versions of SQLite3
(free), WinRAR ($fee) and 7-Zip (free). The 7-Zip version produces a
.rmgb file that RootsMagic can restore, media and database.
Media with Database – 7Zip
If there is Backup, should there be Restore from within Rmpotions?
1.9FilePropertiesDatabase PropertiesReports in a list most of the values found in RM4’s File >
Properties report; results can be exported to a file.
Properties List – Query
1.9FilePropertiesRMGC_PropertiesAs ‘Database Properties’ but with more detail and pointing out
possible problem areas
– Query
Variant for RM5 TBD
1.9FilePropertiesRMGC_TablesRowCountLists count of rows in each table — If not 22 tables listed,
database has corruption
RMGC Tables
Row Count


2.1.1EditPersonBirth YearBirthYearMisMatchLists Individuals whose Birth Year from NameTable does not match
that of the date for their Birth fact.
Birth Year
2.1.2EditPersonDeath YearDeathYearMismatchLists Individuals whose Death Year from NameTable does not match
that of the date for their Death fact.
Death Year
2.1.3aEditPersonSet LivingSetLivingFlagAnother version of a Set Living query.Another
version of a Set Living query
2.1.3bEditPersonSet LivingSetLivingFlagA discussion of queries that can modify the Living flag.Set Living Flag
2.1.4EditPersonSame day sort orderSortDateSameDayOrder | SortDateSameDayOrderCustomRe-orders all same day events of limited types to a natural order,
e.g., Birth before Death
Dates –
Same Day Sort Order
2.1.5EditPersonCopy fact to groupCopyFact2GroupCopies a fact/event for a person to a Named Group of persons,
along with the Sources but not the Media for the Fact.
Copy Fact to
2.1.6EditPersonCopy RIN to REFNCopyRINtoREFNVariant of CopyFact2Group that copies a REFN fact to a group,
substituting the target persons’ PersonID’s or RIN for that of the
source person.
Copy RIN to REFN
2.1.7EditPersonConvert sharable factsSharableFacts2 | SharableEvents-ConvertList and convert events having key properties in common to shared
events. Especially useful on imports from Legacy Family Tree in
which events were copied to multiple persons.
Events – Find and Convert to Shared


3.1ListsSource List
3.1.1ListsSource ListSource ListSource ListAbout as close as we can get to a RM4 Source List Report presented
in tabular form with which the results can be sorted and filtered
with relative ease.
Source List Query
3.1.2ListsSource ListSource DetailsSource Detail View (Parsing XML)Lists Source Names along with Source Detail field names;
illustrates principles applicable to Master Source fields and other
XML-like columns.
Detail View (Parsing XML)
3.1.3ListsSource ListCitationsAllCitationsLists all citations in the database from which citations of
non-existent sources (‘phantoms’) and citations for non-existent
events or persons (‘headless’) can be found, along with other useful
information such as all citations per source.
All Citations –
3.1.3ListsSource ListCitationsAllCitations+DupesLists all source names cited for a person in descending order of
the count of duplicate citations – a help in finding and resolving
duplicate citations after merging.
Citations & Dupes Count – Query
3.2ListsTo-Do List
3.2ListsTo-Do ListTo-Do (was To-Do4Persons)Lists uncompleted To Do tasks for Individuals, Families and
General; can be readily modified to filter or sort by surname,
priority, repository, etc.
RM4 To Do List
3.3ListsResearch Manager
3.4ListsMedia Gallery
3.4.1ListsMedia GalleryDuplicatesMediaRepairSeries of queries to list duplicate media file names in the Media
Gallery and list duplicate links to items in the Gallery. Repairs a
specific case of duplicate file names as an example; repairs all
cases of duplicate links.
Media Repair
Needs rev for RM5
3.4.2ListsMedia GalleryList usesMedia Users ListComplete listing of all users of media in the Gallery, more
complete and navigable than what is provided in RM
Media Users
List Query
Needs rev for RM5
3.4.3ListsMedia GalleryFiles usageScrapBookFilesStatusLists files under the RM Multimedia default folder and flags those
used by the RM database; helps to ensure that files are used.
Files Status
3.5ListsAddress List
3.5.1ListsAddress ListBlank Namesblankname_in_addresslistList Persons with Blank Names in the Address List — (a fault that
may occur in a GEDCOM import).
Four Little
3.6ListsRepository Manager
3.7ListsCorrespondence List
3.8ListsPlace List
3.8.1ListsPlace ListUnused PlacesUnusedPlacesList of unused PlacesFour Little
3.8.2ListsPlace ListPlace DetailsPlacesDetailsLists Places having Place DetailsFour Little
3.8.3ListsPlace ListConvert to Place DetailsPlaces to Place Details ConversionCombo of SQLite queries and RM4 edits of Place List beats having
to edit every fact/event to split a Place into Place and Place
detail; otherwise, wait for the RootsMagician!
to Place Details Conversion
3.8.4ListsPlace ListPlace Details with FactsFactsHavingPlaceDetails3Returns all Places with Place Details and the Persons or Families
(couples) and their Facts, including Shared events, using the Place
Having Place Details – Query
3.9ListsFact Type ListManage, save, apply Fact settings for Export, Reports, Privacy
3.AListsSource Templates
3.A.1ListsSource TemplatesSources by TemplateSourceTemplateListWithCitationDetails2.sqlLists Sources associated with Source Templates, as well as an
extension to Citation details. Free-Form templates are excluded
since they’re not included in SourceTemplateTable.
Template List – Query
3.A.2ListsSource TemplatesMake EditableSrcTmpltsConvertConverts Sources using uneditable, built-in templates to using
editable copies.
Source Templates
3.A.3ListsSource TemplatesRevert to Built-inSrcTmpltsRevertReverts Sources modified by SrcTmpltsConvert to using uneditable,
built-in templates.


6.1SearchNameNameFindProduces a list of names that match or sound like specified names,
similar to RM4’s NameFind.
Name Find query
6.2SearchSurnameselected_surnamesList Persons with specified Surnames. — Example of creating a SQL
View or Virtual Table and the explicit use of COLLATE NOCASE to
override the RMNOCASE collation defined for certain fields and
embedded in the RootsMagic application.
Four Little
6.3SearchSearch & ReplaceSearch&ReplaceDiscussion and examples of how you can search (filter) using any
SQLite manager and replace found values with revised ones using
Search &


7.1.1ReportsFormatParagraphingParagraph-Strip | Paragraph-AddRemoves CR/LF characters around custom fact sentences and
person/family/alt name/fact notes and adds CR/LF pairs to ends of
notes except the last, for first cut batch paragraphing.
7.1.2ReportsFormatStrip footnote line feedsFix Extra Line Feeds in FootnoteThis query strips extraneous Carriage Returns from the end of the
Footnote sentence template for the Source Template “Vital Records
(state-level, online derivatives)” which cause unwanted white space
in reports.
Source Templates
7.2ReportsMy Lists
7.2ReportsMy ListsAll factsAllFacts4PersonsList all the Individual, Family and Shared Facts/Events for all
persons in a database. A complex query using UNION ALL, COUNT() and
GROUP BY, constants and NULL to assemble multiple SELECTs in one big
Together All the Events for An Individual
7.2ReportsMy ListsBegattersPersonsBegatChildrenAn optional extension to AllFacts4Persons or standalone. Having a
Child as an event for the Father and the Mother.
of children as facts
7.2ReportsMy ListsDirect AncestorsAncestorsLists all the ancestral lines for a given RINAncestors Query
7.2ReportsMy ListsDNADNA_mtDNA_locationslist | DNA_Y-STR_markerslistTwo queries that list the test results in a format suitable for
easy review and copying and pasting into other applications.
RM4.1.1.4 provides no DNA report.
DNA Test
results list
7.2ReportsMy ListsFacts shared with persons in databasePeople Who Share a Fact with a PrincipalLists people who share a fact with a Principal, as well as
relevant fact and Principal information.
Who Share a Fact with a Principal List – Query
7.2ReportsMy ListsFacts shared with persons NOT in databasePeople Who Share a Fact with a Principal, But Who Are Not in a
Tree in the File
Lists people who share a fact with a Principal, but who aren’t in
a tree in the database file, as well as relevant fact and Principal
Who Share a Fact with a Principal, But Who Are Not in a Tree in
the File List – Query
7.2ReportsMy ListsGeo-LifelinesGeo-LifelinesHave you ever wished to be able to look at all the facts in your
family tree database that happened within a day’s horseride of a
certain location? This adaptation of the LifeLines query helps you
view your events for any geographic area in addition to looking at
the lifeline of any person in your database.
7.2ReportsMy ListsLifeLinesLifeLinesLists all events for all persons whether in a database tree or
not, including shared facts, date, fact detail, site and place,
MRIN, other parties, and duplication indicator. Sorted by RIN and
Sort Date. With the right SQLite manager, can filter results for one
LifeLines – Query
7.2ReportsMy ListsMap Events (KML)MapEvents-KMLThis query helps you plot events from your RootsMagic database on
Google Maps, Google Earth, and Bing Maps, provided there are
geo-coded Places and Place Details (sites) in your database.
7.2ReportsMy ListsMultiple spousesMulti-spousesLists persons with multiple spouses in descending number – may
flag a data problem
Spouses query
7.2ReportsMy ListsNameless witnesses to shared factsTraceHeadlessWitnessesReport on the role, event and owner or principal of the shared
event for which a witness or sharee is no longer a person in the
Events With Missing Witnesses
7.2ReportsMy ListsTo-Do GridMyToDoListA Query for a To Do List in a Grid Format – using the REFN to
assign status for paragraphing and census. Versions for both SQLite
directly and MS Access via SQLODBC.
Query for a To Do List in a Grid Format


Hmm Tools within a Tool
8.1.1ToolsMergeFast Duplicate Name SearchDuplicateNameSearchLists duplicate name pairs with a weighted score indicating degree
of match. On a large database, produces results in 4.5 minutes
similar to what RootsMagic 4’s Duplicate Search Merge tool does in
45 min.
Name Search – query
8.1.2ToolsMergeMark unmerged not a problemMarkNotProblemCopies all unmerged pairs from the results of RM4’s Duplicate
Search Merge Tool to RM4’s ExclusionTable so that they will not
reappear in subsequent runs of Duplicate Search Merge.
Search Merge Database


Refresh – variousTop page for a collection of queries for refreshing Named GroupsNamed Group
G.1GroupsAncestorsAncestorsGroupProvides a manual refresh for a specific Named Group, i.e., the
ancestors of a specified person.
Named Group
G.2GroupsCensus neededCensusNeededGroup | CensusNeededGroup2Builds or refreshes a group of persons whose lifetime probably
spanned a user-defined Census Year and who had some event in the
user-defined jurisdiction but not a Census fact for that year.
Needed – Named Group
G.3GroupsMark/Unmark ListsGroup Unmark List Refresh | Group Mark List RefreshMarks or Unmarks members of a group according to a list;
equivalent to memorising the checkboxes in RootsMagic Explorer for
Group – Mark or Unmark List refresh


DateDecoderDecodes most of the possible formats found in RM4 Date fields of
the form Da+nnnnnnnn.x+nnnnnnnn.x
Date Decoderencode as SQLite function
zFnfunctionsSortDateEncodeDecodefor encoding single, pure dates and decoding corresponding
SortDates (no modifiers yet like bef, aft)
Dates –
SortDate Algorithm
encode as SQLite function

Discussions & comments from Wikispaces site


Functions by Table modified

January 2012 15:37:45
Warning: I don’t know what all of the current queries are, I
haven’t taken the time to look at the long list of stuff, it’s
currently too long and unstructure to make me believe that
it’s worth my time. In that, I’m probably like most folks who
come here, and am the kind of person this project is intended
to help.
Just be aware, my suggestions have NO relationship to whatever
work has already been done, just a relationship to my prior
program (TMG) and what I needed from a utility program, and
seem to still need from an RM utils program.
The utility program itself should have the standard
File/Edit/View/Tools/Help structure.
Users will come to the RMUtils program why? To either GET INFO
from their database or to CHANGE their database.
The pertinent areas are:
names, events, places, sources, repositories, media, others?
I absolutely see a structure similar to that of TMG being the
most intuitive for users.


January 2012 03:40:31

I have activated a Query Menu in the sidebar linking to a
new Query Menu page providing something of a structure that
hopefully helps newcomers navigate to a query of interest.

Leave a Reply

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