Creating template.xml from installed templates

Submitted by mckelvey on Sun, 01/03/2015 - 03:16

Implementation discussion

Greetings all...

At last count I have made changes to nearly 60 document templates for the Canadian group. Just looking ahead and wondering... is there a utility floating around that creates a templates.xml from the existing templates installed within the database? It looks like all the relevant information for each template is available in the Edit Document Template window. It seems that would be a great addition as it would make importing of the existing templates into 1.8 much less of a headache... unless there is already a straightforward way without importing by hand or without creating a templates.xml by hand.

Thanks,

Sam

Kamloops, BC

Re: Creating template.xml from installed templates

Submitted by tgething (not verified) on Sun, 01/03/2015 - 04:57.

Ahah - been there, done that. I had a similar problem during the time I was building the conversion suite for Hong Kong. I ended up building an Excel spreadsheet that could be used to generate the xml file, and I cloned this to generate the xml file used in the 1.8 release. That is, I do not have a tool that will look at an existing system and generate the xml file, but rather an easily maintained spreadsheet that can be used to generate the xml file.

I have attached the xls file used to generate the xml file for the 1.8 release (where we have diverged from earlier releases and changed the reports directory structure - see http://www.openvpms.org/documentation/csh/1.8/admin/template ).

This file has a good readme sheet which explains how things work. The magic is the formula used to generate the xml - see for example cell A5 of sheet templates-A4.xml [Note that the only difference between the A4, A5 and Letter sheets is that they look in columns E, F & G respectively of the Templates sheet.

For your use, replace the data in the Templates sheet with your own stuff, set the column E entry to A4 and use the templates-A4.xml sheet. This will mean that you do not get a paper size added to the template name.

Note that this is not a total solution if you need to have different templates set for different practice locations. I used Kettle to automate this.

Regards, Tim G

Attachment	Size
Templates.xls	197.5 KB

Re: Creating template.xml from installed templates

Submitted by mckelvey on Sun, 01/03/2015 - 09:28.

Thanks Tim,

This looks great in that it should definitely avoid the keying errors likely in editing the xml directly. Looks like it will still take a bit of detective work to figure out which template file I assigned to which template, but this should be a big time saver in the long run! Probably easier to maintain than initially set up.

Thanks loads.

Sam

Re: Creating template.xml from installed templates

Submitted by tgething (not verified) on Sun, 01/03/2015 - 11:16.

Sam - herewith some sql that will dump your existing template info.

Regards, Tim G

select e.entity_id as id, e.name as name, e.description as description,
     d1.value as archetype, d2.value as paperSize, d3.value as reportType,
    a.name as content    
from entities e
join entity_details d on e.entity_id = d.entity_id
join entity_details d1 on e.entity_id = d1.entity_id and d1.name = 'archetype'
join entity_details d2 on e.entity_id = d2.entity_id and d2.name = 'paperSize'
join entity_details d3 on e.entity_id = d3.entity_id and d3.name = 'reportType'
join participations p on p.entity_id = e.entity_id
join acts a on a.act_id = p.act_id and a.arch_short_name = 'act.documentTemplate'
where e.arch_short_name = 'entity.documentTemplate'
group by e.entity_id;

Re: Creating template.xml from installed templates

Submitted by mckelvey on Mon, 02/03/2015 - 01:15.

Works great! That's a very useful little bundle.

Thanks for all your help Tim!

Sam

Re: Creating template.xml from installed templates

Submitted by mckelvey on Fri, 23/10/2015 - 08:56.

Tim G...

I've come back this thread as I am in the process of migrating to 1.8, cleaning up all my templates and setting up a templates.xml file to speed import in the future. Would like to use the tools that you've provided above. I've run your sql query, but in going through it by stepping through the Edit Document Template windows, I find that some of the templates are not getting picked up by the query... most notably the sub-report templates. Not all, just some. It picked up my counterSaleItems subreport, but not the invoiceItems, creditItems, or estimateItems. I don't see anything different in their declaration that would cause them to be skipped over. I also see that other reports are being dropped... such as debitAdjustment.

It's a pretty useful query. Anything I should do to make sure that all the templates get picked up?

Thanks,

Sam

Re: Creating template.xml from installed templates

Submitted by tgething (not verified) on Fri, 23/10/2015 - 09:30.

Sam - you are my nemesis - another piece of erroneous SQL from TG Enterprises. The problem was the joins - should have been left joins as some of the things (eg paper size) may not be set.

The corrected SQL is:

select e.entity_id as id, e.name as name, e.description as description,
     d1.value as archetype, d2.value as paperSize, d3.value as reportType,
    a.name as content, e.active as active    
from entities e
left join entity_details d1 on e.entity_id = d1.entity_id and d1.name = 'archetype'
left join entity_details d2 on e.entity_id = d2.entity_id and d2.name = 'paperSize'
left join entity_details d3 on e.entity_id = d3.entity_id and d3.name = 'reportType'
left join participations p on p.entity_id = e.entity_id
left join acts a on a.act_id = p.act_id and a.arch_short_name = 'act.documentTemplate'
where e.arch_short_name = 'entity.documentTemplate'
group by e.entity_id
order by d1.value, e.name;

In my system this retrieved 231 records whereas the original SQL got only 124. The 231 matches the number of templates shown by AdministrationTemplates (provided that I turn on Active=Both).

Apologies, Tim G