Four Little Queries #names #blanks #placedetails

Posted on 2013-04-262019-01-26 by Tom Holden

Query Name	Description	File
blankname_in_addresslist	List Persons with Blank Names in the Address List — (a fault that may occur in a GEDCOM import).	RM4_Queries.sql
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.
PlacesDetails	Lists Places having Place Details
UnusedPlaces	List of unused Places

The queries above are included in the one file. Some SQLite managers can import a query file and some can also export a query file (e.g. SQLiteman does both). Others may require you to open the file with a text editor and copy/paste the query into the SQLite manager’s query editor. With SQLiteman, you place the cursor anywhere in a query line and that is the one executed; SQLiteSpy requires you to highlight the selected command and execute using Ctrl+F9 rather than F9 which would run the whole listing. Each query must end with a semi-colon to demark it from the others.

Optionally, the queries can be expanded to include the CREATE VIEW command by deleting the semicolon at the end of its line. When successfully executed, the query is embedded in the database as a virtual table which remains with the database file until it is dropped (deleted). Some SQLite managers allow you to save a query as a view from the user interface, putting a GUI in front an internal CREATE VIEW.

Duplicate Name Search – query #duplicates #names

Posted on 2013-04-262022-04-27 by Tom Holden

This query lists duplicate name pairs with a weighted score indicating the degree of match similar to RootsMagic 4’s Duplicate Search Merge tool. On a large database, it produced useful results in 4.5 minutes compared to 45 minutes for RM4. It operates with fixed settings similar to DSM’s default settings, e.g. Birth Years within 2 years of each other. In use, one would run the query to produce a report and from its list carry out Manual Merge in RootsMagic.

Download: DuplicateNameSearch.sql

Screenshot of results of DuplicateNameSearch query

Screenshot of RootsMagic Duplicate Search Merge

Media Type Reset #media #update

Posted on 2013-04-262023-02-19 by Tom Holden

Users can mistakenly add image files to the Media Gallery with the wrong MediaType which prevents the images from being used in reports. MediaType is normally set by user selection in the Add Media Item dialog and cannot be changed from within RootsMagic 6.0.0.2 and earlier. Within RM the only option is to add the media item again selecting Image as the Media type in the Add Media Item dialog. Of course, the item must be tagged to all the same things as the mis-typed item and the latter must be removed from the Gallery. This might be manageable for a few such mistakes but not if one had consistently erred on a large number of additions. Media Type Reset can correct all mis-typed errors in seconds.

The MediaTypeReset script sets the MultimediaTable.MediaType according to the file extension of the media file. It first sets all MediaType values to 0 (untyped), then sets Image (1), Sound (3) and Video (4) types according to the file extensions. All remaining entries are set to File (2).

It is unclear that RootsMagic makes any distinction among the File, Sound and Video types other than the filters applied when browsing for the file to be added and to display a symbolic thumbnail. Regardless, double-clicking on such items in the Media Gallery opens the file with its associated external application. None are included in RM reports.

When Image type is selected, the Scanner button is enabled and the disk browsing filter is set to an undeclared list of image file extensions. By testing with many file type extensions, only those types that pass the browser filter have been included in the script as only those that the Image Viewer/Editor can render should be allowed. More significantly, the Image explorer mimics but seems different from the Windows Explorer opened for the other file types and may ‘remember’ a different path. Some users may prefer to use a consistent browser. Using the File type along with MediaTypeReset allows one to do so.

On adding an item selected through the Image type, RootsMagic generates a thumbnail image. For images added via the other types, no thumbnail is created nor are they used in any reports. After running MediaTypeReset, the thumbnail is generated on opening an Album to which the item is tagged, or on opening the Media Gallery, subject to Program Options.

MediaTypeReset.sql Download

rev 2023-02-19 added the .mp4 extension as a “Video” type. Tested without error on #RM8.

contains the 2012-12-09 version which omitted the .mp4 extension

