Logo Home    Page list

Bundled Utilities - Groupings #application

Table of Contents

File
Edit
Lists
Search
Reports
Tools
Groups
functions
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.
RMutil-Menu.xlsx
Rmutils
(working name)





January 10, 2012
Main Menu






5:55 PM




SQLiteToolsForRootsMagic query or other description


Sort
Main
Sub1
Sub2
Query Name
Description
Page
Comment
1

File







1.1
File
Open


Open Windows Explorer browser on *.rmgc files.

Get database version, possibly do SQLite Quick Integrity Check "PRAGMA quick_check(integer);"
1.2
File
Open Recent





1.3
File
Search for Files





1.4
File
Close





1.5
File
Copy





1.5.1
File
Copy
All




1.5.2
File
Copy
Less People
CustomDatabaseShell
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

1.5.3
File
Copy
To vanilla DB3
RM#_CREATE_as_DB3_NOCASE | RM#_Copy_Data_to_DB3_NOCASE
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

1.6
File
Database tools…





1.6.1
File

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

1.6.1
File

Integrity check

PRAGMA integrity_check(integer);


1.6.2
File

Reindex

REINDEX


1.6.3
File

Clean
TBD
Deletes 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.3a
File

Clean
Delete Phantom Citations
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

1.6.4
File

Compact

VACUUM


1.7
File
Export

TBD
possible route to a control of Fact settings for GEDCOM export


1.8
File
Backup…





1.8.1
File

Database file only




1.8.2
File

Include media
RMfullbackup.bat
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

1.8.2
File

Include media
RMfullbackup.bat
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
If there is Backup, should there be Restore from within Rmpotions?
1.9
File
Properties

Database Properties
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

1.9
File
Properties

RMGC_Properties
As 'Database Properties' but with more detail and pointing out possible problem areas
RMGC_Properties - Query
Variant for RM5 TBD
1.9
File
Properties

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

1.A
File
Exit





2

Edit







2.1
Edit
Person





2.1.1
Edit
Person
Birth Year
BirthYearMisMatch
Lists Individuals whose Birth Year from NameTable does not match that of the date for their Birth fact.
Birth Year Mis-Match

2.1.2
Edit
Person
Death Year
DeathYearMismatch
Lists Individuals whose Death Year from NameTable does not match that of the date for their Death fact.
Death Year Mismatch

2.1.3a
Edit
Person
Set Living
SetLivingFlag
Another version of a Set Living query.
Another version of a Set Living query

2.1.3b
Edit
Person
Set Living
SetLivingFlag
A discussion of queries that can modify the Living flag.
Set Living Flag

2.1.4
Edit
Person
Same day sort order
SortDateSameDayOrder | SortDateSameDayOrderCustom
Re-orders all same day events of limited types to a natural order, e.g., Birth before Death
Dates - Same Day Sort Order

2.1.5
Edit
Person
Copy fact to group
CopyFact2Group
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

2.1.6
Edit
Person
Copy RIN to REFN
CopyRINtoREFN
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

2.1.7
Edit
Person
Convert sharable facts
SharableFacts2 | SharableEvents-Convert
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

3

Lists







3.1
Lists
Source List





3.1.1
Lists
Source List
Source List
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

3.1.2
Lists
Source List
Source Details
Source 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.
Source Detail View (Parsing XML)

3.1.3
Lists
Source List
Citations
AllCitations
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

3.1.3
Lists
Source List
Citations
AllCitations+Dupes
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

3.2
Lists
To-Do List





3.2
Lists
To-Do List

To-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.3
Lists
Research Manager





3.4
Lists
Media Gallery





3.4.1
Lists
Media Gallery
Duplicates
MediaRepair
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
Needs rev for RM5
3.4.2
Lists
Media Gallery
List uses
Media Users List
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
Needs rev for RM5
3.4.3
Lists
Media Gallery
Files usage
ScrapBookFilesStatus
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

3.5
Lists
Address List





