Move Citation Research Note to Page Number

#1 · 2024-12-12, 9:39 am

Hello, it's me again!

In RM 7 I used the Free Form source template for the newspaper articles I uploaded manually and I put the date and page numbers in a field (I can't remember which). When I upgraded to RM 10 this info ended up in the Citation Research Notes. Is there a way to move the info from Research Notes to Page Number? This involves hundreds of citations. See example.

Thanks,

Jaime

Uploaded files:

#2 · 2024-12-12, 10:47 am

It's doable but tricky. The data will have to be moved from CitationTable.Actual Text to CitationTable.Fields. The reason it's tricky is that the ActualText is an SQLite TEXT column containing plain text and the Fields column is an SQLite BLOB column containing XML strings. I can probably put something together in a day or two that will do what you need.

Almost certainly, the data was the same way in RM7. Namely, the page number stuff was almost certainly in the Research Note in RM7 (the ActualText column from the point of view of SQLite). Otherwise, it wouldn't have come over to RM10 the way it did. Are you sure you want to change it?

#3 · 2024-12-16, 10:02 pm

Quote from thejerrybryan on 2024-12-16, 10:02 pm
It took me a little longer to get back to this than expected. First, here is a little script to test the logic of the WHERE clause that is needed to update the page number from the Research Note to the actual page number. It verifies that the Research Note has the string "page", that the actual page number is blank, and that the free form template is in use. It displays all the columns you need to verify.
SELECT ActualText,
CAST(Fields AS TEXT) AS TextFields,
REPLACE(CAST(Fields AS TEXT),
'<Name>Page</Name><Value></Value>',
'<Name>Page</Name><Value>' || ActualText || '</Value>') AS NewTextFields
FROM CitationTable
WHERE CitationID IN
(
SELECT CitationID
FROM CitationTable
JOIN SourceTable ON SourceTable.SourceID = CitationTable.SourceID
AND CitationTable.ActualText LIKE '%page%' -- reseach note has a page number
AND CAST(CitationTable.Fields AS TEXT) LIKE '%<Name>Page</Name><Value></Value>%' -- actual page number is blank
AND TemplateID = 0 -- free form template in use
)
If you are satisfied with that, then here is the actual update.
UPDATE CitationTable
SET Fields = CAST( REPLACE(CAST(Fields AS TEXT),
'<Name>Page</Name><Value></Value>',
'<Name>Page</Name><Value>' || ActualText || '</Value>') AS BLOB),
ActualText = ''
WHERE CitationID IN
(
SELECT CitationID
FROM CitationTable
JOIN SourceTable ON SourceTable.SourceID = CitationTable.SourceID
AND CitationTable.ActualText LIKE '%page%' -- reseach note has a page number
AND CAST(CitationTable.Fields AS TEXT) LIKE '%<Name>Page</Name><Value></Value>%' -- actual page number is blank
AND TemplateID = 0 -- free form template in use
)
I cast CitationTable.Fields to TEXT, replace the text that needs to be replaced, and then cast it back to BLOB. I'm not sure the casting is strictly necessary, but I like to color within the lines on something like this.
One caution is that I'm not sure how happy RM is going to be with this UPDATE script if the Research Note contains any new line characters or any tab characters - anything like that. But blanks are fine. So take this caution into account. It would be pretty easy to remove any such characters as the data is being copied, but since I don't know if it's issue in your database, my script doesn't worry about it.

It took me a little longer to get back to this than expected. First, here is a little script to test the logic of the WHERE clause that is needed to update the page number from the Research Note to the actual page number. It verifies that the Research Note has the string "page", that the actual page number is blank, and that the free form template is in use. It displays all the columns you need to verify.

SELECT ActualText,
CAST(Fields AS TEXT) AS TextFields,
REPLACE(CAST(Fields AS TEXT),
'<Name>Page</Name><Value></Value>',
'<Name>Page</Name><Value>' || ActualText || '</Value>') AS NewTextFields
FROM CitationTable
WHERE CitationID IN
(
SELECT CitationID
FROM CitationTable
JOIN SourceTable ON SourceTable.SourceID = CitationTable.SourceID
AND CitationTable.ActualText LIKE '%page%' -- reseach note has a page number
AND CAST(CitationTable.Fields AS TEXT) LIKE '%<Name>Page</Name><Value></Value>%' -- actual page number is blank
AND TemplateID = 0 -- free form template in use
)

If you are satisfied with that, then here is the actual update.

UPDATE CitationTable
SET Fields = CAST( REPLACE(CAST(Fields AS TEXT),
'<Name>Page</Name><Value></Value>',
'<Name>Page</Name><Value>' || ActualText || '</Value>') AS BLOB),
ActualText = ''

WHERE CitationID IN
(
SELECT CitationID
FROM CitationTable
JOIN SourceTable ON SourceTable.SourceID = CitationTable.SourceID
AND CitationTable.ActualText LIKE '%page%' -- reseach note has a page number
AND CAST(CitationTable.Fields AS TEXT) LIKE '%<Name>Page</Name><Value></Value>%' -- actual page number is blank
AND TemplateID = 0 -- free form template in use
)

I cast CitationTable.Fields to TEXT, replace the text that needs to be replaced, and then cast it back to BLOB. I'm not sure the casting is strictly necessary, but I like to color within the lines on something like this.

One caution is that I'm not sure how happy RM is going to be with this UPDATE script if the Research Note contains any new line characters or any tab characters - anything like that. But blanks are fine. So take this caution into account. It would be pretty easy to remove any such characters as the data is being copied, but since I don't know if it's issue in your database, my script doesn't worry about it.