People+Who+Share+a+Fact+with+a+Principal+List+-+Query

=People Who Share A Fact with a Principal List - Query=

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:

code format="sql" -- 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 code