Major incorrect data / cross table problem

Using Optigold 3.3.2 & Filemaker Pro Unlimited 6 v.4a

We are unable to determine when this issue started, other than our daily backups from the last week all have the same problem and we know it wasn't there at the beginning of the month.
We are about to invoice and have noticed some accounts with much higher amounts than normal. Upon further investigation these appear to be caused by invoices in those accounts that are supposed to be "Call Activity" data. What I mean by this is that the billing item is a timestamp, the billing description is a date and the amount is the actual tech note.

This is obviously a huge problem resulting in accounts being invoiced, in some cases, millions of dollars incorrectly. Please advise.

Martin Dorschler
db Technology Inc.

 

Billing cycle items are fine, and yes we do have many entries in the 'Null Line Items' some look obviously incorrect (credits for millions and debits for numbers that I can't express without using exponents) some look to be correct amounts (normal dialup rate for example) and some are for $0

 

We have removed them (now showing no null line items) we still see incorrect invoices on accounts however (impossible at this stage to tell how many).

No recent deletions of customers at all. In fact nothing out of the ordinary has been done for months.

All the erroneous invoices that we can find (so far) are already closed invoices, and all are showing as the first invoices on their respective account. On both cancelled and current accounts.
Even though the invoices are closed they are still adding to the amount due. And all are showing a 333,500% markup in line item detail.

The Call Activity entries are not from the accounts that they are showing up as invoices in. They are still showing up as calls though.

 

Only the administrator user has this privilege and it is only used by one person and only for changing other users prefs. We never have need to import invoices for any reason.

We are in a major bind here and even if we dedicate manpower to going through each account individually looking for incorrect invoices and correct them (I don't even know if this will be possible yet), if we don't know how this could happen in the first place how do we know it's not going to occur again?.
We have been using optigold for 5+ years and t is scary that something like this could happen out of the blue. What can we do?

 

in fact just checked and even the administrator account is not set with privileges to do that

 

These invoices are not from a specific date. As stated in my earlier post they are already closed invoices from a variety of accounts, some closed accounts some open accounts, the only thing they have in common is that they are always the first invoice on the account (subsequent invoices on the accounts appear to be normal). How would having null line items cause another tables data to get mixed up with it anyway?

 

I still don't understand how a) null items can just 'happen' in the first place and b) how they could mix up data from a totally separate table. But regardless of that we simply have to get a solution to this.

 

Just re-read this and worked out what you meant (on first reading I thought you meant to delete all invoices that shared the date with the bogus invoices)

We cannot delete invoices from the day this started happening because, as mentioned in my original post, we cannot tell what day it started, only that it has been since the beginning of the month.

Anything else we can try?

 

Managed to dig up some more info by digging round in the occasional old backup that we sometimes archive.

It looks as though these invoices may have been corrupt ever since we upgraded to 3.3.2 back in February of this year, and only affects invoices between April 30 - May 30 1997 (I can only begin to imagine why the upgrade would have pulled only some entries from the wrong table). Stranger still than us not noticing these archaic invoices till now is that only during this month has it started carrying the crazy invoices forward.

We have located a customer who in yesterdays backup had a normal balance (even though at the beginning of his invoice history he has a closed corrupt invoice, for a totally off whack amount.)
But in todays backup the balance from that invoice shows due even though the invoice still shows as closed.
This seams to mean that if we can just stop the corrupt invoices from becoming due balances we can probably with time clear out the old corrupt ones. Any ideas on what might be causing optigold to suddenly start bringing these balances forward?

Thanks
Martin

 

Ok we have figured out that (as all our 1997 invoices and corresponding payments are legacy imports from an old access database and we only need to keep records for an audit going back 5 years) That if we 'simply' delete all 1997 invoices and payments we will (hopefully) fix this whole issue.

That's where another problem comes in which maybe you can help us with. How can we delete them all as a bulk job? We can only delete open invoices and all of these are closed and how can we identify only payments for those invoices? (eg. an invoice on Dec 31st 1997 may have had it's payment posted on Jan 1st 1998).

Or can you think of a different solution?

Thanks, Martin

 

But we rebuild indexes every day, why would this all of a sudden carry forward balances from closed invoices?.

!!!! Also please see my possible solution above !!!!

Thanks
martin

 

Wow, it's almost as if you're not reading my questions at all.