Addresses | Correspondence | Database | Edit | Facts | Fact Types | File | Functions | Groups | Media | People | Places | Reports | Repositories | Research | Search | Sources | To-Do | Tools
This page lists the tools sorted by category. Another way of finding any pages, not just query pages, is by the tag cloud at Find Pages by Tag. To view the most recent tools, have a look at RM SQLite Queries which is sorted by Date Added.

SubMenu1
SubMenu2
Description
Page
Query Name
Date Added
RMtrix

Addresses



-



Blank Names

List Persons with Blank Names in the Address List -- (a fault that may occur in a GEDCOM import).
Four Little Queries
blankname_in_addresslist
2009-12-30
Y
How used

Lists all addresses in AddressTable and reports how they are used.
Addresses - How Used
AddressesHowUsed
2012-08-17
Y

Correspondence



-



Database



-



Copy without People

Makes 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
Depopulate but keep Customs, Places, Sources
CustomDatabaseShell
2011-12-10

Copy to vanilla DB3

Clones 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.
Convert Database to NOCASE
RM#_CREATE_as_DB3_NOCASE | RM#_Copy_Data_to_DB3_NOCASE
2011-12-09

Database tools…






Integrity check

Explains why an integrity check is needed and how, using PRAGMA quick_check.
Check RootsMagic Database Integrity
Check RootsMagic Database Integrity
2010-05-27

Integrity check

PRAGMA integrity_check(integer);



Y
Reindex

REINDEX. N.B., for as long as a fake RMNOCASE collation is used, this REINDEX is needed for external operations on the database and must be followed by RM's Rebuild Indexes on returning to work the database with RM.



Y
Update Birth and Death Years

Sets Birth and Death years as seen in the sidebar index and various other reports and displays to match the corresponding facts. NOT for use on RM4; requires RM5+ Rebuild Indexes after completion.
Rebuild Indexes and Update Birth and Death Years
UpdateBirthDeathYears
2012-11-14
Y
Clean

Deletes unused rows from tables as reported by RMGC_Properties. May need checkbox options to select what tables are cleaned. RM4 & 5 are different

TBD


Clean

Deletes 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.
Delete Phantom Citations - Query
Delete Phantom Citations
2010-01-30

Clean
Delete Phantoms
Cleans out unused records from most tables, including phantom citations. Follow by RM Rebuild Indexes and Compact.
Delete Phantoms
DeletePhantoms
2012-10-27
Y
Compact

VACUUM



Y
Import






Import
Ancestry.com and FTM
Procedures involving Excel, text editor and SQLite queries to bring Ancestry hyperlinks, citations and images into RootsMagic that are otherwise lost on a straight GEDCOM import.
Ancestry.com and RootsMagic 5
MergeAncestryURLsToFTM
Shrinking Verbose Master Sources from Ancestry.com
2012-10-31

Export

possible route to a control of Fact settings for GEDCOM export

TBD


Backup…






Backup database






Backup database + media to RAR

Backs 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.
Backup Media with Database - RAR
RMfullbackup.bat
2011-02-02

Backup database + media to ZIP

Backs 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.
Backup Media with Database - 7Zip
RMfullbackup.bat
2011-02-02

Properties-Basic

Reports in a list most of the values found in RM4's File > Properties report; results can be exported to a file.
Database Properties List - Query
Database Properties
2010-01-29
Y
Properties-Extended

As 'Database Properties' but with more detail and pointing out possible problem areas
RMGC_Properties - Query
RMGC_Properties
2010-01-31
Y
Properties-Count rows in tables

Lists count of rows in each table -- If not 22 tables listed, database has corruption
RMGC Tables Row Count
RMGC_TablesRowCount
2010-05-26

Corrupt Database Recovery

An outline of procedures that have been used with some success to recover a corrupted RootsMagic database.
Corrupt Database Recovery
Corrupt Database Recovery
2012-03-10

People View Error

An outline of procedures that have been used when People View throws error message "SQLite Error 1 - at most 64 tables in a join".
People View Error - at most 64 tables in a Join
People View Error
2012-03-19

Edit



-



Person






