[Vtigercrm-developers] Query error in Reports using vtiger_inventoryproductrel custom field

[Patrick Allen Zulueta]

I create a custom field in table vtiger_inventoryproductrel called "fecha"
wich is a date field used in PurchaseOrder module.

When I create a report with this field in filters and I put Fecha equal
(some date) it works well, but when I put Fecha between two dates or today,
the report gives me an error.  Does some one has experimented something
like this?

Image 1: Works well with a specific date.
[image: imagen.png]

Image 2: Doesnt works well. I get an error that this field doesnt exist on
vtiger_inventoryproductrel table but it exist.
[image: imagen.png]

Here is the query and error:
SELECT vtiger_purchaseorder.subject AS 'PurchaseOrder_Subject',
vtiger_purchaseorder.purchaseorder_no AS 'PurchaseOrder_PurchaseOrder_No',
(CASE WHEN vtiger_purchaseorder.vendorid NOT LIKE '' THEN (CASE WHEN
trim(vtiger_vendorRelPurchaseOrder.vendorname) NOT LIKE '' THEN
trim(vtiger_vendorRelPurchaseOrder.vendorname) ELSE '' END) ELSE '' END) AS
'PurchaseOrder_Vendor_Name', vtiger_purchaseorder.duedate AS
'PurchaseOrder_Due_Date', vtiger_purchaseordercf.cf_1050 AS
'PurchaseOrder_Fecha_de_compra_o_factura',
vtiger_inventoryproductreltmpPurchaseOrder.fecha AS 'PurchaseOrder_Fecha',
vtiger_crmentity.crmid AS "PurchaseOrder_LBL_ACTION" from
vtiger_purchaseorder inner join vtiger_crmentity on
vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid left join
vtiger_inventoryproductrel as vtiger_inventoryproductreltmpPurchaseOrder on
vtiger_purchaseorder.purchaseorderid =
vtiger_inventoryproductreltmpPurchaseOrder.id left join
vtiger_purchaseordercf on vtiger_purchaseorder.purchaseorderid =
vtiger_purchaseordercf.purchaseorderid left join vtiger_groups on
vtiger_groups.groupid = vtiger_crmentity.smownerid left join vtiger_users
on vtiger_users.id = vtiger_crmentity.smownerid left join vtiger_vendor as
vtiger_vendorRelPurchaseOrder on vtiger_vendorRelPurchaseOrder.vendorid =
vtiger_purchaseorder.vendorid WHERE vtiger_purchaseorder.purchaseorderid >
0 AND vtiger_crmentity.deleted=0 and (( vtiger_inventoryproductrel.fecha
BETWEEN '2021-01-12' AND '2021-01-12' ) ) LIMIT 0, 500::->Unknown column
'vtiger_inventoryproductrel.fecha' in 'where clause'

If I change manually vtiger_inventoryproductrel.fecha by
vtiger_inventoryproductreltmpPurchaseOrde.fecha it works well.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210112/3d3302cd/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: imagen.png
Type: image/png
Size: 130240 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210112/3d3302cd/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: imagen.png
Type: image/png
Size: 125000 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210112/3d3302cd/attachment-0003.png>