Delete event description if contains specific text
This pertains to RM7
I am very familiar with and use the script EventDescriptionToNote-Move.sql.
I have used it with the Residence fact to hide the census data that comes from treeshare by moving the description to the note and then not including notes in the narrative report.
But frequently I write my own description with details that I want to keep in the Residence description and include in reports. So I don't want these descriptions to be hidden in the narrative reports.
Hopefully this makes sense, but if not, please ask for clarification or examples.
Jaime
So my question is, can a script be written to delete event descriptions that contain specific text? I have started a list of keywords:
Age:
StreetAddress:
Marital Status:
Residence Post Office:
GradeCompleted:
OccupationCategory:
Occupation:
EnumerationDistrict:
AttendedSchool:
ClassofWorker:
Relation to Head:
Age in (one space afterwards)
PartyAffiliation:
I think you need something like the following:
UPDATE EventTable
SET Details = '' -- this is two consecutive single quotes
WHERE Details LIKE '%Age:%' OR
Details LIKE '%StreetAddress:%'; -- etc.
I may need to be corrected, but I don't see a good way to do it with an IN construction to give you your list of text that triggers the deletion. I would certainly run a SELECT first with the same WHERE condition to be sure I was getting the correct EventTable.Details text that needed to be deleted.
Thank you Jerry. That looks like the beginning of what I am needing, but if you recall, I don't know SQL (wish I did!) and need a complete script. Jaime
The script is pretty much complete as is. I just didn't list all your conditions, such as OR LIKE '%Marital Status:%' OR LIKE '%Residence Post Office;%' etc. You would need to type all them into your script, separated with an OR.
But my suggestion was first to run a pure query first something like
SELECT *
FROM EventTable
WHERE Details LIKE '%Age:%' OR
Details LIKE '%StreetAddress:%'; -- etc.
This would be just to be sure you have identified the correct Details to be deleted. Again, you need to type in all the conditions that should cause deleting the Details, each one with the '% ... %' construction and each separated with OR. After this query is correct, just copy and paste the conditions into the UPDATE script.
The script is not really "deleting" the Details. It's setting the Details to a zero length character string.
Jerry, thanks a million!!!!!!! It worked perfectly! Don't know that I'll need to do this, but is there a way to undo it?
Not unless you have a backup you can restore.
I was being very literal in responding to your request, but a different way of doing it might have been to move the Details data to the front of the Note so that the data is retained but is stored in the Note instead of in the Details. I can't remember for sure, but Tom may already have such a script.
A nice nuance to such a script might be to move the Details information to the front of the Note fields surrounded by {privacy braces} in the Note so that you could choose not to print the data in reports but still keep it around.
I am happy with the way it works and don't want to mix my details into the notes. Rarely do I want the details. When I run treeshare and a residence record is created I look at it and if there is anything pertinent (like occupation) that I want to keep, I create a new fact and put the info there.
Thanks SO MUCH for your help with this!!
Adding to @thejerrybryan note-- I would also suggest running Straight SELECT query before using in UPDATE query -- just to make sure everything looks as expected. Especially when using LIKE etc.
Kevin
I don't know how to write SQL. I have been dependent on the wonderful people on this site to help me get special tasks done that RM can't or won't do. Could you please write the lines I would need to do this? And would it be a separate script that I would run first to check the results before running Jerry's script? Thanks so much.