Sage 100

Have a customer who back in 2008, had an SO sales journal post to the year 2020. The invoice dates were correctly dated 2008 but the transaction date was 2020. Made a journal entry and everything was fine until they upgraded to 4.4. Now when they run statements, all of the invoices paid by credit card on that register are appearing as unpaid since they have a 2020 payment date. I'm assuming for statements, it is looking at the invoice date in AR_OpenInvoice and then transactions through the statement date (which there aren't any). What's the best way to correct this - I see a couple of options: Modify the statement form (which is likely to cause problems when the year 2020 does roll around and I'd say that's a long ways off but...); or modify the transaction dates either through DFDM or a VI job (would that create another transaction or overwrite the existing one?). Suggestions????

How many invoices in question? I think you would be able to VI the change without a duplicate record. Not sure about the transaction history file though. And don't forget to look at the AR invoice history header table as that one is probably wrong also.

A lot! I'd say at least 150 or more.

With that many, I'd use VI + Randy's recalc utility to correct summary files.

The primary key of the AR_TransactionPaymentHistory table includes the transaction date, so importing into that table would create a duplicate record. Could you use a BOI script or program to modify the transaction dates? @DawnAnastasi or @DanBurleson know a lot about that sort of thing, and could possibly weigh in on this.

The date is part of the key in the transaction history file, so that will have to be DFDM. This is one of those goofs that is SO much easier to fix in SQL.

You could use BOI to delete out the old records, yes. But you would still need to import in replacement records.

Wonder if DSD's Global Editor can be used for this purpose.

Another thought is to export the entire transaction file, change the 150 lines, initialize the transaction file then reimport.........

@jeff, we're talking over 100k records in the transaction file! @Mark, it doesn't look like the DSD Global Editor allows changes to date fields (hadn't heard of this enhancement; could be useful!).

Ahh, an after hours project to be charged at a premium. At the server, it should go fairly quickly.......

I think DFDM is the only option on the Payment Transaction file, that being said you will be promted when you change the date to delete the old record, very tedious.

I absolutely hate fixing errors like that, but the mantra ""billable hours, billable hours"" sometimes helps.

Yeah, DFDM it is. At least I have until they send statements out again.. Wait...that'll be next week!!!

Deleting out the bad records using BOI should take under an hour. I would do that versus using DFDM if there are that many records to delete. Then you just need to VI in the good records.

I'm done. It took me about an hour to correct the necessary records. Thanks though!