Person
Birth Year
Lists Individuals whose Birth Year from NameTable does not match that of the date for their Birth fact.
Birth Year Mis-Match
BirthYearMisMatch
2010-01-17
Y
Person
Death Year
Lists Individuals whose Death Year from NameTable does not match that of the date for their Death fact.
Death Year Mismatch
DeathYearMismatch
2010-06-04
Y
Person
Last Edited
Queries to list and set Edit Dates.
Date Last Edited
Unnamed
2013-01-25

Person
Set Living
Another version of a Set Living query.
Another version of a Set Living query
SetLivingFlag
2011-07-04

Person
Set Living
A discussion of queries that can modify the Living flag.
Set Living Flag
SetLivingFlag
2011-07-02

Person
Same day sort order
Re-orders all same day events of limited types to a natural order, e.g., Birth before Death
Dates - Same Day Sort Order
SortDateSameDayOrder | SortDateSameDayOrderCustom
2011-12-19
Y
Person
Copy fact to group
Copies 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 Group
CopyFact2Group
2011-04-05

Person
Copy RIN to REFN
Variant 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
CopyRINtoREFN
2011-06-29

Person
Convert sharable facts
List 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.
Sharable Events - Find and Convert to Shared
SharableFacts2 | SharableEvents-Convert
2011-12-12
Y

Facts



-



Names - Add Married

Inserts an Alternate Name of type "Married" for female "Wife" spouses in a "Family" where no Alternate Surname matches the male "Husband's" and a (family) Marriage fact exists.
Names - Add Married
Name-Add_Married
2012-11-26
Y
Add Marriage to Couples Without

Adds a Marriage event to any couple (family) in a database that does not have this fact already.
Event - Add Marriage to Couples Without
Event-Add_Marriage
2013-01-16

Merge

Discussion of techniques used to copy Description from one type of event to another and the beginning of a more complex merging of near duplicate events
Events - Merge
Unnamed
2013-02-13

Non-Privatized SSNs

Finds SSN facts that have not been set to Private.
WebTrees Website - Pre-Processing Tools using SQLite in Visual Basic et al
RMSanity.exe
2012-02-06

Shared Events with missing witnesses

A fact may show that it is shared with someone whose name is missing. This query lists such facts with the names of the principals and the RINs of the missing witnesses.
Shared Events With Missing Witnesses
TraceHeadlessWitnesses
2011-12-10
Y
Sort Order Problems

Conecptual discussion and scripts to find undated or same date events that are reported by RM in an unnatural order.
Facts - Sort Order Problems
FactOrderTable, SortOrderProblems
2013-01-07

Split Shared

Convert shared events to individual events for compatibility on export to 3rd party software. Split | Unshare | Undo | Hide Tracks
Facts - Split Shared to Individual
Facts-SplitSharedToIndiv | Facts-Unshare |Facts-Split-Undo | Facts-Split-HideTracks
2012-12-19
Y

Fact Types



-



View current outputs;
Set all to GEDCOM;
Snapshot current;
View snapshot outputs;
Restore from snapshot;
Delete snapshot

Lists the Include settings for all fact types.

Sets all fact types to be included in GEDCOM and drag'n'drop.

Stores a snapshot of all settings for fact types in an extra table in the database.

Lists the include settings stored in the snapshot.

Restores all settings for fact types from the snapshot.

Deletes the extra table storing the snapshot from the database.
Fact Inclusion Controls

2012-03-12
Y

File



-



Open

Open Windows Explorer browser on *.rmgc files.



Y
Open Recent






Search for Files






Close






Copy






Exit





Y

Functions



-





Decodes most of the possible formats found in RM4 Date fields of the form Da+nnnnnnnn.x+nnnnnnnn.x
Date Decoder
DateDecoder
2010-01-21



For encoding to SortDates, dates with optional RM modifiers and decoding same.
Dates> SortDate Algorithm
SortDateDecodeDev
2012-11-03

Groups



-





Top page for a collection of queries for refreshing Named Groups
Named Group Refresh
Refresh - various
2011-11-26

Ancestors

Provides a manual refresh for a specific Named Group, i.e., the ancestors of a specified person.
Ancestors Named Group
AncestorsGroup
2011-11-23

Ancestors + Collateral Lines

The RMSplit C# program below allows you to create a group in RootsMagic5 which contains a selected person, their ancestors, and a selected number of "leaves" (collateral lines us a given depth).
SplitTree
Rmsplit
2011-01-03

Census needed

