Major incorrect data / cross table problem

[mdorsh]

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.

[digitalpoint]

Does the customer's billing cycle items look okay (on their billing info screen)?

Also, if you go to Maintenance & Preferences -> Clean Up -> Null Line Items does anything come up?

[mdorsh]

Does the customer's billing cycle items look okay (on their billing info screen)?

Also, if you go to Maintenance & Preferences -> Clean Up -> Null Line Items does anything come up?

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

[digitalpoint]

I would go ahead and delete all null line items (as they are not attached to any customer or invoice). My guess is that is where the funky stuff is coming from. Did you recently do any mass deletion of customers or anything?

[mdorsh]

I would go ahead and delete all null line items (as they are not attached to any customer or invoice). My guess is that is where the funky stuff is coming from. Did you recently do any mass deletion of customers or anything?

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.

[digitalpoint]

Well that's not going to fix the invoices that were already created, that will just take care of it going forward.

As far as how something like that could happen, I'm not sure to be honest, but if the call activities are still in the normal place, the only thing I can think of is maybe someone accidently imported invoices, but imported from the Activity table directly. Does anyone have access to Maintenance & Preferences -> Import ?

[mdorsh]

Well that's not going to fix the invoices that were already created, that will just take care of it going forward.

As far as how something like that could happen, I'm not sure to be honest, but if the call activities are still in the normal place, the only thing I can think of is maybe someone accidently imported invoices, but imported from the Activity table directly. Does anyone have access to Maintenance & Preferences -> Import ?

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?

[digitalpoint]

Well if you never have a need to import, I would take it away from all employees (even administrators). No need for it if you never use it.

One thing I can think of would be to go and delete all the invoice created on a certain date (the day it happened), then regenerate them now that there are no null line items in the system.

[mdorsh]

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

[mdorsh]

Well if you never have a need to import, I would take it away from all employees (even administrators). No need for it if you never use it.

One thing I can think of would be to go and delete all the invoice created on a certain date (the day it happened), then regenerate them now that there are no null line items in the system.

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?

[digitalpoint]

It would not affect if it's the first invoice or not, but if a new invoice is created with the same ID, the null items could get assigned to that invoice.

[mdorsh]

It would not affect if it's the first invoice or not, but if a new invoice is created with the same ID, the null items could get assigned to that invoice.

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.

[mdorsh]

Well if you never have a need to import, I would take it away from all employees (even administrators). No need for it if you never use it.

One thing I can think of would be to go and delete all the invoice created on a certain date (the day it happened), then regenerate them now that there are no null line items in the system.

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?

[digitalpoint]

Well, there isn't going to be a way to automatically fix it if there is no way to differentiate (in an automated way/query) between them unfortunately...

[mdorsh]

Well, there isn't going to be a way to automatically fix it if there is no way to differentiate (in an automated way/query) between them unfortunately...

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

[digitalpoint]

If indexes were rebuilt, it would rebuild the balance due index.

[mdorsh]

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

[mdorsh]

If indexes were rebuilt, it would rebuild the balance due index.

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

[digitalpoint]

You could bulk delete invoices, but make sure you delete their corresponding payments of the same amount. Otherwise the system will think they overpaid.

[mdorsh]

You could bulk delete invoices, but make sure you delete their corresponding payments of the same amount. Otherwise the system will think they overpaid.

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

[digitalpoint]

Payments aren't tied to a specific invoice, so you are just going to need to make sure you delete the same dollar amount of payments as the invoices you delete. As far as deleting them goes, there is not an automated way to do it, so you are going to have to write a script to do it.

[mdorsh]

ohhh kaayyy. so let me get this straight.

1) You have no idea how your database software could have placed info from the call activity into invoices.

2) You have no idea why your database software would suddenly choose to bring those balances forward.

3) You have no idea how we can remedy this problem.

4) You don't much seem to care.

Let me know if I am missing something.

[digitalpoint]

The only way it could have brought something in from another table would be to import it.

Rebuilding the indexes would rebuild the balance due field.

The null line items clean up will take care of it going forward, but going back historically, you would need to be able to key off something unique within those line items to be able to fix it. If there is nothing unique about those line items, it would be impossible.

[mdorsh]

The only way it could have brought something in from another table would be to import it.

Rebuilding the indexes would rebuild the balance due field.

The null line items clean up will take care of it going forward, but going back historically, you would need to be able to key off something unique within those line items to be able to fix it. If there is nothing unique about those line items, it would be impossible.

-----"The only way it could have brought something in from another table would be to import it."

As stated in a previous post, we seem to have tracked the creation of these bogus invoices to our upgrade to 3.3.2 which did, I believe, do some imports of ._usr files converting them to the fm5 format.
However the upgrade was completed with no errors reported. The upgrade took place earlier this year with accounts and invoices going back to early 1997. If an incorrect file was imported during the upgrade then why/how could this happen? Also why would this only cause the 1 or 2 months span of invoices on only some accounts to corrupt rather than all of them? It is obvious that, at least at some point, it imported the correct file, as the vast majority of invoices were imported correctly.

-----"Rebuilding the indexes would rebuild the balance due field."

BUT we rebuild indexes daily and these invoices have been there since feb/march so why all of a sudden would that days reindex bring those forward after so many months?

[digitalpoint]

I'm not sure to be honest, do you have a backup of your old data, and if so are you able to replicate the problem with it when doing the import?

[mdorsh]

I'm not sure to be honest, do you have a backup of your old data, and if so are you able to replicate the problem with it when doing the import?

I have found the backup from right before the upgrade and will try this as time allows today.

This brings up a question. If after this experiment the invoices are not corrupted, is there a way we can overwrite the invoices from a specific time period. What I mean is if for example I export all the 1997 invoices (can I even do this?) can I then import these into our current database overwriting them all?

Martin

[digitalpoint]

It could be done... it would require a little scripting and getting at the data via XML to alter the tables directly.