I have updated this template to resolve issue with product information not displaying correctly

Hi Tony,

Is it possible to update this report to list products which do not have a product type set? Currently they are not listed.

Matt.

Hi Matt,

Yes.  I believe you just need to change the report query to look like this. 

select e.entity_id as pid, e.name as product, l.name as uom, sl.name as stockLocation, if(d2.value,d2.value,"0") as currentStock
from
entities e inner join products p on e.entity_id = p.product_id
left outer join entity_relationships r on e.entity_id = r.target_id and r.arch_short_name = "entityRelationship.productTypeProduct"
left outer join entities pt on r.source_id = pt.entity_id
left join entity_details d on d.entity_id = e.entity_id and d.name = "sellingUnits"
left join lookups l on l.code = d.value and l.arch_short_name = "lookup.uom"
left outer join entity_relationships s on s.source_id = e.entity_id and s.arch_short_name = "entityRelationship.productStockLocation"
left join entity_relationship_details d2 on s.entity_relationship_id = d2.entity_relationship_id and d2.name = "quantity"
left join entities sl on sl.entity_id = s.target_id
where e.name like $P{productName} and
e.active = true and
((pt.name like $P{productType}) or (pt.name is null)) and
(
    (e.arch_short_name = "product.medication" and $P{includeMedications}) or
    (e.arch_short_name = "product.merchandise" and $P{includeMerchandise})
)
order by e.name

Tell me if it works as expected :-)

Cheers

Tony

Thanks Tony,

it now works as expected. I have attached the report with the new query which includes products without a product-type as well.

Matt.