Contents
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 | ||
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.