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

Wed Feb 17 17:56:25 GMT 2021

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