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

Mon Feb 22 13:36:56 GMT 2021

But due date is a field in table vtiger_purchaseorder. My custom field is
in vtiger_inventoryproductrel.  I only have error in this date field using
the condition between two days.

El lunes, 22 de febrero de 2021, Uma S <uma.s at vtiger.com> escribió:

> Hi Patrick,
>
> I tried reproducing this issue with Purchaseorder reports, with
> conditions set to Today and Between in the Modify Conditions block where
> that works fine.
>
> [image: Screenshot from 2021-02-22 18-45-59.png]
>
> On Thu, Feb 18, 2021 at 8:54 PM Patrick Allen Zulueta <
> alexander.allenz at gmail.com> wrote:
>
>> Thank you. Any clue what I am missing? I only have the sql error when I
>> create a report with this date field using the clause "between" two dates,
>> if I use the clause "is" date, doesnt generate the error.
>>
>> El jue, 18 feb 2021 a las 9:17, Patrick Alexander Allen (<
>> patrick at coazgt.com>) escribió:
>>
>>> Thank you. Any clue what I am missing? I only have the sql error when I
>>> create a report with this date field using the clause "between" two dates,
>>> if I use the clause "is" date, doesnt generate the error.
>>>
>>> El mié, 17 feb 2021 a las 11:58, Uma S (<uma.s at vtiger.com>) escribió:
>>>
>>>> Hi Patrick,
>>>>
>>>> Sorry! adding a custom field to the Line Items block of inventory
>>>> module is not supported.
>>>> As the dependencies of these fields are bound.
>>>>
>>>> On Thu, Feb 11, 2021 at 10:56 PM Patrick Allen Zulueta <
>>>> alexander.allenz at gmail.com> wrote:
>>>>
>>>>> Hello Uma. I triead this way: The field is created but in
>>>>> vtiger_salesorder table, not in vtiger_inventoryproductrel.  Can you tell
>>>>> me what have I to change?
>>>>>
>>>>> ini_set('memory_limit', '10240M');
>>>>> set_time_limit(0);
>>>>> ini_set('mysql.connect_timeout','600');
>>>>> ini_set('max_execution_time', '500000');
>>>>> $today = date("Y-m-d H:i:s");
>>>>> require_once 'config.inc.php';
>>>>> require_once('modules/Emails/mail.php');
>>>>> if (file_exists('config_override.php')) { include_once
>>>>> 'config_override.php'; }
>>>>> require_once 'includes/Loader.php'; vimport
>>>>> ('includes.runtime.EntryPoint');
>>>>> global $current_user;
>>>>> global $adb;
>>>>> $db = PearDatabase::getInstance();
>>>>> $current_user = Users::getActiveAdminUser();
>>>>> require_once 'vtlib/Vtiger/Utils.php';
>>>>> $moduleName='SalesOrder';
>>>>> $moduleInstance = Vtiger_Module::getInstance($moduleName);
>>>>> $blockInstance = Vtiger_Block::getInstance('LBL_ITEM_DETAILS',
>>>>> $moduleInstance);
>>>>> $fieldInstance = new Vtiger_Field(); $fieldInstance->name = 'test';
>>>>> $fieldInstance->table = $moduleInstance->basetable;
>>>>> $fieldInstance->column = 'test';
>>>>> $fieldInstance->label='test';
>>>>> $fieldInstance->columntype = 'DATE';
>>>>> $fieldInstance->uitype = 23;
>>>>> $fieldInstance->typeofdata = 'D~O';
>>>>> $fieldInstance->readonly = '1';
>>>>> $fieldInstance->presence = '2';
>>>>> $fieldInstance->displaytype = '1';
>>>>> $fieldInstance->masseditable = '0';
>>>>> $blockInstance->addField($fieldInstance);
>>>>> echo "Field added.";
>>>>> exit;
>>>>>
>>>>> El mié, 13 ene 2021 a las 10:52, Patrick Allen Zulueta (<
>>>>> alexander.allenz at gmail.com>) escribió:
>>>>>
>>>>>> 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
>>>>>>>
>>>>>> _______________________________________________
>>>>> http://www.vtiger.com/
>>>>
>>>>
>>>>
>>>> --
>>>> With
>>>> Best Regards
>>>> Uma.S
>>>> Vtiger Team
>>>> _______________________________________________
>>>> http://www.vtiger.com/
>>>
>>> _______________________________________________
>> 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/20210222/3ec25ab6/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/20210222/3ec25ab6/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot from 2021-02-22 18-45-59.png
Type: image/png
Size: 99139 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210222/3ec25ab6/attachment-0004.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/20210222/3ec25ab6/attachment-0005.png>