-- MediaTypeReset.sql
/*
2012-12-04 Tom Holden ve3meo
2012-12-09 restricted Image file types to those rendered by Viewer/Reporter
2023-02-19 MP4 added

Users can mistakenly add image files to the Media Gallery with the 
wrong MediaType which prevents the images from being used in reports.
MediaType is normally set by the user selection in the Add Media Item dialog
and cannot be changed from within RootsMagic 6.0.0.2 and earlier.
This script sets the MediaType according to the file extension of the media file.
*/

-- Clear all previous settings
UPDATE MultimediaTable
 SET MediaType=0
;
-- Set Image files (only RM Add Media filters + .targ, .tiff because of viewer/reporter limitations)
UPDATE MultimediaTable
 SET MediaType=1
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.bmp','.gif','.jpg','jpeg','.png','.tga','targ','.tif','tiff')
; 
-- Set Sound files (RM Add Media filters on just .wav, .mid, .mp3 but no player to set constraints)
UPDATE MultimediaTable
 SET MediaType=3
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.wav','.mid','.mp3','.ogg','.gsm','.dct','flac','aiff','.vox','.raw','.wma','.aac')
  OR LOWER(SUBSTR(MediaFile,-3)) 
  IN ('.au','.ra','ram','dss','msv','dvf','ape')
;
-- Set Video files (RM filters on Add Media only for .avi, .mov, .mpg, .mpeg, .wmv but no player to set constraints)
UPDATE MultimediaTable
 SET MediaType=4
  WHERE LOWER(SUBSTR(MediaFile,-4)) 
  IN ('.3gp','.asf','.avi','.dat','.flv','.m4v','.mkv','.mov','mp4','mpeg','.mpg','.mpe','.swf','.wmv')
;
-- Set files, other than previously set Image, Sound, Video types, to File type
UPDATE MultimediaTable
 SET MediaType=2
  WHERE MediaType=0
;

Media – Delete Unused #media #delete

Posted on 2013-04-262019-01-26 by Tom Holden

Deletes records from MultiMediaTable untagged by any Person, Family, Fact, Place, Source or Citation. Was formerly included in Delete Phantoms.

MediaDeleteUnused.sql

-- MediaDeleteUnused.sql
/*
2013-01-21 Tom Holden ve3meo
 
Deletes records from MultiMediaTable untagged by
any Person, Family, Fact, Place, Source or Citation.
 
Extracted from DeletePhantoms.sql
*/
DELETE
FROM MultimediaTable
WHERE MediaID NOT IN (
        -- Person media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 0
            AND OwnerID IN (
                SELECT PersonID
                FROM PersonTable
                )
 
        UNION
 
        -- Family media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 1
            AND OwnerID IN (
                SELECT FamilyID
                FROM FamilyTable
                )
 
        UNION
 
        -- Event Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 2
            AND OwnerID IN (
                SELECT EventID
                FROM EventTable
                )
 
        UNION
 
        -- Master Source Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 3
            AND OwnerID IN (
                SELECT SourceID
                FROM SourceTable
                )
 
        UNION
 
        -- Citation Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 4
            AND OwnerID IN (
                SELECT CitationID
                FROM CitationTable
                )
 
        UNION
 
        -- Place Media
        SELECT MediaID
        FROM MediaLinkTable
        WHERE OwnerType = 5
            AND OwnerID IN (
                SELECT PlaceID
                FROM PlaceTable
                )
        );

MediaTags – Delete Personal Having Fact Duplicates #media

Posted on 2013-04-262019-01-26 by Tom Holden

It is possible to excessively tag a media item to a Person and his/her Facts/Events. If someone uses Facts – Split Shared to Individual AND MediaTags – Copy Shared Facts Media To Sharee Personal, there certainly will be real duplicate mediatags for each person, one is the tag for the newly created Individual event from the first script, the other
is the workaround Personal tag generated by the second. A procedure is needed to delete the duplicate Personal ones. This script endeavours to do that, based on the unique combinations of MediaID and OwnerID of events having media tags; Personal media tags having the same combination are deleted.

MediaTags-DeletePersonalHavingFactDupes.sql

