Merging similar Master Source records
I have a ton of Source citations in my database now, largely imported from Family Search, which are a MESS! As an example, let's look at a Census record. It presents in the RM GUI with a name like "Fred Smith in household of George Smith, Census of United States, 1910" and the footnote, short footnote and bibliography fields are all long and complicated and they are identical.
I want to change all such Master Source titles to be just "Census, Untied States, 1910" and I think I can do that with some SQL. But ....
But then I want to merge all the resulting same-named Master Source entries. Sounds do-able since RM knows how to merge sources but some of the citation specific information is in the Master Source record NOT in the Source detail record, so there's some tough parsing to do and splitting info between fields. Moreover, some of those fields are in XML not plain text!
Anyone know how to do that kind of parsing and transfer of information in an SQL script environment?
Had a thought today ... what if I exported the complete Source tables (master and detail) into Excel? I have better tools, functions, macros, scripts to manipulate text there, and I'm more familiar with it. Then I could do a lot of reworking the titles and parsing/moving fields between master and detail as we've discussed.
Then I put it all back into the RM tables, totally replacing what was there before but OF COURSE keeping the ID numbers 100% unchanged. After that, the merging can be done, with some painful manual clicking and poking, completely within RM and I'd trust that to maintain all the database integrity.
Have you done any of that? Export - manipuate - replace?
I have not, at least not recently. You can connect Excel to the database through the SQLite ODBC connection (look up the #msexcel tag) which is great for reading a table and you can edit fields but you will run into a problem editing name-type fields that are indexed by RMNOCASE or deleting/inserting records containing one. I don't recall if anyone solved that. But I think you could do the editing of the existing fields fields in SourceTable and CitationTable that way and then change the SourceTable.name field using a SQLite manager with the fake RMNOCASE.
A full or partial export to Excel, edit therein, import to a temp table and UPDATE of the permanent table is perhaps the surest way to go.
What you need obviously is to Replace the SourceID of the citationtable by another.
In the source table, you can look at the ID you want to replace by another: If you want the people using source 10, 15 and 20 to use the number 75 instead (75 being your nice clean "Census, Untied States, 1910"), you can use
Update CitationTable Set SourceID = 75 Where SourceID in (10,15,20)
but you might still have specific text of the citation you don't like.
For that I would check ActualText of the CitationTable and use
UPDATE CitationTable SET ActualText IS 'New Sentence' WHERE ActualText IS 'Old Sentence'
to replace the full cell.
you can use like '%WordToFind%' to identify the text that contains a specific word.