Logo Home    Page list

People Who Share A Fact with a Principal, But Who Are Not in a Tree in The File List - Query #sharedevent #person

Sharing of facts was a new feature added with RootsMagic 4. A Person or Family in the database (a Principal) is now able to share a fact with others either in a tree in the database file by linking to them or with those not in a tree in the file by indicating name only. Those people sharing the event with the Principal are each assigned a role in that fact/event.

Reporting and other capabilities within the program relative to shared facts haven't yet been fully developed, and there's currently no way to produce information relating to those not in a tree in the file (as of current version RM 4.0.7.1). However, the following SQL code can be run against the database in order to obtain that information, as well as relevant fact and Principal data:
FactsSharedWithTreelessPersons-OO.sql Download this variant compatible with OpenOffice Base when used in Run SQL Direct mode.
RMtrix_tiny_check.png - includes these people in Report > Lifelines of a principal.
-- People Who Share A Fact with a Principal, But Who Are Not in a Tree in The File List
-- created by romermb on 26 Feb 2010
 
-- Individual Facts
SELECT   WitnessTable.Surname COLLATE NOCASE AS Surname,
         WitnessTable.Suffix COLLATE NOCASE AS Suffix,
         WitnessTable.Prefix COLLATE NOCASE AS Prefix,
         WitnessTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         NULL AS MRIN,
         NameTable.OwnerID AS RIN1,
         NameTable.Surname COLLATE NOCASE AS Surname1,
         NameTable.Suffix COLLATE NOCASE AS Suffix1,
         NameTable.Prefix COLLATE NOCASE AS Prefix1,
         NameTable.Given COLLATE NOCASE AS Given1,
         NULL AS RIN2,
         NULL AS Surname2,
         NULL AS Suffix2,
         NULL AS Prefix2,
         NULL AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN NameTable ON
         EventTable.OwnerID = NameTable.OwnerID
WHERE    WitnessTable.PersonID = 0 AND EventTable.OwnerType = 0 AND
         NameTable.IsPrimary = 1
 
UNION ALL
 
-- Family Facts
SELECT   WitnessTable.Surname COLLATE NOCASE AS Surname,
         WitnessTable.Suffix COLLATE NOCASE AS Suffix,
         WitnessTable.Prefix COLLATE NOCASE AS Prefix,
         WitnessTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         FamilyTable.FamilyID AS MRIN,
         NameTable1.OwnerID AS RIN1,
         NameTable1.Surname COLLATE NOCASE AS Surname1,
         NameTable1.Suffix COLLATE NOCASE AS Suffix1,
         NameTable1.Prefix COLLATE NOCASE AS Prefix1,
         NameTable1.Given COLLATE NOCASE AS Given1,
         NameTable2.OwnerID AS RIN2,
         NameTable2.Surname COLLATE NOCASE AS Surname2,
         NameTable2.Suffix COLLATE NOCASE AS Suffix2,
         NameTable2.Prefix COLLATE NOCASE AS Prefix2,
         NameTable2.Given COLLATE NOCASE AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN RoleTable ON
         WitnessTable.ROLE = RoleTable.RoleID
         LEFT OUTER JOIN EventTable ON
         WitnessTable.EventID = EventTable.EventID
         LEFT OUTER JOIN FactTypeTable ON
         EventTable.EventType = FactTypeTable.FactTypeID
         LEFT OUTER JOIN FamilyTable ON
         EventTable.OwnerID = FamilyTable.FamilyID
         LEFT OUTER JOIN NameTable AS NameTable1 ON
         FamilyTable.FatherID = NameTable1.OwnerID
         LEFT OUTER JOIN NameTable AS NameTable2 ON
         FamilyTable.MotherID = NameTable2.OwnerID
WHERE    WitnessTable.PersonID = 0 AND EventTable.OwnerType = 1 AND
         NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
 
ORDER BY 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 14, 15, 16, 17

Discussions

ve3meo

Nomenclature

ve3meo 27 February 2010 01:36:59

Another good query!

I wonder, 'tho', if the language could be a little more precise. There is no word 'sharee' but there is 'sharer'. These Fact or event sharers not currently reported by RM are in the file and in the database but are not persons in the database trees. A more accurate name would be 'List Fact Sharers not in Database Trees - Query' and the description could be made clearer by revising accordingly.

romermb
romermb 01 March 2010 20:11:57

I actually intentionally used the same terminology used in RM4 in the Edit shared event screen -- This person is not in my file.

Sharee (vs. Sharer) was used to try to differentiate the person sharing in the fact (vs. the one sharing the fact). However, I just looked and realized that it's not an actual word! I was unable to find it in the dictionary.

Anyway, I grant that the wording could be made better, so will look to improve upon it here shortly.

Thanks.

www.000webhost.com