Update problem with financial fields

[partnerwerk]

I have got a problem with updating update no. cbupd-0000225

This is the error message I get:

Code:

Creating Field sum_tax1 (MwSt.) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_tax4 (erm. USt.) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_taxtotal (Total Tax) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_taxtotalretention (Total Tax Retention) on Invoice ... **ERROR**
A field with that name or label already exists.
result is not an object

What can I do to make the update run?

Since having this update not working I cannot save invoices without the following error message:

Code:

Fatal error: Uncaught Exception: result is not an object in [path]/include/database/PearDatabase.php:826
Stack trace:
#0 [path]/include/utils/InventoryUtils.php(683): PearDatabase->query_result(false, 0, 'tax4')
#1 [path]/include/setVAT.php(164): getInventoryProductTaxValue('279609', '4457', 'tax4')
#2 [path]/modules/com_vtiger_workflow/VTEntityMethodManager.inc(50): setVAT(Object(VTWorkflowEntity))
#3 [path]/modules/com_vtiger_workflow/tasks/VTEntityMethodTask.inc(23): VTEntityMethodManager->executeMethod(Object(VTWorkflowEntity), 'Set Financial F...')
#4 [path]/modules/com_vtiger_workflow/VTWorkflowManager.inc(477): VTEntityMethodTask->doTask(Object(VTWorkflowEntity))
#5 [path]/modules/com_vtiger_workflow/VTEventHandler.inc(92): Workflow->performTasks(Object(VTWorkflowEntity))
#6 [path]/include/events/VTEven in [path]/include/database/PearDatabase.php on line 826

Any help is appreciated.

---

Probably this thread needs to be moved to Administrator support? My mistake.

[joebordes]

the error shouldn't be a problem, they are simply saying that the fields already exist, not a problem. In fact, I'd say that the changeset has been applied.

The error is somewhere else. From the error, I'd say something to do with tax4, some custom tax field you have. There is another changeset about adding deleted taxes, has that one been executed correctly?

[partnerwerk]

Joe, thank you for your reply.

All changesets have been applied correctly except ”addFinancialFields” which is marked ”pending” although it looks like the fields have been created. shouldn't the updater script recognize the correct creation of the fields and then return ”update done”. But this is what I get:

Code:

Updating Financial fields for Invoice
Total Number of updates executed : 1
Correct Updates : 0
Failed Updates : 1

These are the fields in the vtiger_invoice table:

 

Code:

`invoiceid` int(19) NOT NULL DEFAULT '0',
 `subject` varchar(100) DEFAULT NULL,
 `salesorderid` int(19) DEFAULT NULL,
 `customerno` varchar(100) DEFAULT NULL,
 `contactid` int(19) DEFAULT NULL,
 `notes` varchar(100) DEFAULT NULL,
 `invoicedate` date DEFAULT NULL,
 `duedate` date DEFAULT NULL,
 `invoiceterms` varchar(100) DEFAULT NULL,
 `type` varchar(100) DEFAULT NULL,
 `adjustment` decimal(28,6) DEFAULT NULL,
 `salescommission` decimal(25,3) DEFAULT NULL,
 `exciseduty` decimal(25,3) DEFAULT NULL,
 `subtotal` decimal(28,6) DEFAULT NULL,
 `total` decimal(28,6) DEFAULT NULL,
 `taxtype` varchar(25) DEFAULT NULL,
 `discount_percent` decimal(25,3) DEFAULT NULL,
 `discount_amount` decimal(28,6) DEFAULT NULL,
 `s_h_amount` decimal(28,6) DEFAULT NULL,
 `shipping` varchar(100) DEFAULT NULL,
 `accountid` int(19) DEFAULT NULL,
 `terms_conditions` text,
 `purchaseorder` varchar(200) DEFAULT NULL,
 `invoicestatus` varchar(200) DEFAULT NULL,
 `invoice_no` varchar(100) DEFAULT NULL,
 `currency_id` int(19) NOT NULL DEFAULT '1',
 `conversion_rate` decimal(10,3) NOT NULL DEFAULT '1.000',
 `tandc` int(11) DEFAULT NULL,
 `pl_gross_total` decimal(25,6) DEFAULT NULL,
 `pl_dto_line` decimal(25,6) DEFAULT NULL,
 `pl_dto_global` decimal(25,6) DEFAULT NULL,
 `pl_dto_total` decimal(25,6) DEFAULT NULL,
 `pl_net_total` decimal(25,6) DEFAULT NULL,
 `pl_sh_total` decimal(25,6) DEFAULT NULL,
 `pl_sh_tax` decimal(25,6) DEFAULT NULL,
 `pl_adjustment` decimal(25,6) DEFAULT NULL,
 `pl_grand_total` decimal(25,6) DEFAULT NULL,
 `sum_nettotal` decimal(25,6) DEFAULT NULL,
 `sum_tax1` decimal(25,6) DEFAULT NULL,
 `sum_tax4` decimal(25,6) DEFAULT NULL,
 `sum_taxtotal` decimal(25,6) DEFAULT NULL,
 `sum_taxtotalretention` decimal(25,6) DEFAULT NULL

