Rename Source Template Field Names
Quote from Wayne b on 2022-03-24, 10:49 pmHas 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
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
Quote from Pat Jones on 2022-03-29, 1:47 amI'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
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
Quote from Tom Holden on 2022-03-29, 9:41 amAdding 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.
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.
Quote from Richard Otter on 2022-04-03, 10:38 pmI 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
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
Quote from Wayne b on 2022-05-11, 10:26 pmQuote from Pat Jones on 2022-03-29, 1:47 amI'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) andcast(Replace(cast(FieldDefs as Text),'<FieldName>Cem</FieldName>','<FieldName>Cemetery</FieldName>') as blob)
andcast(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)andcast(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
Quote from Pat Jones on 2022-03-29, 1:47 amI'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
Quote from Tom Holden on 2022-05-15, 10:21 amHi 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 DateOne constraint is that field names must be one word so "Birth Date" is not allowed but "BirthDate" is.
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.
Quote from Wayne b on 2022-05-16, 9:06 pmThx 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
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: