Report to find which templates include a certain product and/or report to find templates that include inactive products

Hi,

Is there a report or a way to find which templates include a certain product? I want to change the product on some of templates (but not change the product completely) and would save time not having to go through one by one looking for them.

Also, is there a report or a way to find templates that include any inactive products?

 

Greta

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Report to find which templates include a certain product ...

I'm not aware of any report that does this.

If you want to have a go at generating such a report, some basic SQL to generate a listing of templates and their included products is shown below.

Alternatively, if you want fund development of a report, please let us know at support[at]openvpms[dot]com. Please consider contributing it back to the project so other users can benefit.

-Tim

select template.entity_id template_id,
       template.name template_name,
       template.active template_active,
       product.entity_id product_id,
       product.name product_name,
       product.active product_active
from entities template
join entity_links includes
    on includes.source_id = template.entity_id
        and includes.arch_short_name = 'entityLink.productIncludes'
join entities product
    on product.entity_id = includes.target_id
where template.arch_short_name = 'product.template'
    order by template.name, template.entity_id, product.name, product.entity_id

Re: Report to find which templates include a certain product ...

G'day Greta,

Is this the type of report you were after?  It uses TimA's SQL, but needs a little more tidy-up, like parameters for selecting templates and maybe better labels for the fields.

Simon

AttachmentSize
templatesWithProductsA4v1.jrxml 14.62 KB

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Re: Report to find which templates include a certain product ...

That is exactly what I was after, thanks so much Simon!

Re: Report to find which templates include a certain product ...

Here is a better version with parameters to select active templates and names of templates and products.  Note that using the latter will only show those producs in each template, omitting any other product contained in the same template.

Given that this would be for internal use, the margins are small.  If printed, there should be enough other space for annotating etc.

Simon

AttachmentSize
templatesWithProductsA4v2.jrxml 17.72 KB

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Syndicate content