Blob Field BOOKS
Last fall I wrote a SQL Script to create nearly 3000 citations (updating the blob field)
That was quite successful. It created the Citations -- and I did the linking manually as I needed to fill in a a few missing details plus gave me chance review person / family. The past couple of months - that was was major focus.
The past couple weeks I have been building a few test Books. However, one of things lacking is the ability to add multiple people/chapters at once. And to update the title/person(or family). This could be done by via SQL using something link a union query.
I was wondering if anyone has experimented with anything with updating the BOOK records /blob field in the Config table?
Kevin
Not I. Had considered it in the pre-RM8 era but never got beyond a cursory inspection of the contents which I imagine would now be very different. So you may be the pioneer in developing something that reliably works with the blobs associated with a report.
Thanks Tom.
Was hoping someone had played with -- Creating Citations turned out relatively easy once I got my head wrapped our how the fields need to to be -- then pulling from the associated tables
Not sure if this was final version -- but this was one I tested
See attached -- but below is the part that did the blob
<Root><Fields>
<Field><Name>AccessType</Name><Value>web</Value></Field>
<Field><Name>AccessDate</Name><Value>' || strftime('%m/%d/%Y', ML.UTCModDate+2415018.5) ||' </Value></Field>
<Field><Name>memorial</Name><Value>' || Prefix || ' ' || Given || ' ' || Surname || ' ' || Suffix || ' (' || BirthYear || '-' || DeathYear || ' ) ' || ' </Value></Field>
<Field><Name>FaG</Name><Value>' || Details || ' </Value></Field>
<Field><Name>cmtrynm</Name><Value>' || (substr(pt.Name, 1, instr(pt.Name,', ')-1)) || ' </Value></Field>
<Field><Name>cmtryloc</Name><Value>' || (substr(pt.Name, instr(pt.Name,',')+2)) || ' </Value></Field>
<Field><Name>createdby</Name><Value></Value></Field>
<Field><Name>photoby</Name><Value></Value></Field>
</Fields></Root>
'
as Fields,
Reading your script, I infer that you have downloaded images from Find-a-Grave and tagged them to a custom Find-a-Grave event.
Using the UTCModDate from the tag in the MediaLinkTable to generate the "accessed" date for the citation is clever. It assumes the tag was made on the same date as the download which seems likely. While the UTCModDate in the MultimediaTable might more accurately contain the accessed date, that would involve another JOIN. Most accurate would be the date from the downloaded file itself but that adds even more complexity involving process outside of SQLite.
You've got an unnecessary constraint in your first JOIN "ON FT.Name LIKE '%grave%'" because the following WHERE includes "and FT.Name is NOT NULL and FT.Name = 'Find-a-Grave'". The first two are redundant; only the FT.Name = 'Find-a-Grave' constraint satisfies both of the others.
Did you write this up and post it somewhere? I don't see it on this site. It's a neat piece of work.
Thanks -- other than forum where else can I (or any user) post such work?
This is more specific to my case as I had created a Find-a-grave with the purpose of being able to create sources and citations late. However, it could be adapted to other purposes. I am going to write one later in Spring for WW draft cards for WW1 & WW2. Also for Marriage (might be a little trickier for family fact). And in summer or so hopefully for Census.
[EDIT]
I downloaded a PDF of the Find-a-grave details (Example attached) -- that is attached to Find-a-grave fact. The headstones are attached to Burial fact. The Citations - that process to link/tag and fix and media has been a manual process for now. The media of headstones can be attached to many people . I also in a few cases have found two memorial for same people that have not (or can not be merged).
Well, it seemed incidental to the OP in this discussion. You could post it under a new topic focussed on it. Or I could elevate you to an Author and you can post it to the Blog.