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

[Patrick Allen Zulueta]

No, in PurchaseOrder module but in elements block (next to product name,
quantity, etc). To create the field I follow this procedure:


https://danssy.wordpress.com/2017/01/26/vtiger-6-5-add-column-to-item-details-in-invoice-salesorder-quotes/

Image added by reference.


El miércoles, 13 de enero de 2021, Uma S <uma.s at vtiger.com> escribió:

> 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/ffbde843/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/ffbde843/attachment-0003.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/ffbde843/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: E0787BFB-1A1E-4708-885A-5FD4BDA9C21A.png
Type: image/png
Size: 167772 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210113/ffbde843/attachment-0005.png>