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

[Patrick Allen Zulueta]

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