-- MediaTags-DeletePersonalHavingFactDupes.sql
/*
2012-12-28 Tom Holden ve3meo
 
Deletes Personal MediaTags (Person-General) for media
having duplicate tags to an event for the same person.
*/
 
-- SELECT LinkID, MediaID, OwnerID -- testing
DELETE
FROM MediaLinkTable
WHERE OwnerType=0
AND MediaID || '.' || OwnerID
IN
(
-- build ordered list of event mediatags in form MediaID.PersonID
SELECT DISTINCT MediaID || '.' || PersonID AS MediaPerson
FROM
(
-- LinkIDs for PersonID's having Indiv Event MediaTags
SELECT DISTINCT LinkID, MediaID, E.OwnerID AS PersonID FROM MediaLinkTable ML
INNER JOIN EventTable E
ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 0
 
UNION
 
-- LinkIDs for Husband PersonID's having Family Event MediaTags
SELECT DISTINCT LinkID, MediaID, F.FatherID AS HusbandID FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 1
INNER JOIN FamilyTable F
 ON E.OwnerID = F.FamilyID
 
UNION
 
-- LinkIDs for Wife PersonID's having Family Event MediaTags
SELECT DISTINCT LinkID, MediaID, F.MotherID AS WifeID
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType=2
 AND E.OwnerType = 1
INNER JOIN FamilyTable F
 ON E.OwnerID = F.FamilyID
)
ORDER BY MediaPerson
);

MediaTags – Copy Shared Facts Media To Sharee Personal #media #sharedevent

Posted on 2013-04-262019-01-26 by Tom Holden

This script responds as a workaround to a 13 Dec 2011 wish expressed by MarkVS, that RootsMagic’s Media Album/Gallery and other Media dialog windows that show MediaTags should also show the indirect tags to shared events for the Persons having non-Principal roles, sometimes called “sharees” and the shared event in their context a “sharee event”. To do so can only be done by programming changes; one year later, nothing has changed. A workaround is to generate Personal mediatags from the shared event’s media for these non-Principal sharees. That’s what this script does.

Considerations:

Assuming the shared event’s mediatag is Primary for that event and Included in Scrapbook, so, too is each inherited mediatag for each sharee event. Therefore, the script generates a Primary-No, Scrapbook-No mediatag for the Person to avoid conflict with the truly Personal and other script-generated Personal mediatags and so that the Person Scrapbook does not receive duplicates.
These workaround mediatags probably should have some unique property that would distinguish them from regular mediatags and the indirect ones that may appear if and when RootsMagic is enhanced to display them. The current script does so, perhaps too subtly, by adding a sentence to the Comment field in the Media Tag dialog window (Description field in MediaLinkTable). There are other techniques that might be better suited for future script operations – e.g., an apparently unused Note field in MediaLinkTable or wrapping the sentence in some distinct string sequence such as the “{ … }” pairing used for distinctive privacy flags in Facts – Split Shared to Individual (note that they do not function as privacy flags in the MediaTag Comments/Description field because it is not outputted in any report anyway).
If someone uses Facts – Split Shared to Individual in combination with this script, there certainly will be real duplicate mediatags for each person, one is this workaround Personal tag, the other is the tag for the newly created Individual event. A procedure is needed to delete the duplicate Personal ones. It could rely on a more distinctive branding as discussed in 2) or simply look for basic matches with mediatags for events as in MediaTags – Delete Personal Having Fact Duplicates.

2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.
Sentence in tag description reads “Shared in the …”.
2012-12-28 V1 complete – tags for both family and non-family shared events
2012-12-27 V0 creates mediatags for shared individual events, not for shared family events

MediaTags-CopySharedFactsMediaToShareePersonal.sql

-- MediaTags-CopySharedFactsMediaToShareePersonal.sql
/*
2012-12-27 Tom Holden ve3meo
2012-12-28 V1 complete
2013-01-05 1.1 outputs media tag for sharees having no defined role in the event.
           Sentence in tag description reads "Shared in the ...".
 
Creates a media tag, for media already tagged to a shared event,
 to the Persons sharing the event. The Description contains
 the original Description appended with the Person's role name,
 the event the Person shared in and the name(s) of the Principal(s)
 in the event.
 
Version 1 deals with tags for shared non-family and family events.
 
Use MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by this script.
*/
 
 
INSERT OR REPLACE INTO MediaLinkTable
 