Builds 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.
Census Needed - Named Group
CensusNeededGroup | CensusNeededGroup2
2011-11-26

Mark/Unmark Lists

Marks or Unmarks members of a group according to a list; equivalent to memorising the checkboxes in RootsMagic Explorer for re-use.
Named Group - Mark or Unmark List refresh
Group Unmark List Refresh | Group Mark List Refresh
2011-11-27








Media



-



Compare and Attach

Compares RM MultiMedia report to contents of a folder for review and generates a GEDCOM which on import attaches all unlinked items to the Media Gallery. Excel workbook, not SQLIte.
Media Comparison & Attach Utility
RM-Import-Unlinked-Media
2013-01-19

Copy from Shared Facts to Sharee Personal

Generates Personal mediatags from shared events' media for non-Principal sharees.
MediaTags - Copy Shared Facts Media To Sharee Personal
MediaTags-CopySharedFactsMediaToShareePersonal
2012-12-27
Y
Delete Personal having Fact Duplicates

Deletes media tags from Person where a duplicate exists in a fact.
MediaTags - Delete Personal Having Fact Duplicates
MediaTags-DeletePersonalHavingFactDupes
2012-12-28
Y
Delete Unused

Deletes records from MultiMediaTable not tagged by any Person, Family, Fact, Place, Source or Citation.
Media - Delete Unused
MediaDeleteUnused
2013-01-21
Y
Duplicates

Series 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 Queries
MediaRepair
2010-12-14

List uses

Complete listing of all users of media in the Gallery, more complete and navigable than what is provided in RM 4.1.2.1
Media Users List Query
Media Users List
2011-11-09
Y
Files usage

Lists files under the RM Multimedia default folder and flags those used by the RM database; helps to ensure that files are used.
Scrapbook Files Status
ScrapBookFilesStatus
2011-03-13

Update Media Paths

A SQLite equivalent to RootsMagic's own Search & Replace function on "Multimedia filenames" in the "Field to search" selection but with no programming constraints
Update Media Paths
Update Media Paths
2012-01-11

Missing Media

Lists those image files that have not been opened by RM, hence nothing in the thumbnail field. With comments leading to other references.
Missing Media

2012-01-15

Preserve Captions et al from RM4 to RM5

This series of queries and related Windows commands revises a RM4 database and makes copies of the multiply-linked files so that the update to RM5 will preserve all the metadata.
Media - Preserve Captions et al from RM4 to RM5
RM4-MultiLinkedMediaDuplicationPreRM5
2012-01-31

Sync Image File Metadata

Compare EXIF, IPTC, XMP metadata stored in image files to the Caption, Description and other metadata stored in a RootsMagic 5 database for image files linked to its Media Gallery and copy between the files and the database.
Media Metadata, Read, Write, Compare with Picasa
RM5comparePicasa.bat
ImportPicasaDescriptions.sql
RM5copyMetadataToImageFileExifToolArgList.sql
RM5copyMetadataToImageFileExifToolArgList.bat
RM5copyMetadataToRM.sql
RM5copyMetadataToRM.bat
2012-01-17

Broken Links; Unused Items; Duplicate Items

Finds broken media links using an extension in Visual Basic.
Finds media items in the Media Gallery that are not tagged to anything in the database.
Finds media items that have been attached more than once to the Media Gallery
WebTrees Website - Pre-Processing Tools using SQLite in Visual Basic et al
RMSanity.exe
2012-02-06

Media Type Reset

Sets the Media Type value for each media file to one of the four types: Image, File, Sound, Video, according to the file extension.
Media Type Reset
MediaTypeReset
2012-12-04
Y

People



-



Merge
Fast Duplicate Name Search
Lists 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.
Duplicate Name Search - query
DuplicateNameSearch
2010-02-23
Y
Merge
Mark unmerged not a problem
Copies 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.
Duplicate Search Merge Database
MarkNotProblem
2010-02-21

Delete Many
Delete by Color Code

Delete by Named Group
Delete all people color-coded RED (or other color by editing the query). Follow with Delete Phantoms.


Delete all people belonging to a Named Group (edit the query to the group ID). Follow with Delete Phantoms.
Delete Many
DeleteByColorCode


DeleteByNamedGroup
2012-10-27

Phantom Spouses - Unnamed & childless