I did create a few custom fields but they reside in vtiger_invoice_cf and should not interfere?

[joebordes]

it is not the fields that are causing the problem it is filling them with values
The changeset creates the fields and then launches a process to fill them in with the correct values
it is this second part that is failing. We need to find out why.
I made some modifications to the changeset to make it more robust on large installs. It would be a good idea that you update, but that will not solve the problem, I need you to activate the debug log and capture the SQL that is causing the error. See if that gives us some clue as to what is going on.

[partnerwerk]

I activated debugging according to http://corebos.org/documentation/doku.php?id=en:devel:debuging

   Turn on debuging for ADODB instanced in include/database/PearDatabase.php (at end of file)

   if(empty($adb)) {
    $adb = new PearDatabase();
    $adb->connect();
    // ADD THIS LINE
    $adb->setDebug(true);
   }

I then run the financial fields update and the following seems to be the important output:

Code:

Query: select tax4 from vtiger_inventoryproductrel where id = '4453' and productid = '1240' failed. Unknown column 'tax4' in 'field list'
1054: Unknown column 'tax4' in 'field list'

              ADOConnection._Execute(select tax4 from vtiger_inventoryproductrel where id = '4453' and productid = '1240') % line 1204, file: adodb.inc.php
           ADOConnection.Execute(select tax4 from vtiger_inventoryproductrel where id = '4453' and productid = '1240', Array[1]) % line  495, file: PearDatabase.php
        PearDatabase.pquery(select tax4 from vtiger_inventoryproductrel where id = ? and productid = ?, Array[2]) % line  682, file: InventoryUtils.php
     getInventoryProductTaxValue(4453, 1240, tax4) % line  164, file: setVAT.php
  setVAT(Object:VTWorkflowEntity) % line  223, file: addFinancialFields.php

I hope you can tell me what to do with it...

[joebordes]

I made a mistake with the changeset. I just uploaded the modifications, now addFinancialFields should be working better.

Now, your problem seems to be that you have created a new tax, this tax was named internally tax4. It seems to have been added correctly to the vtiger_inventorytaxinfo table but it was no added to the vtiger_inventoryproductrel table.

I just tried to see if this was working correctly and I had no problem so I really don't know how that could have happened in your system

I would say that you simply add the tax4 column to vtiger_inventoryproductrel but this is incorrect and you have not been able to save that tax for any invoice, so walk with care....

[partnerwerk]

Updating with the new addFinancialFields it runs a bit further but not completely:

Code:

Creating Field sum_tax1 (MwSt.) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_tax4 (erm. USt.) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_taxtotal (Total Tax) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_taxtotalretention (Total Tax Retention) on Invoice ... **ERROR**
A field with that name or label already exists.
Creating Field sum_tax1 ... DONE
Module language mapping for MwSt. ... CHECK
Creating Field sum_tax4 ... DONE
Module language mapping for erm. USt. ... CHECK
Creating Field sum_taxtotal ... DONE
Module language mapping for Total Tax ... CHECK
Creating Field sum_taxtotalretention ... DONE
Module language mapping for Total Tax Retention ... CHECK
Creating Field sum_tax1 ... DONE
Module language mapping for MwSt. ... CHECK
Creating Field sum_tax4 ... DONE
Module language mapping for erm. USt. ... CHECK
Creating Field sum_taxtotal ... DONE
Module language mapping for Total Tax ... CHECK
Creating Field sum_taxtotalretention ... DONE
Module language mapping for Total Tax Retention ... CHECK
Creating Field sum_tax1 ... DONE
Module language mapping for MwSt. ... CHECK
Creating Field sum_tax4 ... DONE
Module language mapping for erm. USt. ... CHECK
Creating Field sum_taxtotal ... DONE
Module language mapping for Total Tax ... CHECK
Creating Field sum_taxtotalretention ... DONE
Module language mapping for Total Tax Retention ... CHECK
result is not an object

