Fact type code numbers (& hiding event descriptions w/ specific text)
A while back Jerry Bryan created a nifty little script for me that I might be able to use for other things. As you see below, it specifies which fact type to manipulate. Is there a chart anywhere of all fact types with their code number?
Below is the script because I think he emailed it to me and didn't put it on this site. Background: I don't like all of the detail included when I download a census record via Treeshare. I used to use a script to move it to event notes, but really never used the info and just wanted to get rid of it. It's always there online if I ever really need it.
Hide event description (details) with specific text.sql (Thank you again Jerry!)
UPDATE EventTable
SET Details = ''
WHERE EventType = 29 /* Residence fact */
AND Details LIKE '%Census%' OR
DETAILS LIKE '%Age%' OR
DETAILS LIKE '%StreetAddress%' OR
DETAILS LIKE '%Residence Post Office%' OR
DETAILS LIKE '%Marital Status%' OR
DETAILS LIKE '%GradeCompleted%' OR
DETAILS LIKE '%OccupationCategory%' OR
DETAILS LIKE '%Occupation%' OR
DETAILS LIKE '%EnumerationDistrict%' OR
DETAILS LIKE '%AttendedSchool%' OR
DETAILS LIKE '%ClassofWorker%' OR
DETAILS LIKE '%Relation to Head%' OR
DETAILS LIKE '%PartyAffilation%' OR
DETAILS LIKE '%Total%' ;
"Is there a chart anywhere of all fact types with their code number?"
Use ==> Select * from FactTypeTable;
The Data Dictionary webpage provides additional information on column details - https://sqlitetoolsforrootsmagic.com/rm9-data-dictionary/
I use SQLiteSpy. I don't even have to do a SELECT * FROM FactTypeTable to get the FactTypeID for each fact. I can just double click on FactTypeTable. I suspect that most any SQLite manager will have the same capability.
However, for the most part I don't do it that way anymore. What I do instead is to do a JOIN between EventTable and FactTypeTable. That way, I never have to lookup or even to know the FactTypeID. For example
SELECT E.*
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
AND FT.Name LIKE 'Birth'
And of course I replace 'Birth' with the name of my fact type of interest.
To do an UPDATE, it's something like the following, again replacing 'Birth' with the name of my fact type of interest.
UPDATE EventTable
SET some_column = some_value
WHERE EventID IN
(
SELECT E.EventID
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
AND FT.Name LIKE 'Birth'
)
Thanks Kevin McLarnon. This is quite a bit over my head, but I thank you for the info. Maybe someday I will understand it all, but in the meantime I'll go with Jerry's suggestion. I very much appreciate your time! Jaime