-- tags for shared individual (non-family) events
SELECT
 NULL AS LinkID,
 MediaID,
 0 AS OwnerType,
 OwnerID,
 0 AS IsPrimary,
 0 AS Include1,
 0 AS Include2,
 0 AS Include3,
 0 AS Include4,
 0 AS SortOrder,
 0 AS RectLeft,
 0 AS RectTop,
 0 AS RectRight,
 0 AS RectBottom,
 '' AS Note,
 Caption,
 RefNumber,
 DATE,
 SortDate,
 Description
FROM
(
SELECT
 ML.MediaID AS MediaID,
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.DATE AS DATE,
 ML.SortDate AS SortDate,
 ML.Description
  || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || N.Given  || ' '  || N.Surname  || '-'  || N.OwnerID
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 0 -- Person
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.ROLE = R.RoleID
INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND ML.MediaID || '.' || W.PersonID
NOT IN
(
 SELECT DISTINCT MediaID || '.' || OwnerID
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
 
UNION
-- tags for shared family events
SELECT
 ML.MediaID AS MediaID,
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.DATE AS DATE,
 ML.SortDate AS SortDate,
 ML.Description  || ' '  || ifnull(R.RoleName, 'Shared')  || ' in the '  || LOWER(F.Name)  ||  ' of '  || Husb.Given  || ' '  || Husb.Surname  || '-'  || Husb.OwnerID  || ' & '  || Wife.Given  || ' '  || Wife.Surname  || '-'  || Wife.OwnerID
 
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 1 -- FamilyPerson
INNER JOIN FamilyTable Fam
 ON E.OwnerID = Fam.FamilyID
LEFT JOIN NameTable Husb
 ON Fam.FatherID = Husb.OwnerID AND +Husb.IsPrimary
LEFT JOIN NameTable Wife
 ON Fam.MotherID = Wife.OwnerID AND +Wife.IsPrimary
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.ROLE = R.RoleID
--INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND W.PersonID || '.' || ML.MediaID
NOT IN
(
 SELECT DISTINCT MediaID || '.' || OwnerID
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
)
 
;
 
-- USE MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by above
;

Fact Inclusion Controls #facttypes #events #gedcom #reports

Posted on 2013-04-262022-04-27 by Tom Holden

2021-10-28: The following queries are compatible with #RM8 but, while included in , the app itself is not.

If a user has set certain Fact Types to be excluded from GEDCOM export, those fact types are also not transferred to another database via Drag and Drop (DnD). This setting is controlled in the Edit Fact Type window, accessible through the menu path Lists > Fact Type List which opens the Fact Types window through which one can inspect the settings one fact at a time. The Edit button opens the Edit Fact Type window on the selected fact. To ensure that all facts or events for the selected persons are transferred via DnD, one needs to remember to inspect and change the ‘excluded’ facts to ‘included’; following the DnD, the settings wanted for GEDCOM, if different from those for DnD, must be restored. Given the one-at-a-time procedure, it is desirable to have more expeditious ways to change settings. This page offers some short queries that will satisfy some needs until such time as RootsMagic gains more efficient controls. These queries are included in RMtrix.

List Include Settings

This query puts the Include settings for all fact types in table form on one screen for easy inspection. Sorting the list on any column rapidly shows which fact types have been included or excluded from RootsMagic outputs.

Screenshot from SQLiteSpy. 1 = included, 0 = excluded.

-- List FactType Include Settings
SELECT
 Name,
 Flags & 1 AS GEDCOM,
 Flags & 2 <> 0 AS WEB,
 Flags & 4 <> 0 AS FGS,
 Flags & 8 <> 0 AS NARR,
 Flags & 16 <> 0 AS INDIV,
 Flags & 32 <> 0 AS LISTS
FROM FactTypeTable
ORDER BY Name
;

N.B.: As written, the above query (and some of the others) requires SQLiteSpy to have the fake RMNOCASE collation as described at RMNOCASE – faking it in SQLiteSpy.

Save Current Fact Type Settings for Later Restoration

It would be nice to be able to save the current settings, make changes that are temporary, and restore them later. This query simply makes a copy of the FactTypeTable and saves it in the database file. Dead simple and does not affect RootsMagic operations.

-- Save current FactType settings for subsequent restoration
DROP TABLE IF EXISTS zFactTypeTable;
CREATE TABLE IF NOT EXISTS zFactTypeTable
AS SELECT * FROM FactTypeTable;

Set All Fact Types to be Included in GEDCOM and Drag and Drop

This query changes only the GEDCOM include setting so that all fact types are included in both Export and DnD. Similar UPDATE queries could be done for other include settings.

-- Set all FactTypes to be included in GEDCOM and Drag and Drop
UPDATE FactTypeTable
 SET Flags = Flags +1
WHERE NOT Flags & 1
;

Restore Saved Fact Type Settings

Regardless of what changes have been made or how they were made, this query returns the FactTypeTable to the way it was when its then current settings were saved to a backup table.

-- Restore saved FactTypeTable settings
INSERT OR REPLACE INTO FactTypeTable
SELECT * FROM zFactTypeTable;

Other Ideas

Some may want to have more than one set of saved settings and be able to name or describe each set. That is possible; it would require another custom table and the addition of a SettingID column to the table that stores the multiple sets, or a different table for each set.
Direct interaction with the settings from a tabular screen – possible using a high level programming language

Discussions & comments from Wikispaces site

Shared Events With Missing Witnesses #sharedevent

Posted on 2013-04-262022-04-29 by Tom Holden

#RM8 compatible

RootsMagic 4 & 5 fail to clean up all the tables after some entities are merged or deleted. The new Database Tools don’t either. Apart from wasted disk space, there are other more concerning consequences. One is what has been called “Headless Witnesses” – these are RINs found in the WitnessTable for whom there is no matching person in the PersonTable. A result is that a fact may show that it is shared with someone whose name is missing.

The RMGC_Properties query reports a count of the number of sharings for which the RIN of the witness does not exist in the database. Its core statement can be brought out to inspect the RINs. Perhaps, by reviewing a database backup, the missing persons can be identified and the matter resolved.

-- List of RINs of Headless Witnesses
SELECT PersonID FROM WitnessTable
WHERE WitnessTable.PersonID > 0
EXCEPT
SELECT PersonID FROM PersonTable
;

A more comprehensive query was developed to report on the role, event and owner or principal of the shared event. Download TraceHeadlessWitnesses.sql . Here’s a sample result:

Using these results the search can be quickly narrowed to two shared Census facts for RIN 829. The Headless Witness can be removed and,
if necessary, replaced. The query is designed to report on shared family events, too, but there were no Headless Witnesses in the subject database.

Names – Add Married #names #alternatenames #marriage #spouse

Posted on 2013-04-262022-04-28 by Tom Holden

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

Having the married name of a person as an Alternate Name can be useful to those more familiar with it than the birth name as per LessTX's posting on the Wish List:
Add Married Name as Alternate Name and has been requested over the years in these postings found in the RootsMagic Forums. Or for looking up someone in the database from a source that cites a married name. While RootsMagic 6 currently does not index alternate names for reports, it does for its new Publish Online website generator. This query adds a Married name as an Alternate to those persons not already having one.

The Alternate Name fact generated fills the fields as follows:

Example of an Alternate Name fact created by the query.

Given Names: Husband's
Surname: Husband's
Name Type: Married
Prefix: Mrs.
Suffix: (Wife's_primary_surname, Wife's_Given)
Nickname: Wife's
Proof: empty
Date: MarriageDate
SortDate: After MarriageDate
Private: unchecked

The Name Index for RM Reports does not include Alternate Names but that for the Publish Online website of RM6 does; so Private is left unchecked.

The default sentence for this fact reads clumsily and adds nothing to a narrative; by setting the Custom sentence for each such fact to one space character " ", no sentence is outputted. However, the Alternate Name field values are outputted in Individual Summary and other table type reports.

Download

Name-Add_Married-RM8 2021-03-11 rev by Pat Jones for #RM8

Name-Add_Married-RM7 compatible with RM5 to RM7

Requires SQLiteSpy with the fake RMNOCASE collation.
DO NOT USE ON A DATABASE EARLIER THAN RM5.
USE RootsMagic File > Database tools > Rebuild Indexes after executing the query.

-- Name-Add_Married.sql
/*
2012-11-24 Tom Holden ve3meo
 
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.
 
Prefixes with "Mrs.".
Suffixes the birth name in parentheses ().
 
Assigns the Marriage date and sortdate to the Alternate Name.
 
Sets the fact to Not Private and the fact sentence to ' ', assuming the user wishes to have
the Married Name show on the Publish Online website Name Index but not in narrative reports. It will
show in other reports but alternate names do not come out in report Name Indexes.
 
Because the NameTable uses the RMNOCASE collation, the query first REINDEXes
the database against the fake RMNOCASE collation. Following the query,
it is essential to use RootsMagic's File > Database tools > Rebuild Indexes, i.e.,
do not use this procedure on databases prior to RootsMagic 5.
*/
 
-- Following deletes all Alternate Names of type Married
-- DELETE FROM NameTable WHERE NameType = 5 AND NOT +IsPrimary;
 
-- Name-Add_Married
REINDEX -- because following procedure does string matches on columns indexed using RMNOCASE
;
 
INSERT OR REPLACE INTO NameTable
SELECT
 NULL AS NameID,
 Wife.OwnerID AS OwnerID,
 Husband.Surname AS Surname,
 Husband.Given AS Given,
 'Mrs.' AS Prefix,
 '(' || Wife.Surname || ', ' || Wife.Given || ')' AS Suffix,
 Wife.Nickname AS Nickname,
 5 AS NameType,
 Event.DATE AS DATE, -- set to ',' for undated, Event.Date to match Marriage date.
 CASE Event.SortDate & 1023
  WHEN 1023 THEN Event.SortDate --
  ELSE Event.SortDate + 1
 END AS SortDate,
-- Event.SortDate +1 AS SortDate, -- set to 9223372036854775807 to sort with undated Alt Names, Event.SortDate to match Marriage
 0 AS IsPrimary,
 0 AS IsPrivate, -- set to 1 to make Private
 0 AS Proof,
 0.0 AS EditDate,
-- 'After [person:hisher] marriage, [person] was also known as [Desc].' AS Sentence,
 ' ' AS Sentence, -- a blank space to prevent the default sentence from being outputted
 '' AS Note,
 Wife.BirthYear AS BirthYear,
 Wife.DeathYear AS DeathYear
FROM NameTable Wife
INNER JOIN FamilyTable Family ON Wife.OwnerID=MotherID AND +Wife.IsPrimary
INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary
INNER JOIN EventTable Event ON Family.FamilyID = Event.OwnerID AND Event.EventType = 300
INNER JOIN PersonTable Person2 ON Wife.OwnerID = Person2.PersonID AND Person2.Sex=1
INNER JOIN PersonTable Person1 ON Husband.OwnerID = Person1.PersonID AND Person1.Sex=0
WHERE
 Wife.Surname NOT LIKE Husband.Surname AND
 Wife.OwnerID NOT IN
(
-- Wives with Alternate Surnames matching Husband's Surname
SELECT Wife.OwnerID --, Wife.IsPrimary, Wife.Given, Wife.Surname, Husband.Surname
FROM NameTable Wife
INNER JOIN FamilyTable ON Wife.OwnerID=MotherID AND NOT +Wife.IsPrimary
INNER JOIN NameTable Husband ON FatherID = Husband.OwnerID AND +Husband.IsPrimary
WHERE Wife.Surname LIKE Husband.Surname
   OR Wife.Surname LIKE Wife.Surname || '-' || Husband.Surname
)
;
 
--- BE SURE TO REBUILD INDEXES IN ROOTSMAGIC --

Sharable Events – Find and Convert to Shared #sharedevent

Posted on 2013-04-262022-04-29 by Tom Holden

Problem:

MarkVS posted:
I have still not tackled the task of going through my RM4 database to merge up events that were originally added separately for each individual (in RM3) but would have been added as a shared event in RM4, and RM5.

A Query that would show events that were likely to be suitable for merging would be very useful. In the vast majority of cases this would be a Census event – but it might apply to tohers.I was thinking along the lines of something that listed Census events where the following data was identical
a) the date of the event
b) Census type
c) identical address and place

