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

[Uma S]

You mean create a field for Products module?

On Wed, Jan 13, 2021 at 9:03 PM Patrick Allen Zulueta <
alexander.allenz at gmail.com> wrote:

> Ok. For inventory Related modules does this work? This is a field that I
> insert in product elements. How I specify that ?
>
> El miércoles, 13 de enero de 2021, Uma S <uma.s at vtiger.com> escribió:
>
>> Hi Patrick,
>>
>> Sorry! That will not work, As related dependencies of the field are not
>> set to it's standard.
>>
>> Please do follow the docs
>> <https://community.vtiger.com/help/vtigercrm/developers/vtlib/module-field.html>
>> .
>>
>> On Wed, Jan 13, 2021 at 8:35 PM Patrick Allen Zulueta <
>> alexander.allenz at gmail.com> wrote:
>>
>>> Hi Uma. I didn’t. I create it manually inserting on table vtiger_field,
>>> vtiger_inventoryproductrel and also modifying Editviewutils.php and
>>> InventoryUtils.php
>>>
>>> El miércoles, 13 de enero de 2021, Uma S <uma.s at vtiger.com> escribió:
>>>
>>>> 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
>>>>
>>> _______________________________________________
>>> http://www.vtiger.com/
>>
>>
>>
>> --
>> With
>> Best Regards
>> Uma.S
>> Vtiger Team
>>
> _______________________________________________
> 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/01fb3578/attachment-0001.html>
-------------- 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/01fb3578/attachment-0002.png>
-------------- 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/01fb3578/attachment-0003.png>