[Vtigercrm-developers] Query error in Reports using vtiger_inventoryproductrel custom field
Uma S
uma.s at vtiger.com
Tue Feb 23 17:29:46 GMT 2021
Hi Patrick,
Can you get the glimpse of the executed query, Is the data type of field is
set to datetime?
On Mon, Feb 22, 2021 at 7:12 PM Patrick Allen Zulueta <
alexander.allenz at gmail.com> wrote:
> 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
>>
> _______________________________________________
> 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/20210223/9704ae7e/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/20210223/9704ae7e/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/20210223/9704ae7e/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/20210223/9704ae7e/attachment-0005.png>
More information about the vtigercrm-developers
mailing list