Fix & Merge Hundreds of Newspapers.com Sources
I can't believe no one else has asked this before. Maybe there is an obvious answer that I'm missing or it doesn't bother anyone but me.
Whenever I save a clipping from Newspapers.com to a person in my Ancestry.com tree and then copy via Treeshare to my Rootsmagic 7 database, it creates a brand new source. I now have hundreds of these individual sources (see attached).
Using this example:
Newspapers.com - The Waxahachie Daily Light - 15 Feb 1902 - Page 2
Is there a way to:
- Remove the "Newspapers.com - " text from the Source Name?
- Assuming #1 can be done, could you then move the " - 15 Feb 1902 - Page 2" from the source name to Source Detail?
- If the above 2 steps can be done, then I could merge all of the "The Waxahachie Daily Light" sources into one and my bibliography would be a lot shorter.
Keep in mind, as mentioned above, that I'm using RM 7 🙂
As always, I am most appreciative of the brilliant SQL gurus on this site!
Jaime
P.S. I figured out one way to accomplish #1. I exported the RM7 file to a gedcom, opened the gedcom file and did a search/replace to eliminate the unwanted "Newspapers.com - " text, then imported the gedcom into a new RM database. Still, it would be nice to be able to do this in SQL so I didn't have to export/import my entire file every time I wanted to update the source names.
Uploaded files:
You might want to broaden the scope to review that all sources have the desired amount of lumping vs splitting as well as the right amount of unique data in citation detail fields (ie Research Notes, Citation Comments, and other template-defined source detail fields). This will position you to take advantage of new capabilities when you migrate from RM7.
As I recall, the way clippings from City Directory collections come across via treeshare present even more of an issue than Newspaper.com clippings.
Also, you don't need to merge duplicate sources with SQL as that function exists in RM7. Some people prefer to wait and do it in RM8/9 because you have better visibility into what you are merging.
I realize that you are hoping for a finished query, however, if you are interested in playing around with solving this, here are a few queries that might help you get started. The solution takes a 3-step approach.
First step is to create a sql view that shows where each source has been cited along with important fields. I included a script to view event citations and another to view citations added to the Person. (These "Show" sql queries were originally created by Tom Holden in something he posted and then were modified a bit by me. )
The second step is to copy this SQL view to excel (or your favorite spreadsheet program), evaluate the data, and make changes. For me, this was easier to do in excel, since I am more familiar with "find" and "replace" excel commands and I could work iteratively. Basically, you create an "Output" column that will contain the citation detail that you want to add. A second output column would contain the final source name that you want. You might also decide to create more than 1 output column for the citation data. For example, if you wanted to add something like "sourced from newspaper.com" into the Citation Comments field. (Also remember that when you migrate from RM7 the Citation Name will get initially built from several fields, including this citation detail field in the Ancestry Record template.)
The last step is to update the RM tables. Create a 2- column, comma separated file derived from the excel output. The first column - c0 - defines record key for the appropriate table and the second column - c1 - contains the value to be updated. (c0 and c1 are the default variable names since I didn't bother with column headers.) The attached "Add" query updates the LinkAncestry table whereas the task you describe will need to update the Citation table with new details and then update the Source table to modify the source name. So you'll need to modify the sql to update source and citation tables. It's probably simplest to create 2 separate "Add" queries and csv files.
All the normal caveats of backups and using a copy db apply.
Uploaded files:Quote from jlodge on 2023-04-20, 8:52 pm...it would be nice to be able to do this in SQL so I didn't have to export/import my entire file every time I wanted to update the source names.
...and suffer the losses that occur in a GEDCOM transfer (GEDCOM & DnD transfer losses).
Are these sources in Free-form or a template (which one?).
Kevin has given you some some good things to consider. I wonder if there might be a simpler solution. Iirc, RM reports list the distinctly different Bibliography sentences just once. Maybe a solution would be to simply change the Bibliography sentences. Try this experiment. You show only two sources from the Enterprise-Journal so edit each one's Bibliography sentence to read what you want (you might only be able to do that in Free-form) for their common Bibliography. Then do a report that cites both sources and see if there is only the one entry for them in the Bibliography. If so, then a simpler solution is possible.
They are an Ancestry record template.
I appreciate all of the input, but this is getting very complicated and beyond my understanding.
Template-based makes it a harder proposition because the Bibliography sentence cannot be overridden at the Source level.
I understand that this is more complicated than you would have wished and probably want a canned solution. I could have a stab at it as a low priority so no promises on a timeline or a satisfactory outcome. A description of the data is not enough; I would need your database to work with.
Thanks for understanding my technical limitations 😉 and thanks for your tentative offer. I certainly understand that you have better things to do! I am happy to give you a copy of my database to play with at your leisure. Please send an email to ***** and I will send you a cloud link. Jaime
This took more hours than I would like to repeat but I've put together a procedure, scripts and a writeup at Sources - Newspapers.com - Cleaner Footnotes and Simpler Bibliography. I hope it all works out satisfactorily.