Logo Home    Page list

People Who Share a Fact with a Principal 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 (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:

RMtrix_tiny_check.png
-- People Who Share A Fact with a Principal List
-- created by romermb on 10 Mar 2010
 
-- Individual Facts, Person Sharing Fact in Tree in File
SELECT   WitnessTable.PersonID AS RIN,
         NameTable.Surname COLLATE NOCASE AS Surname,
         NameTable.Suffix COLLATE NOCASE AS Suffix,
         NameTable.Prefix COLLATE NOCASE AS Prefix,
         NameTable.Given COLLATE NOCASE AS Given,
         RoleTable.RoleName COLLATE NOCASE AS ROLE,
         FactTypeTable.Name COLLATE NOCASE AS Fact,
         NULL 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,
         NULL AS RIN2,
         NULL AS Surname2,
         NULL AS Suffix2,
         NULL AS Prefix2,
         NULL AS Given2
FROM     WitnessTable
         LEFT OUTER JOIN NameTable ON
         WitnessTable.PersonID = NameTable.OwnerID
         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 AS NameTable1 ON
         EventTable.OwnerID = NameTable1.OwnerID
WHERE    WitnessTable.PersonID <> 0 AND EventTable.OwnerType = 0 AND
         NameTable.IsPrimary = 1 AND NameTable1.IsPrimary = 1
 
UNION ALL
 
-- Family Facts, Person Sharing Fact in Tree in File
SELECT   WitnessTable.PersonID AS RIN,
         NameTable.Surname COLLATE NOCASE AS Surname,
         NameTable.Suffix COLLATE NOCASE AS Suffix,
         NameTable.Prefix COLLATE NOCASE AS Prefix,
         NameTable.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 NameTable ON
         WitnessTable.PersonID = NameTable.OwnerID
         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
         NameTable.IsPrimary = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1
 
UNION ALL
 
-- Individual Facts, Person Sharing Fact Not in Tree in File
SELECT   NULL AS RIN,
         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, Person Sharing Fact Not in Tree in File
SELECT   NULL AS RIN,
         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 2, 3, 4, 5, 6, 7, 10, 11, 12, 13, 15, 16, 17, 18

Discussions

weaberj

Add date field to query

weaberj 17 May 2011 18:53:51

I have been using the query "People Who Share A Fact with a Principal List" for data entry checking for shared events. I find it very useful except for one field which is, at least for my purposes, missing. That field is the date of the event. One parent or couple can share several censuses with a child and while all are listed in the query results it would be a lot easier if the date were also there. I would modify the query myself except it does not query the event table which contains the fact date and I'm not sure how to construct the correct links. If anyone has some spare time (ha, ha) and can add the date, well, that would be great.
Thanks, John Weaber
jweaber@gmail.com

ve3meo
ve3meo 18 May 2011 12:01:18

If you are content with the raw date data, it is probably not a big deal to add a date column; decoding the raw data into more human readable form is. You can see what's involved on the Date Decoder page. I wonder if one of the Lifelines queries might answer your needs - they include decoded dates and shared events. Copy and paste results into Excel or Calc and you can sort and filter.

Tom

microzoa

Thanks

microzoa 08 January 2012 16:46:13

Just a short word of thanks - this query is perfect for what i need. Thanks for taking the time. Hopefully I can add to the list at some point.

ve3meo
ve3meo 08 January 2012 18:37:38

Romer authored this query and I am sure he shares my appreciation for your word of thanks. His query found its way into several other more complex ones.

Feel free to contribute to the wiki something you have come up with at anytime. The more, the merrier!

www.000webhost.com