Rebuild Key Files - Strange Issue?

Greetings,

We're in the process of upgrading our ancient Sage 4.50 to Sage 2018. Ran into a strange issue with regards to rebuilding the key files prior to migration.

All the files were able to be rebuilt and optimized without any issue except for one file. This file fails the verification test, however it will rebuild / optimize without any errors.

After the file has been rebuilt / optimized it still won't pass the verification test though.

See below for output from the verification test.

IM_ItemCustomerHistoryByPeriod.M4T
     The read test failed.  Error #11: Record not found or Duplicate key on write

Here is the output if we uncheck the verification test.

IM_ItemCustomerHistoryByPeriod.M4T
     Record count indicates all records were recovered.
     Keys from the old file are being compared to the new file.
     No discrepancies have been found.

I went ahead with the migration, and then converted the data from 4.50 to version 6.00. When I perform a verification test on this file it still fails the read test. We're not seeing any errors generated in Sage 100 either.

I've talked to Sage support and they don't have any idea about this. One individual even suggested we ignore the issue. It was suggested that I post on the forum here and see if anyone else had a clue.

Is this something we need to be concerned about?

Thanks for any help you can offer.

  • 0

    I would export the IM_ItemCustomerHistoryByPeriod.M4T file to Access and then import the Customer Masterfile, do an unmatched query to see if there are any records in the history file that do not have a customer in AR_Customer. I would also look to see if there is a blank record.

  • 0 in reply to BigLouie

    Big Louie,

    Thank you for the reply. I'm not entirely sure how to do this, but I'll look into it.

    If I do find a records in the history file that do not have a customer in AR_Customer what do I do then? Same with the blank record?

    Thank you kindly.

  • 0 in reply to BigLouie

    Big Louie,

    I was able to do as you suggested with Access and I found two customer numbers in IM_ItemCustomerHistoryByPeriod.M4T with some records that have no corresponding customer number in AR_Customer.

    The data looks pretty old (from 2006). What do you recommend that we do?

    Thanks so much.

  • 0 in reply to MOB

    Send me a check with a lot of zeros? Joy

    you can either add the customer to the AR_Customer file or find and delete the records in the IM_ItemCustomerHistoryByPeriod.M4T file using DFDM. Do it in a test company first.

  • 0 in reply to BigLouie

    Big Louie,

    You're too funny!

    I'm not very knowledgeable about the DFDM utility, and sounds a bit intimidating, but I'm willing to learn if I have to.

    I did just try adding the customers into AR in the test company, however the file still does not pass the verification test. How do I search for blank records? Is there something else missing?

    Thanks again for your help!

  • 0

    I have had luck finding the bad record by creating a crystal report with all the fields.  Preview or export to excel and see what record it fails on.  Use DFDM and look at the records at and after the failure to see if you can see an issue.  You can in a test copy delete records you might think is the issue and then retry the verification test then you narrow down for sure if the record is the issue.

    I've had data that looked right on the screen but had hidden characters i had to delete to fix it.

  • 0 in reply to Bvulliamy

    I messed around with DFDM last night so I understand a bit better how it works. I'll try what you suggested and see if I can locate the problem and report back.

    Thank you so much.

  • 0 in reply to Bvulliamy

    Hi, so I was able to dump all the data into a crystal report, and even export the data to excel. There were no errors during this process. Is there something else I should be looking for in the data? Perhaps something I could find within the excel file?

    Thanks again!

  • 0 in reply to MOB

    You don't understand. The error message:

    IM_ItemCustomerHistoryByPeriod.M4T
         The read test failed.  Error #11: Record not found

    Is telling you a record is not found. When you did the unmatched query you found records that were not in the customer master file. In a test company I would remove those records and try to rebuild the key files and see what happens.

  • 0 in reply to BigLouie

    I removed the records from IM_ItemCustomerHistoryByPeriod.M4T using the DFDM.

    I rebuilt just IM_ItemCustomerHistoryByPeriod.M4T and it still fails the read test after rebuild. No errors occurred during rebuild.

    Do I have to rebuild all the files, or just the IM_ItemCustomerHistoryByPeriod.M4T file?

    Thanks!