SQL code to enter data into a fact based on a colour code.

#1 · 2025-07-11, 8:40 am

I have colour coded my family tree and have a fact called Family.

I would like a sqlite code so that I can enter the family surname into the fact for records that have a certain colour.

For example - all records that have colour code 9 would have the surname "Purves" entered into the Family fact.

Is this possible? Thanks in advance. I do have some knowledge of writing sql code.

#2 · 2025-07-11, 9:02 am

Your "Family" fact is presumably a custom, individual-type fact type. While a query could work from its name to get its FactTypeID, it's simpler if you encode it in the query.
Does everyone who is to receive "Purves" already have that fact type?
Is it the Description field that is to receive "Purves"?
What Color code set number is your color code in?

Nampara has reacted to this post.

#3 · 2025-07-12, 4:58 am

Thanks for your reply

1 - yes it is a custon individual-type fact - Fact type id is 1051

2 - yes everyone in my database has this fact

3 - yes the description field is to receive the surname

4 - color code set 1

Thanks again

#4 · 2025-07-14, 3:49 pm

This should do what you ask for. If, however, your fact type is to append "Purves" to pre-existing values, you would need to revise it.

UPDATE EventTable SET Details='Purves'
WHERE EventType=1051
AND
(SELECT P.Color=9 FROM PersonTable P
WHERE EventTable.OwnerID=P.PersonID
)
;

#5 · 2025-07-15, 4:01 am

Hi Tom

Thanks very much for this - worked perfectly.

Thanks