Update problem with financial fields

[partnerwerk]

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

It seems you work day and night...

I applied the changes but that does not change anything. When I run the update script it just stopps and the update stays marked as failed although it looks like everything has been done.

What do you think: Should I just change the status to done in the DB ?

[joebordes]

Keep on executing it
You can follow the progress in the cb_settings table
It will finish
On the install with almost 500000 inventory records I had to launch it 5/6 times

[partnerwerk]

Sorry to say: No progress in cb_settings. No finish of the update process after several launches of the update.

I guess its the field addFFcrmidDone ? It stays on the same value after each execution of the update file.

There are only about 15000 entries in the crmentity table. But the highest crmid is 279644 at the time. In vtiger_inventoryproductrel its even less.

[joebordes]

See if you have some other SQL error besides the missing tax4 field.

[partnerwerk]

No missing fields. These are all the errors mysqli puts out when running the financial fields update.

(mysqli): SHOW COLUMNS FROM `vtiger_invoice`   Creating Field sum_tax1 (MwSt.) on Invoice ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_invoice`   Creating Field sum_tax4 (erm. USt.) on Invoice ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_invoice`   Creating Field sum_taxtotal (Total Tax) on Invoice ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_invoice`   Creating Field sum_taxtotalretention (Total Tax Retention) on Invoice ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_salesorder`   Creating Field sum_tax1 (MwSt.) on SalesOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_salesorder`   Creating Field sum_tax4 (erm. USt.) on SalesOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_salesorder`   Creating Field sum_taxtotal (Total Tax) on SalesOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_salesorder`   Creating Field sum_taxtotalretention (Total Tax Retention) on SalesOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_quotes`   Creating Field sum_tax1 (MwSt.) on Quotes ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_quotes`   Creating Field sum_tax4 (erm. USt.) on Quotes ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_quotes`   Creating Field sum_taxtotal (Total Tax) on Quotes ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_quotes`   Creating Field sum_taxtotalretention (Total Tax Retention) on Quotes ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_purchaseorder`   Creating Field sum_tax1 (MwSt.) on PurchaseOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_purchaseorder`   Creating Field sum_tax4 (erm. USt.) on PurchaseOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_purchaseorder`   Creating Field sum_taxtotal (Total Tax) on PurchaseOrder ... **ERROR**
A field with that name or label already exists.
(mysqli): SHOW COLUMNS FROM `vtiger_purchaseorder`   Creating Field sum_taxtotalretention (Total Tax Retention) on PurchaseOrder ... **ERROR**
A field with that name or label already exists.

I apologize for being so ”difficult”

[joebordes]

Those are not errors, it is just saying that the fields are already there. Once it informs it continues to update all the records.

[partnerwerk]

No other errors have been reported in debug mode.

[partnerwerk]

Maybe I found something that can help to find a solution:

I realized that when calling the ”more information” tab on a contacts page and then click on ”Invoices” that no invoice is shown. I turned on DB debugging and found this statement which seems to be wrong:

SELECT vtiger_crmentity.*, vtiger_invoice.*, CASE WHEN (vtiger_users.user_name NOT LIKE '') THEN CONCAT(vtiger_users.first_name,' ',vtiger_users.last_name) ELSE vtiger_groups.groupname END AS user_name, vtiger_invoicecf.* FROM vtiger_invoice INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_invoice.invoiceid LEFT JOIN vtiger_invoicecf ON vtiger_invoicecf.invoiceid = vtiger_invoice.invoiceid INNER JOIN vtiger_contactdetails ON (vtiger_contactdetails.contactid = vtiger_invoicecf.cf_832) LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted = 0 AND vtiger_contactdetails.contactid = 275698 ORDER BY crmid ASC LIMIT 0, 20

Unfortuately my skills end here. Do you know how I could proceed from here? I guess this is what blocks the financial fields update script to run properly. It should update all the financial fields in existing invoices if I understand it right? When I am opening an invoice and save it the fields are populated correctly. But the script makes no changes on existing invoices.

[joebordes]

I don't see anything wrong with that SQL. At most, it is odd that you added a related field to contacts on the invoice when there was already one there for that. But, unless you have deleted the custom field (cf_832) and the system is still looking for it, that SQL should work.

have you tried executing it directly in MySQL, to see if it gives you some error?

[partnerwerk]

Yes, I tested it in MySQL. It does not return an error but it also doesn't return results where there definitely are records for this contact.

But I found a little more:

My invoices contain a custom field (cf_832) which is a relation to a second person. This is sometimes needed, sometimes not. When I am in the contacts module und looking for invoices related to this contact it should show the records related to their contactid. Strangely enough the system seems to use the custom field as identifier to retrieve the related invoices. As a result
1. no invoices get displayed when there is no second contact related to the invoice, and
2. when looking at the secondary contact those invoices are displayed that should be displayed for the primary one.

Do you have any hint how this could be switched?

PS: The invoices are stored with the correct contactid.