Delete all facts containing specific text AND change fact type if it has a specific source
OK - I don't think these questions have been asked before.
I am still using RM7 until I finish researching for a book. I have a deadline and can't afford to spend time learning RM8/9, which I have purchased :).
Question 1: When importing some people, they have a Misc fact type and the description text is "American". Is there a way to automatically find anyone with the Misc fact type and specific text "American" and delete all of those facts from just those people? See pic Q1
Question 2: I think this happens sometimes when doing TreeShare ... or maybe it is also from imports. Anyway, some people have a Misc fact type and no description, but the source is either WWI or WWII draft registrations. Is there a way to change anyone with a Misc fact type that has a specific source to another fact type? See pic Q2
Many thanks,
Jaime
Uploaded files:
Question #1:
DELETE FROM EventTable
WHERE TRIM(Details) LIKE 'American'
AND EventType IN
(
SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE 'Misc'
)
;
If there are tags for citations, media, web to these facts, they are left dangling but harmless. RM's Delete Phantoms tool might clean them out.
Question #2 is more complicated to script but, yes, it is possible. Requires more detail:
- What is the precise name of the Source?
- Is it used exclusively for the subject facts?
- To what fact type are they to be converted?
Thank you for the first script 🙂
Re: Question #2
- The source is U.S., World War II Draft Cards Young Men, 1940-1947
- I am not sure I understand what you mean by is it used exclusively for the subject facts. The US WWII Draft Cards YM is also used for Military Draft Registration fact type and Birth fact type and Residence fact type.
- The fact type to convert is Military Draft Registration (abbrev Draft Reg)
Try this and see if it gets the results you want (backup!):
UPDATE EventTable
SET EventType =
(SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE 'Draft Reg')
WHERE EventID IN
(
SELECT EventID
FROM EventTable E
JOIN CitationLinkTable CL ON E.EventID=CL.OwnerID AND CL.OwnerType=2
JOIN CitationTable C USING(CitationID)
JOIN SourceTable S USING(SourceID)
WHERE E.EventType
IN
(
SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE 'Misc'
)
AND S.Name LIKE 'U.S., World War II Draft Cards Young Men, 1940-1947'
)
;
I get an error
No such table: CitationLinkTable
Ahh, I wrote it for RM8 9. Needs a change for RM7 which I'll get to later.
Here's the RM7 version:
UPDATE EventTable
SET EventType =
(SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE 'Draft Reg')
WHERE EventID IN
(
SELECT EventID
FROM EventTable E
JOIN CitationTable C ON E.EventID=C.OwnerID AND C.OwnerType=2
JOIN SourceTable S USING(SourceID)
WHERE E.EventType
IN
(
SELECT FactTypeID FROM FactTypeTable
WHERE Abbrev LIKE 'Misc'
)
AND S.Name LIKE 'U.S., World War II Draft Cards Young Men, 1940-1947'
)
;
The CitationLinkTable of RM8+ broke out the Links from the RM4-7 CitationTable which made for a lot of redundancy of the Citation data. This data 'normalisation' enabled 'reusable' Citations in RM8+ and can result in fewer single, 'master' Citations with many tags or links, rather than one tag per Citation.
Your explanation is totally lost on me 😉 but the script worked perfectly! Thank you so much for saving me tons of time!!!!!!!!
And here's a RM7 version that uses run-time variables as a general solution so that any Fact having a given source can be changed to a different Fact Type in the same set: Individual or Family. The FactType names entered are compared to the FactTypeTable.Abbrev column whose value is what appears in the Edit Person screen. The two FactType names and the SourceName entry fields accept the SQLite wild cards, %=any number of any characters, _(underscore)=1 of any character but might have disastrous results if used stupidly. I was looking back at Facts - Change Fact Type and recognised how cautious and maybe inexperienced I was with sqlite then. This single statement is a lot faster although I just realised writing this that I haven't tested whether an event with no citation can be changed by using just % as the source name.
To use this script, you'll need SQLite Studio or SQLite Expert or another manager that supports runtime variables. SQLite Spy does not, afaik.
UPDATE EventTable
SET EventType = (
SELECT FactTypeID
FROM FactTypeTable
WHERE Abbrev LIKE $FactTypeToChangeTO
)
WHERE EventID IN (
SELECT EventID
FROM EventTable E
JOIN
CitationTable C ON E.EventID = C.OwnerID AND
C.OwnerType = 2
JOIN
SourceTable S USING (
SourceID
)
WHERE E.EventType IN (
SELECT FactTypeID
FROM FactTypeTable
WHERE Abbrev LIKE $FactTypeToChangeFROM
)
AND
S.Name LIKE $HavingSourceNameLike AND
(
SELECT OwnerType
FROM FactTypeTable
WHERE Abbrev LIKE $FactTypeToChangeFROM
)
= (
SELECT OwnerType
FROM FactTypeTable
WHERE Abbrev LIKE $FactTypeToChangeTO
)
);