Need a tool to recover data
I need a tool to recover custom source data converted from a Family Historian gedcom to RM11. I used Richard Otter's python script to change the source template from "Free Form" to my custom templates. Now I have all my custom fields, but they are empty. The data was saved into the Source Comment note and the Citation's Detail Comment Note when I imported the gedcom. For the few I've already corrected manually, the data is stored in the "Fields" column in both the SourceTable and the CitationTable and are Blob. Is there any way to move the data into the fields via a script? This database has over 11,000 citations so copying this stuff manually would be extremely time consuming.
I tried to enter this in the Tool request page but kept getting an error "Nonce check failed"
I very much doubt that it is possible to do so. Slight possibility is if the text in the Comment fields is structured identically to your field definitions in your Source Template. You could post an example. Even if it is well structured, I suspect one would need to revert to a higher level language such as python to do the necessary parsing and XML creation.
A better solution could be at the GEDCOM level because that is FH's native database storage format. If the sources are structured in the GEDCOM in a fashion that could be translated to a RM compatible GEDCOM with a custom source template, that would bypass the structural collapse into Free Form.
Actually, the data placed in the citation note field is structured into a 2 column format- field name, field value.
One could just bundle that up into xml and you're done.
Also not clear how many source templates would need to be coded.
I'm surprised there isn't a better import mechanism available from FH.
"... he data placed in the citation note field is structured into a 2 column format- field name, field value. One could just bundle that up into xml and you're done."
That's a quote from Richard's message, but my question is for nconway567. That sounds like a pretty easy SQLite script. Are you looking for help in writing it yourself, or are you looking for someone to write it for you? Well, "pretty easy" can be tricky. The script would have to loop through the citation note field an unknown number of times to process each field name, field value pair. The easiest way to do that is probably a recursive CTE, and recursive CTE's sometimes make my head swim.
I am not importing into FH, I'm exporting from FH to RootsMagic - just to clarify. FH's export options for templates are Include, Exclude or copy template fields to Notes.
I have about 70 source templates, and I usually try to keep the same syntax, i.e., field name=EventD Display name = Event Date
field name=EventPL Display name = Event Place
I try to keep it simple, and no spaces in the field name.
Jerry - I wouldn't know what a recursive CTE is if it slapped me in the face LOL About the most I can do myself in SQL is maybe an Insert or Append script, and the last real coding I did was back in the early 90's using PERL.
I definitely need assistance here - this is way beyond my skill level!
The image for the Source Templates shows my Census template and in the citation image, you see the data gets dumped into the Detail comment. I've copied and pasted it all back into the appropriate fields as an example.
I have about 387 sources, and over 11,000 citations. I can manage doing the source details manually, but not the citations.
Uploaded files:
I see what needs to be done. The query needs to be structured as follows.
UPDATE CitationTable
SET Fields = some_value
WHERE some_condition
I know how to create some_value, although it will involve a recursive CTE and I'm not as fluent with recursive CTE's as I would like to be. But I'm not sure about some_condition. What can I test to determine which citations need this treatment and which do not?
By the way, some_value is going to equal <root><fields>...</fields></root> where the ... is a sequence of <field>...</field> values corresponding to the values found in CitationTable.Comment. That's where the recursive CTE will come into play. I just don't know how to test which citations need to be updated and which do not.