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

[Uma S]

Hi Patrick,

Can I know that you have created a custom field through script or Layout
editor?

Is the case same with module filters as well?

On Wed, Jan 13, 2021 at 3:01 AM Patrick Allen Zulueta <
alexander.allenz at gmail.com> wrote:

> 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.
> _______________________________________________
> http://www.vtiger.com/



-- 
With
Best Regards
Uma.S
Vtiger Team
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210113/5266a5e1/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/20210113/5266a5e1/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/20210113/5266a5e1/attachment-0003.png>