Then sorted into date/address/place sequence so the likley identical events would be listed together for potential manual update in RM4/5.

Perhaps there would be a cleverer way to identify merge candidates across all Event types?

MVS.

Finding Sharable Events

SharableFacts2.sql for RM4 to RM7
SharableFacts2-RM8.sql for #RM8 2022-02-21

Working on Lee Irons request, I came up with this adaptation of the SourceList.sql query that displays and counts events having citations. His database, or at least those events he wants merged as shared events, is rigorously and systematically cited so that was the easier query to start with. The “Sharable” column lists the number of other persons or events having the same EventDate, “Cited by”, “Description”, “Event Place”, “Event Site”, “Source Name”, “Cit Fields” values.

Convert Sharable Events to Shared – A Start

Lee Irons posed the problem coming from Legacy Family Tree 7.5 that its nearest equivalent to RM’s shared events is to copy facts to different people. When imported to RootsMagic, it would be advantageous to merge these to shared facts. He outlined his preferred merging as:

This is how the merge would work for shared individual facts. Facts associated with different persons that are exactly the same in terms of Fact Type, Date, Place, Place Details, Description, and Sources but have different information for each person in the Note field would be merged into a single shared individual fact. This new shared indivdual fact would have the same Fact Type, Date, Place, Place Details, Description, and Sources as before, and all of the persons would be listed in the People Who Share This Fact with each person’s unique data from the Note field of their indivudal fact being moved to the Role Note for that person in the People who Share This Fact list of the new shared individual fact. The Note field for the new shared individual fact would be left blank.