and the following table

Quote:ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 1 [1] => 886 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 2 [1] => 887 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 3 [1] => 889 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 4 [1] => 888 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 5 [1] => 890 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 6 [1] => 895 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 7 [1] => 928 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 8 [1] => 926 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 9 [1] => 929 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 10 [1] => 891 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 11 [1] => 892 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 12 [1] => 894 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 13 [1] => 893 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 14 [1] => 927 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 1 [1] => 896 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 2 [1] => 897 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 3 [1] => 899 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 4 [1] => 898 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 5 [1] => 900 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 6 [1] => 905 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 7 [1] => 901 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 8 [1] => 902 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 9 [1] => 904 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 10 [1] => 903 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 1 [1] => 906 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 2 [1] => 907 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 3 [1] => 909 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 4 [1] => 908 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 5 [1] => 910 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 6 [1] => 915 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 7 [1] => 911 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 8 [1] => 912 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 9 [1] => 914 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 10 [1] => 913 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 1 [1] => 916 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 2 [1] => 917 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 3 [1] => 919 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 4 [1] => 918 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 5 [1] => 920 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 6 [1] => 925 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 7 [1] => 921 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 8 [1] => 922 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 9 [1] => 924 )
ADORecordSet_empty S UPDATE vtiger_field SET sequence = ? WHERE fieldid=?  Array ( [0] => 10 [1] => 923 )
Updating Financial fields for Invoice

Total Number of updates executed :
1
Correct Updates :
0
Failed Updates :
1

[joebordes]

the fixes in the script are not to fix your issue, it is another issue they are attending. Your problem is that there is a missing column in vtiger_inventoryproductrel. My comment above:

I would say that you simply add the tax4 column to vtiger_inventoryproductrel but this is incorrect and you have not been able to save that tax for any invoice, so walk with care....

[partnerwerk]

Dear @joebordes. Thank you for your dedicated support. I appreciate it a lot!

I know these changes where not done for me only. What I wanted to tell, is, that I applied both: your rewritten script and adding the field tax4 in vtiger_inventoryproductrel

Then I ran the update addFinancialFields with encountering the above errors.
So I did this again with mysql debug on and it finished with the following output. Maybe you can tell me what this means...

Quote:Creating Field sum_taxtotalretention (Total Tax Retention) on PurchaseOrder ... **ERROR**
A field with that name or label already exists.
Id specified is incorrect

a lot of mysqli output finishing wiht this

Quote:(mysqli): select tabid from vtiger_tab where name='SalesOrder'   (mysqli): select distinct block,vtiger_field.tabid,name,blocklabel from vtiger_field inner join vtiger_blocks on vtiger_blocks.blockid=vtiger_field.block inner join vtiger_tab on vtiger_tab.tabid=vtiger_field.tabid where displaytype != 3 and vtiger_tab.name in ('Invoice') and vtiger_field.presence in (0,2) order by block   (mysqli): SELECT vtiger_field.*, '0' as readonly, vtiger_blocks.sequence as blkseq FROM vtiger_field LEFT JOIN vtiger_blocks ON vtiger_field.block=vtiger_blocks.blockid WHERE vtiger_field.fieldid IN (select min(vtiger_field.fieldid) from vtiger_field where vtiger_field.tabid=23 GROUP BY vtiger_field.columnname) and block in ('67','68','69','72','150') and displaytype in (1,2,3,4) ORDER BY vtiger_blocks.sequence ASC, vtiger_field.sequence ASC   (mysqli): select sequence from vtiger_blocks where blockid=67   (mysqli): select sequence from vtiger_blocks where blockid=69   (mysqli): select sequence from vtiger_blocks where blockid=68   (mysqli): select sequence from vtiger_blocks where blockid=72   (mysqli): select sequence from vtiger_blocks where blockid=150  

Id specified is incorrect

[joebordes]

update the script again, I made some additional changes as I debugged it to get it working with installs with 1M inventory records