entering missing data on a group of people

#1 · 2025-05-17, 11:46 am

I want to enter missing data onto people under a specific tag (not everyone) using the insert command.

I have got the eventtable linked to the group table, thus:

How do I use the group table or tag table to set a list of names and then insert the missing event description?

When I use

SELECT * FROM grouptable gt
inner join PersonTable pt on pt.personid=gt.startid
inner join EventTable et on pt.personid=et.ownerid
where gt.groupid=1013

I get 671 returns, when the group in RM shows 146 people.

#2 · 2025-05-17, 3:14 pm

You need a single JOIN to the GroupTable with a lower limit to the range and an upper limit to the range in the same JOIN. Each of your separate JOIN's on GroupTable has a range which unlimited either to the left or to the right. That is why you are getting too many matches.

The following works for me. And of course you can JOIN the GroupTable to many tables other than the PersonTable. Many other tables have the PersonID as a foreign key called OwnerID

SELECT P.PersonID
FROM PersonTable AS P
JOIN GroupTable AS G ON G.StartID <= P.PersonID AND P.PersonID <= G.EndID
JOIN TagTable AS T ON T.TagType = 0 AND T.TagValue = G.GroupID
AND T.TagName LIKE 'descendants of g grandparents'

kevync and Ali Christie-Upton have reacted to this post.

#3 · 2025-05-17, 3:34 pm

If you've not already seen it, this post from Tom may also be helpful: Copy Fact to Group

Ali Christie-Upton has reacted to this post.

#4 · 2025-05-17, 4:37 pm

There is some ambiguity in your description that needs clearup.

GroupTable: GroupID=1013 contains the people of interest on whom you wish to operate. So the list of people in that group would be:

SELECT pt.PersonID, nt.Given, nt.Surname
FROM PersonTable pt
JOIN NameTable nt ON pt.PersonID=nt.OwnerID AND nt.IsPrimary
JOIN GroupTable gt ON pt.PersonID BETWEEN gt.StartID AND gt.EndID
WHERE gt.GroupID=1013
;

Take out the Given and Surname fields to get just the list of PersonIDs.

The EventTable has events not only for individuals but for families, characterised by OwnerType. Moreover, it supports events of many types identified by EventType. Without appropriate constraints, your query is returning all types of events for persons and for families. That's why it returns way more than you expect.

Suppose you are interested only in EventType= 26 = FactTypeTable.FactTypeID "Occupation".

SELECT * FROM EventTable et
WHERE et.EventType=26 --constrain to one type
AND et.OwnerType=0 --assigned to individuals only

lists all the "Occupation" events for all individuals; to filter for just the individuals in the group you could add the constraint:

AND et.OwnerID IN
(SELECT pt.PersonID
FROM PersonTable pt
JOIN GroupTable gt ON pt.PersonID BETWEEN gt.StartID AND gt.EndID
WHERE gt.GroupID=1013
)
;

Insert Description: Sounds like you already have events for the group but some of them want the description added. You won't be Inserting the Description (Column name is Details), rather you will be UPDATEing the event record by SETting the Details column. Something like this:

UPDATE EventTable SET Details='blah,blah'
WHERE Details LIKE '' --avoid altering non-empty field
AND EventID IN
(SELECT EventID from the list of events of the given type for the given group)

Ali Christie-Upton has reacted to this post.

#5 · 2025-05-17, 5:06 pm

Quote from Kevin McLarnon on 2025-05-17, 3:34 pm
If you've not already seen it, this post from Tom may also be helpful: Copy Fact to Group

Hi Kevin

I did read it, but it is about the fact being the same, rather than inserting different facts into the same field for different people (i.e. I'm putting the ancestry name into a field I've created from an excel list)

Kevin McLarnon has reacted to this post.

#6 · 2025-05-17, 5:18 pm

Quote from Tom Holden on 2025-05-17, 4:37 pm
There is some ambiguity in your description that needs clearup.
GroupTable: GroupID=1013 contains the people of interest on whom you wish to operate. So the list of people in that group would be:
SELECT pt.PersonID, nt.Given, nt.Surname
FROM PersonTable pt
JOIN NameTable nt ON pt.PersonID=nt.OwnerID AND nt.IsPrimary
JOIN GroupTable gt ON pt.PersonID BETWEEN gt.StartID AND gt.EndID
WHERE gt.GroupID=1013
;
Take out the Given and Surname fields to get just the list of PersonIDs.
The EventTable has events not only for individuals but for families, characterised by OwnerType. Moreover, it supports events of many types identified by EventType. Without appropriate constraints, your query is returning all types of events for persons and for families. That's why it returns way more than you expect.
Suppose you are interested only in EventType= 26 = FactTypeTable.FactTypeID "Occupation".
SELECT * FROM EventTable et
WHERE et.EventType=26 --constrain to one type
AND et.OwnerType=0 --assigned to individuals only
lists all the "Occupation" events for all individuals; to filter for just the individuals in the group you could add the constraint:
AND et.OwnerID IN
(SELECT pt.PersonID
FROM PersonTable pt
JOIN GroupTable gt ON pt.PersonID BETWEEN gt.StartID AND gt.EndID
WHERE gt.GroupID=1013
)
;
Insert Description: Sounds like you already have events for the group but some of them want the description added. You won't be Inserting the Description (Column name is Details), rather you will be UPDATEing the event record by SETting the Details column. Something like this:
UPDATE EventTable SET Details='blah,blah'
WHERE Details LIKE '' --avoid altering non-empty field
AND EventID IN
(SELECT EventID from the list of events of the given type for the given group)

Thank Tom.