Forum

Please or Register to create posts and topics.

Rename Source Template Field Names

Has anyone got a query that can rename a FieldDefs (blob) in RM8 by referencing a specific field name?

Example

I have a current field name of [Cemetery] in TemplateId 163 that I wish to change to [Cem]

Over time I have created a number of unnecessary templates (bet no one has heard that before) and a quick query based on the above would be very useful so I can merge 2 similar templates with slightly different field names. I see Footnote, Short Footnote and bibliography may have to change to reflect the field name change. Regards Wayne b

I'd do this type of change using the Replace function to change "<FieldName>Cemetary</FieldName>" to "<FieldName>Cem</FieldName>" within the fielddefs field.

It's something like cast(Replace(cast(CitationTable.Fields as Text),'<FieldName>Cemetary</FieldName>','<FieldName>Cem</FieldName>') as blob) so you cast it to text, do the replace and cast it back to blob all in one line.

Though I couldn't see a Cemetary field in template 163 and my preference would be to not touch the built in templates but to copy that template and create what I wanted, then change the template id of the source from 163 to the new template

Wayne b has reacted to this post.
Wayne b

Adding to Pat's post, mods to the RM built-in templates are not carried over through GEDCOM or drag'n'drop transfers so it is unwise to edit them if data mobility between databases is wanted.

Wayne b has reacted to this post.
Wayne b

I have a python script that does what you want in a different way. As Tom recommended, don't modify the built-in source templates. Instead, create a new template that has the fields as you want and then use that template for the applicable sources.
Using the script involves two steps-

1- create a SQL select that returns the rowids of all of the sources that need fixing.

2- create a mapping of old field names to new field names.

Then run the script. I did this several moths ago and haven't done much with it since. Let me know if you're interested. I think it is documented enough to use. But you need to be able to validate that the result is what you want. (no warranties).

It's at-

https://github.com/ricko2001/Genealogy-scripts/tree/main/RM%20-Switch%20source%20template

Richard

Wayne b has reacted to this post.
Wayne b
Quote from Pat Jones on 2022-03-29, 1:47 am

I'd do this type of change using the Replace function to change "<FieldName>Cemetary</FieldName>" to "<FieldName>Cem</FieldName>" within the fielddefs field.

It's something like cast(Replace(cast(CitationTable.Fields as Text),'<FieldName>Cemetary</FieldName>','<FieldName>Cem</FieldName>') as blob) so you cast it to text, do the replace and cast it back to blob all in one line.

Though I couldn't see a Cemetary field in template 163 and my preference would be to not touch the built in templates but to copy that template and create what I wanted, then change the template id of the source from 163 to the new template

UPDATE SourceTemplateTable
SET (FieldDefs) = cast(Replace(cast(FieldDefs as Text),'<FieldName>Born</FieldName>','<FieldName>Birthdate</FieldName>') as blob) and

cast(Replace(cast(FieldDefs as Text),'<FieldName>Cem</FieldName>','<FieldName>Cemetery</FieldName>') as blob)
and

cast(Replace(cast(FieldDefs as Text),'<FieldName>Died</FieldName>','<FieldName>DeathDate</FieldName>') as blob)

WHERE TemplateID = 10090;

UPDATE CitationTable
SET (Fields) = cast(Replace(cast(CitationTable.Fields as Text),'<FieldName>Born</FieldName>','<FieldName>BirthDate</FieldName>') as blob)and
cast(Replace(cast(CitationTable.Fields as Text),'<FieldName>Cem</FieldName>','<FieldName>Cemetery</FieldName>') as blob)and

cast(Replace(cast(CitationTable.Fields as Text),'<FieldName>Died</FieldName>','<FieldName>DeathDate</FieldName>') as blob)
FROM SourceTable
WHERE SourceTable.SourceID = CitationTable.SourceID )
WHERE
EXISTS (
SELECT *
FROM SourceTable
WHERE (SourceTable.SourceID = CitationTable.SourceID)
AND SourceTable.TemplateID = 13258
);

Very bad coding but it is the best I can do and you guessed it it does not work. Any help would be appreciated. Regards Wayne b

Hi Wayne,

I've been unable to comment for several days. Please post an example from a record in each table of what you have and what you want. I think what you are trying to do is to revise a source template and every source and citation that use it and that the changes are in the names of fields, e.g.,

Born => Birth Date
Cem => Cemetery
Died => Death Date

One constraint is that field names must be one word so "Birth Date" is not allowed but "BirthDate" is.

Thx for the reply Tom. I replied yesterday however I did not notice that the Upload files does not except Source Template Files hence it looks like nothing was uploaded. I will upload the main doc again . Essentially the new query would run individually. Somehow I need to get the .rmst files to you.

 

Hope all is well.

Regards Wayne b

Uploaded files:
  • Billiongraves.jpg