This is how the merge would work for shared family facts. This function would merge macthing individual facts and a matching family fact into a new shared family fact. When there is (1) the same situation as identified above for individual facts and (2) the persons that have these matching individual facts are part of the same family and (3) there is a family fact with exactly the same Fact Name as the individual fact type and exactly the same Date, Place, Place Details, and Description as the matching indivdual facts of the persons in the same family, then all of these would be merged into a single shared family fact. The new shared family fact would (1) get the Family Fact Type, Date, Place, Place Details, Description, and Note of the original family fact, and (2) would get the source information of the matching individual facts, and (3) would have all of the individuals in the family who have a matching individual fact added to the People Who Share This Fact list of the new family fact, with each person’s unique data from the Note field of their indivudal fact being moved to the Role Note for that person in the People who Share This Fact list of the new shared family fact.

After many hours of feverish brain activity, I think I have a solution for RootsMagic 4-7 in
SharableEvents-Convert.sql (requires a RMNOCASE collation – see RMNOCASE – faking it in SQLiteSpy).
This query has been used with both RM4 and 5 but it does not yet do anything with media that may be linked to an event which has been deleted in favour of sharing another event. Media handling may have to be different for the two versions. Nor does it do anything about assigning roles to the sharers of an event – that could be automated with more grey cells fried. Already shared events that become sharers of another event have their own sharers reassigned to their new shared event; this appears to work correctly with limited testing.

