Bug when updating item type and refreshing invoices

[tranx]

This is a combination of various issues and consequence are major issue as it seems updates have cause account to become wrong - more specifically payments to invoice amounts seemed to have been changed to total payment amount when updating invoices. This causes the payments that were paying multiple invoices to be reported multiple time at their full value instead of only the part contributing to the invoice in the accounts.

Here is the context and the full story:

We use big capital to manage accounts for a small language school. We have 100 of families paying regularly the school.

PART 1: Bulk Changing "Income Account" on invoice items We had imported transaction using API and put quite a lot of transactions in "other income" when they were not related to main trade - however when completing tax return there is a limit to 1000 gbp in other income in "UK" so we had to reaffect some of the invoice to a new income category part of main income. Most of these invoices where using a specific "Item" type - so we thought updating the "Income account" used in the item would be enough to update how all invoices that were using this item category may be enough. In reality just changing the item didn't changed the way existing invoices were categorised, however, if we did an update on the invoice to force an update they eventually did update into the right category. We have thousands of small invoices - and we have managed to do a somekind of refresh alll this using the API. As a first point, it may be questioned whether, this should be a feature of the software to allow to change the category of many invoices at once. Defining the test cases and the characteristics of this feature certainly has its own complexity especially if you have locked account in previous years... But I would see this first part as feature request, and not as a bug which is the main story - I want to discuss about.

PART 2: "BROKEN RECONCILIATION" Before running the update on all the invoices we had done a reconciliation against the bank statement and our accounts in bigcapital were following the bank statements (we have a small application that looks at bigcapital database and the CSV of our bank to verify they match - and display the difference on a date by date basis - this is a very useful feature - but requires to be able to read the bank dates running balances from csv).

After, running this change, we could notice that this reconciliation was all over the place. The only things we had done when updating the invoice was changing an "income type" this should not have affected the bank account. We did zoom one of the date where the reconciliation was now failing - and here is what we can see now: What's important to see here is that repeating line mentioning PAY-14620 with the same amount 150 being repeated - and the fact that this amount is debited multiple times in the balance.

Now, if we look at the payment or the invoice we see that actually the payment is composed of various individual legs to payments to invoices (the fact it is actually the same invoice in that example is an artifact caused by the fact we imported data from our legacy system). What's important is that there are actually 8 payments with value 20 and 10 GBP - and we should see these payments with value 10 or 20 when looking at the bank account transaction and not the full 150 - which is the total amount of the payment and of the invoice in this case. What is concerning is that we only "refreshed" the invoice by calling update API on them and we haven't touched the "payment".

Despite, the issue, as shown by the statement, the correct value still seems to be known.

It would be great if we could get a rapid fix for this as this impacts our ability to continue to update our accounts as the presentation of the accounts is now impacted by many instances of this bug.

[linear[bot]]

BIG-197 Bug when updating item type and refreshing invoices

[tranx]

PS: Probably much less important - but looking at the screenshot I am confused by the date indicated on the line matching payments and invoices - it seems to indicate today's date - but it does not correspond to invoice date / nor payment date .

[abouolia]

Thanks for long reporting, I've read all what you've written, there're too many points here.

• Changing "Income Account" on invoice items: The income account should be stored and associated to estimate, invoice and credit note transaction and takes the default income account from the selected item.

Even if the income account being changed from the item won't affect the old transactions (like invoices) that would update the new transactions only to update old invoices you have update "Income Account" from these invoices in bulk.

Of course, adding bulk endpoint to update invoices (for instance) in bulk would help a lot to complement this feature.

• Yes, the payment date on the payment received drawer is not correct even though retrieved from the server correct but the problem in frontend formatting.

I will follow up with other issues.

[tranx]

Sorry if this was confusing - I wanted to provide the context.

The main issue is not described in your the summary reply - the main issue can be summarised as : """payment amount is changed to an invalid value in bank transactions when we update invoice with multiple payments .""" This breaks the account reconciliation against bank data.

[abouolia]

unfortunately, Tried many times but I couldn't duplicate the issue, could you please record a quick video about it or give more details?

[tranx]

Ok the duplicate amounts seem to appear when there were multiple payment amount between a payment receives and an invoice. This probably don't happen to most users. I have managed to manually fix this.

The query that allowed to identify the invoice that had multiple payments generated was :

SELECT pri.*, si.INVOICE_NO, pre.PAYMENT_RECEIVE_NO FROM (SELECT PAYMENT_RECEIVE_ID, INVOICE_ID, COUNT(*) AS C  FROM lpbcten1efi1lth2k8nq.PAYMENT_RECEIVES_ENTRIES  GROUP BY PAYMENT_RECEIVE_ID, INVOICE_ID   HAVING C>1) AS pri
   LEFT JOIN lpbcten1efi1lth2k8nq.SALES_INVOICES si ON (pri.INVOICE_ID=si.ID)
   LEFT JOIN lpbcten1efi1lth2k8nq.PAYMENT_RECEIVES pre  ON (pri.PAYMENT_RECEIVE_ID=pre.ID)

[abouolia]

@tranx If you got specific scenario to duplicate the issue from my side please let me know, I will keep the issue open for a while, in terms of the other issues will be fixed in the next patch release.