List or delete phantom spouses (PersonID=0 in FamilyTable and childless) as from Ancestry.com via FTM2012.
Phantom Spouses - Unnamed and childless
Unnamed
2013-03-14

Places



-



County Check

There are four Internet sites that RM5's County Check is set to call for maps and other information.
County Check
-
2011-12-10

Unused Places

List of unused Places
Four Little Queries
UnusedPlaces
2009-12-30
Y
Place Details

Lists Places having Place Details
Four Little Queries
PlacesDetails
2009-12-30
Y
Place Details with Facts

Returns all Places with Place Details and the Persons or Families (couples) and their Facts, including Shared events, using the Place Detail.
Facts Having Place Details - Query
FactsHavingPlaceDetails3
2010-01-16

Place Details without a Place

One query lists all Place Detail rows from PlaceTable for which there is no master Place. Another deletes such rows.
Place Details without a Place
Place Details without a Place
2012-01-30
Y
Convert to Place Details

Combo 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!
Places to Place Details Conversion
Places to Place Details Conversion
2011-11-16

Convert Place + Place Detail to Place

The opposite direction is much easier. Needed for export to GEDCOM for third party apps that do not support Place Detail.
Convert Place + Place Detail to Place
Convert Place + Place Detail to Place
2012-01-22

Frequency of Use

Lists total number of events and the maximum user of each place.
Places - Frequency of Use
Place_Frequency
2013-03-25

Orphaned Place Details

Finds Place Details that have no owner Place.
WebTrees Website - Pre-Processing Tools using SQLite in Visual Basic et al
RMSanity.exe
2012-02-06

Parse and Recombine Names

Collection of queries to parse the 4-part comma-delimited Standardized place names and place the first 3 parts in the regular Name field and the first 2 parts in the Abbreviated field.
Place Names - parse and recombine
PlaceCommaParse
PlaceParse
PlaceAbbrevUpdate
PlaceNameUpdate
2013-02-17

Recombine Fractures from FTM2012

Recombines places from Ancestry.com having the "/" in the name which get split in FTM2012 into Place Name and Event Description.
Places - Recombine Fractures from FTM 2012
Places-RecombineFTMfractures
2013-03-22

Reports



-



Format






Format
Paragraphing
Removes 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.
Paragraphing
Paragraph-Strip | Paragraph-Add
2011-12-22
Y
Format
Strip footnote line feeds
This 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
Fix Extra Line Feeds in Footnote
2011-10-02

Format
Narrative, Jerry Bryan Trick
Causes Narrative Descendant reports to output childless, spouseless children in their own generation, along with their married or parental siblings.
Reports, Narrative, Jerry Bryan Trick
DummyFamily-Add
DummyFamily-Delete
2012-01-15

Format
Places - first name exploit for improved narratives
To mitigate repetition of full place names, changes the default sentence templates to use [Place:first] instead of [Place] and then goes on to generate custom sentence templates or modify existing ones for all first instances of a place in each person's chronology to use [Place] instead of [Place:first]
Places - first name exploit for improved narratives
Places-FirstNameExploit
2013-03-30

My Lists






My Lists
Ahnentafel 64 generations
Every direct line ancestral RIN list for one or all persons in the database with the Ahnentafel number of the last person in each line, to 64 generations, exceeding RM's 32 gen limit on the number. Also shows all lines, not just those of parents selected for display in RM's main Views.
Ahnentafel 64 generations
Ahnentafel-64
2012-01-13

My Lists
All facts
List 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 result.
Pulling Together All the Events for An Individual
AllFacts4Persons
2010-01-06

My Lists
Begatters
An optional extension to AllFacts4Persons or standalone. Having a Child as an event for the Father and the Mother.
Births of children as facts
PersonsBegatChildren
2010-01-14

My Lists
Direct Ancestors
Lists all the ancestral lines for a given RIN
Ancestors Query
Ancestors
2011-11-23

My Lists
DNA
Two 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
DNA_mtDNA_locationslist | DNA_Y-STR_markerslist
2011-06-18

