Remove Fact Note, Add Source instead
I can do some very simple queries, and modify some existing 'code' that ya'all have published in the past to try to do something more specific to me, but I don't know where to begin with this one. So I'll ask if there's anyone out there willing to spend a little of their time trying to help me out.
I have several facts where, instead of using a source, I have used a note. Specifically, I have used 5 very specific notes instead of using 5 generic sources. I would like to shift over from using notes to using generic sources/citations. I have created those source/citations, and I can use them going forward, but it would be nice if I could modify all of the existing facts to:
- Remove the specific text from the note in the fact. The note may have other text before and/or after it, or it may not. If additional text after the specific text, there's usually a CR/LF (or whatever is stored for a line break)
- Add the generic source/citation to the fact.
One specific example, the most prolific, occurring 783 times:
Remove the text: "Ancestry.com. U.S., Index to Public Records, 1994-2019"(and any line break)
Add the source # 105865 / citation # 127945
(This particular Ancestry source has info for recent residence and birth month/year, so I use it a lot for those 2 facts. I also the marriage, marriage index, and divorce sources for associated RM facts.)
I can figure out how to change the search/remove text and change the source/citation number for the other 4 common text notes.
Thanks in advance!
Chad
I'm not volunteering just yet, but let's make sure I understand the requirement.
The last thing to do would be to remove the text Ancestry.com. U.S., Index to Public Records, 1994-2019 783 different times. This would have to be the last thing to do because that's the data that's needed to identify where the newly added citations would go. I just wanted to clarify that that's the exact text to delete, and that there is no page number or other identifying information that's different for each of the 783 different times.
That's the crux of the matter, because if the text is truly identical each time, then the only thing that has to be done before deleting the fact note is to add an entry to the CitationLinkTable for each of the facts where the note appears. But if there is other identifying information that also has to be deleted from the fact note, then that information would have be added to 783 different citations that would have to be added to the CitationTable.
I knew you'd step forward Jerry. Thanks! But what I should have done is tried my hand at AI. It hallucinated several times, including not following the schema I pointed it to here in the website's DataDictionary.
After some back and forth calling out it's mistakes, it came up with:
BEGIN TRANSACTION;
------------------------------------------------------------
-- 1. Capture EventIDs containing the specific note text
------------------------------------------------------------
CREATE TEMP TABLE _TargetEvents AS
SELECT EventID
FROM EventTable
WHERE Note LIKE '%Ancestry.com. U.S., Index to Public Records, 1994-2019%';-- Optional: inspect how many events will be affected
SELECT COUNT(*) AS TargetEventCount FROM _TargetEvents;------------------------------------------------------------
-- 2. Remove the specific note text, leaving other text/line breaks intact
------------------------------------------------------------
UPDATE EventTable
SET Note = TRIM(REPLACE(Note, 'Ancestry.com. U.S., Index to Public Records, 1994-2019', ''))
WHERE EventID IN (SELECT EventID FROM _TargetEvents);-- Optional: set now-empty notes to NULL
UPDATE EventTable
SET Note = NULL
WHERE EventID IN (SELECT EventID FROM _TargetEvents)
AND TRIM(COALESCE(Note, '')) = '';------------------------------------------------------------
-- 3. Insert citation links for these events
-- Using Excel-style serial number for UTCModDate
------------------------------------------------------------
INSERT INTO CitationLinkTable
(CitationID, OwnerType, OwnerID, SortOrder, Quality, IsPrivate, Flags, UTCModDate)
SELECT
127945, -- CitationID
2, -- OwnerType = Event
te.EventID, -- OwnerID
0, -- SortOrder
'~~~', -- Quality
0, -- IsPrivate
0, -- Flags
(julianday('now') - julianday('1899-12-30')) -- UTCModDate as Excel-style serial
FROM _TargetEvents te
WHERE NOT EXISTS (
SELECT 1
FROM CitationLinkTable cl
WHERE cl.CitationID = 127945
AND cl.OwnerType = 2
AND cl.OwnerID = te.EventID
);------------------------------------------------------------
-- 4. Verification queries
------------------------------------------------------------
-- Check number of citation links added
SELECT COUNT(*) AS CitationLinksAdded
FROM CitationLinkTable
WHERE CitationID = 127945
AND OwnerType = 2
AND OwnerID IN (SELECT EventID FROM _TargetEvents);-- Spot-check a few events
SELECT e.EventID, e.Note
FROM EventTable e
WHERE e.EventID IN (SELECT EventID FROM _TargetEvents)
LIMIT 10;------------------------------------------------------------
-- 5. Cleanup temporary table
------------------------------------------------------------
DROP TABLE _TargetEvents;------------------------------------------------------------
-- 6. Finalize
------------------------------------------------------------
-- COMMIT; -- Run this when satisfied
-- ROLLBACK; -- Run this to undo
I of course kept an original copy, and I'm soon going to load it up in RM and see how it did (I replaced the 783, plus another 200+ of one of the other text phrases and new source/citation).
Sorry for the ugly formatting, I didn't see a button for a code block.