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

Thu Feb 18 15:18:22 GMT 2021

 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/
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20210218/7191d3d9/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/20210218/7191d3d9/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/20210218/7191d3d9/attachment-0003.png>