One challenge was choosing which individual was to be the Principal for a group of sharable individual events. The eldest male was a natural choice for 19th century events but what if there was an older female, e.g., the widow with children – the eldest male might be a child. The algorithm I developed results in the eldest female as Principal if she is more than 10 years older than the eldest male. Of course that might result in the elderly woman being the Principal for a census event when she is resident in her son’s house.

As written, there is some duplication of procedures between the two blocks of code corresponding to Lee’s two merge outlines; these can be split apart and run independently. I would welcome any feedback on how it works with any database for which sharable events figure significantly.

Sample database imported from a GEDCOM from Legacy Family Tree 7.5

In the sample database Lee sent, about 3200 events were converted to sharers, resulting in a reduction of about 3200 citations. Everything else on the properties report remained the same.

Sample database Edit Person screen before and after conversion

As delivered, the GEDCOM resulted in identical Fact Names for both Person and Family facts for Cemetery, Occasion and Proximity. Renaming would distinguish them readily on the Edit Person screen but the Details give a clue. “Household of Irons, John …” is an Individual’s fact while “Household of Irons-Dunham, … – Henry A. Dunh…” is a Family fact. The highighted event in the Before window is an Indi fact that was converted in the After window to the highlighted share of a family fact. Hannah is one of 10 people sharing the family fact with the two Principals.