My Lists
Facts shared with persons in database
Lists people who share a fact with a Principal, as well as relevant fact and Principal information.
People Who Share a Fact with a Principal List - Query
People Who Share a Fact with a Principal
2010-03-10
Y
My Lists
Facts shared with persons NOT in database
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 information.
People Who Share a Fact with a Principal, But Who Are Not in a Tree in the File List - Query
People Who Share a Fact with a Principal, But Who Are Not in a Tree in the File
2010-02-26
Y
My Lists
Geo-Lifelines
Have 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.
Geo-Lifelines Query
Geo-Lifelines
2010-08-31

My Lists
LifeLines
Lists 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 person.
LifeLines - Query
LifeLines
2010-03-15

My Lists
Map Events (KML)
This 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.
MapEvents-KML query
MapEvents-KML
2010-09-03

My Lists
Multiple spouses
Lists persons with multiple spouses in descending number - may flag a data problem
Multiple Spouses query
Multi-spouses
2010-08-23
Y
My Lists
Nameless witnesses to shared facts
Report 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 database.
SharedEventsWithMissingWitnesses
TraceHeadlessWitnesses
2011-12-09
Y
My Lists
Non-Proven
List non-proven events
Report Non Proven
Proven
2013-03-28

My Lists
To-Do Grid
A 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.
A Query for a To Do List in a Grid Format
MyToDoList
2011-07-07
Y

Repositories



-



Research



-



Search



-



Name

Produces a list of names that match or sound like specified names, similar to RM4's NameFind.
Name Find query
NameFind
2011-11-21

Surname

List 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 Queries
selected_surnames
2009-12-30

Search & Replace

Discussion and examples of how you can search (filter) using any SQLite manager and replace found values with revised ones using SQLiteSpy.
Search & Replace
Search&Replace
2010-11-09

Sources



-



Source List

About 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
Source List
2011-11-05
Y
Source Details

Lists Source Names along with Source Detail field names; illustrates principles applicable to Master Source fields and other XML-like columns.
Source Detail View (Parsing XML)
Source Detail View (Parsing XML)
2011-06-06

Citations

Lists 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 - Query
AllCitations
2010-01-30

Citations

Lists 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.
All Citations & Dupes Count - Query
AllCitations+Dupes
2010-07-08

Citations, Invisible
List

Convert
Lists citations for Primary Name as imported from Ancestry.com, FTM, et al but hidden in RM.
Converts such invisible citations to visible Person citations.
Citations Invisible Revealed
Citations, Invisible - List
Citations, Invisible - Convert to Personals
2012-09-10
Y
Sources by Template

Lists 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.
Source Template List - Query
SourceTemplateListWithCitationDetails2.sql
2010-02-13

Make Built-in TemplatesEditable

Converts Sources using uneditable, built-in templates to using editable copies.
Source Templates
SrcTmpltsConvert
2011-10-02

Revert Editable Templates to Built-in

Reverts Sources modified by SrcTmpltsConvert to using uneditable, built-in templates.
Source Templates
SrcTmpltsRevert
2011-10-02

Source Template Field Count

Lists the total number of fields used in a source template and the number of fields in its footnote sentence template as a measure of complexity.
Source Templates - Field Count
SourceTemplates-FieldCount
2013-04-08

To-Do



-





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
To-Do (was To-Do4Persons)
2010-01-18
Y

Tools



-



WebTags - Make from Ancestry.com Comments

Creates a Citation WebTag for each citation having an URL beginning the Citation Comments field with "http://". Not restricted to Ancestry.com downloads.
WebTags - from Ancestry.com and FTM
WebTags-MakeFromAncestryComments
2012-12-10
Y
WebTags - Consolidate

Creates Person WebTags from Citation WebTags to consolidate all WebTags pertaining to a person under the WebTags button on the Edit Person Screen.
WebTags - Consolidate
WebTags-Consolidate
2012-12-10
Y
WebTags - Delete Duplicates

Deletes perfectly duplicate WebTags as will occur if WebTags-Consolidate is repeated or other possible cause.
WebTags - Consolidate
WebTags-DeleteDuplicates
2012-12-10
Y
WebTags - Convert to Notes et al

Append Person, Source and Citation WebTags to Person Notes, Source Comments and Citation Comments for passing through GEDCOM to other software.
WebTags - convert to Notes et al
WebTags_to_Notes
2013-03-03

Addresses | Correspondence | Database | Edit | Facts | Fact Types | File | Functions | Groups | Media | People | Places | Reports | Repositories | Research | Search | Sources | To-Do | Tools