3.5.1
Lists
Address List
Blank Names
blankname_in_addresslist
List Persons with Blank Names in the Address List -- (a fault that may occur in a GEDCOM import).
Four Little Queries

3.6
Lists
Repository Manager





3.7
Lists
Correspondence List





3.8
Lists
Place List





3.8.1
Lists
Place List
Unused Places
UnusedPlaces
List of unused Places
Four Little Queries

3.8.2
Lists
Place List
Place Details
PlacesDetails
Lists Places having Place Details
Four Little Queries

3.8.3
Lists
Place List
Convert to Place Details
Places to Place Details Conversion
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

3.8.4
Lists
Place List
Place Details with Facts
FactsHavingPlaceDetails3
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

3.9
Lists
Fact Type List




Manage, save, apply Fact settings for Export, Reports, Privacy
3.A
Lists
Source Templates





3.A.1
Lists
Source Templates
Sources by Template
SourceTemplateListWithCitationDetails2.sql
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

3.A.2
Lists
Source Templates
Make Editable
SrcTmpltsConvert
Converts Sources using uneditable, built-in templates to using editable copies.
Source Templates

3.A.3
Lists
Source Templates
Revert to Built-in
SrcTmpltsRevert
Reverts Sources modified by SrcTmpltsConvert to using uneditable, built-in templates.
"

6

Search







6.1
Search
Name

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

6.2
Search
Surname

selected_surnames
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

6.3
Search
Search & Replace

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

7

Reports







7.1
Reports
Format





7.1.1
Reports
Format
Paragraphing
Paragraph-Strip | Paragraph-Add
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

7.1.2
Reports
Format
Strip footnote line feeds
Fix Extra Line Feeds in Footnote
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

7.2
Reports
My Lists





7.2
Reports
My Lists
All facts
AllFacts4Persons
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

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

7.2
Reports
My Lists
Direct Ancestors
Ancestors
Lists all the ancestral lines for a given RIN
Ancestors Query

7.2
Reports
My Lists
DNA
DNA_mtDNA_locationslist | DNA_Y-STR_markerslist
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

7.2
Reports
My Lists
Facts shared with persons in database
People Who Share a Fact with a Principal
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

7.2
Reports
My Lists
Facts shared with persons NOT in database
People 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 information.
People Who Share a Fact with a Principal, But Who Are Not in a Tree in the File List - Query

7.2
Reports
My Lists
Geo-Lifelines
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

7.2
Reports
My Lists
LifeLines
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

7.2
Reports
My Lists
Map Events (KML)
MapEvents-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

7.2
Reports
My Lists
Multiple spouses
Multi-spouses
Lists persons with multiple spouses in descending number - may flag a data problem
Multiple Spouses query

7.2
Reports
My Lists
Nameless witnesses to shared facts
TraceHeadlessWitnesses
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.
Shared Events With Missing Witnesses

7.2
Reports
My Lists
To-Do Grid
MyToDoList
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

8

Tools






Hmm Tools within a Tool
8.1.1
Tools
Merge
Fast Duplicate Name Search
DuplicateNameSearch
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

8.1.2
Tools
Merge
Mark unmerged not a problem
MarkNotProblem
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

G

Groups



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

G.1
Groups
Ancestors

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

G.2
Groups
Census needed

CensusNeededGroup | CensusNeededGroup2
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

G.3
Groups
Mark/Unmark Lists

Group Unmark List Refresh | Group Mark List Refresh
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

zFn

functions



DateDecoder
Decodes most of the possible formats found in RM4 Date fields of the form Da+nnnnnnnn.x+nnnnnnnn.x
Date Decoder
encode as SQLite function
zFn
functions


SortDateEncodeDecode
for encoding single, pure dates and decoding corresponding SortDates (no modifiers yet like bef, aft)
Dates - SortDate Algorithm
encode as SQLite function
















Discussions

LessTX

Functions by Table modified

LessTX 11 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.

ve3meo
ve3meo 21 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.

www.000webhost.com