Sample database after conversion: the Role Note contains the Note formerly in the event which was deleted and replaced by sharing a master event.

With no automatically assigned role, and no role defined for this fact type, the program offers the opportunity to define and assign a new role type. However, it may not be necessary to do so if there is no intent to generate a sentence in reports. It is conceivable that the procedure could automatically assign a pre-existing role or even generate a new role but that may be more confusing than no role at all.

Discussions & comments from Wikispaces site

Running in RM6

arfeeld
29 April 2013 01:53:52

Has anyone had any luck running this against RM6.
I tried and some date is put in the tmp tables but nothing else seems to happen.
Using a Legacy 7.5 imported file.

Ron

ve3meo

ve3meo
29 April 2013 04:27:21

I just ran it against a RM6 database created by importing the same Legacy 7.5 GEDCOM as in the example and it had the same dramatic effect on File Properties and produced shared events. Perhaps your database has highly similar events that differ just enough that the algorithm does not accept them as sharable.

Tom

ve3meo

Inline comment: “about 3200 events were converted to sharers, resulting in a reduction of about 3200 citations. Everything else on the properties report remained the same”

ve3meo
04 September 2018 02:57:50

ve3meo Dec 17, 2011

The first draft of this page compared the wrong pair of database files before and after conversion. It understated the numbers of conversions and had an initially puzzling discrepancy in the number of sources.

SQLite Tools for RootsMagic

Four Little Queries #names #blanks #placedetails

Duplicate Name Search – query #duplicates #names

Media Type Reset #media #update

Media – Delete Unused #media #delete

MediaTags – Delete Personal Having Fact Duplicates #media

MediaTags – Copy Shared Facts Media To Sharee Personal #media #sharedevent

Fact Inclusion Controls #facttypes #events #gedcom #reports

List Include Settings

Save Current Fact Type Settings for Later Restoration

Set All Fact Types to be Included in GEDCOM and Drag and Drop

Restore Saved Fact Type Settings

Other Ideas

Discussions & comments from Wikispaces site

Shared Events With Missing Witnesses #sharedevent

Names – Add Married #names #alternatenames #marriage #spouse

Download

Sharable Events – Find and Convert to Shared #sharedevent

Problem:

Finding Sharable Events

Convert Sharable Events to Shared – A Start

Discussions & comments from Wikispaces site

Running in RM6

Inline comment: “about 3200 events were converted to sharers, resulting in a reduction of about 3200 citations. Everything else on the properties report remained the same”

Inline comments

List Include Settings

Save Current Fact Type Settings for Later Restoration

Set All Fact Types to be Included in GEDCOM and Drag and Drop

Restore Saved Fact Type Settings

Other Ideas

Discussions & comments from Wikispaces site

Having problems

Download

Problem:

Finding Sharable Events

Convert Sharable Events to Shared – A Start

Discussions & comments from Wikispaces site

Running in RM6

Inline comment: “about 3200 events were converted to sharers, resulting in a reduction of about 3200 citations. Everything else on the properties report remained the same”

Inline comments

Comment: The first draft of this page compared…