SO_SalesOrderHistoryDetail has records with a null SalesOrderNo

I've noticed that we have just over 14,000 records in the SO_SalesOrderHistoryDetail table where the SalesOrderNo field is null.  This is causing some issues with trying to do a copy into a MySQL database due to the Primary Key not accepting null values.  There are no records in the SO_SalesOrderHistoryHeader table that have a null SalesOrderNo so I have no idea where these came from.

I need to find a way to get rid of them.  I've worked with Sage support to try and figure it out and we've tried a variety of things from rebuilding the key files to some command line utility they ran that I've never seen before and we can't figure it out.  We can easily see the records through DFDM, but manually deleting 14,000 of them one at a time is not an option.

Is there any other method of finding and removing these all at once?  I'm open to anything.  Using Business Objects, trying Visual Integrator, using some other method we can't seem to think of...

Has anyone ran into this before, or have any ideas on how to fix it?

This is for Sage 100 Advanced 2020.

  • 0

    One thing I would try first in a test company might be to export all of the non null records for all fields into a text file, and then rename the file and import only the non-null records.  Be aware that if you have quotes, single quotes and commas in your descriptions this could be a problem on the import.

    Depending on how you are inserting into MySQL, you could also filter out those records where the SalesOrderNo is null, if it is in fact null.

  • 0 in reply to mhainesalt

    You say you rebuilt key files, but just checking to make sure you rebuilt sorts as well? I'm wondering if the command line procedure was running UFAR - the old rebuild utility. Depending on what Product Update you are on, if you're not on the latest you might try applying the latest and letting it scan your company data files.

  • 0 in reply to mhainesalt

    I could easily fix it by stripping those entries out of a text file, but that is not how the import is being done.  It is also not a one off, it runs multiple times a day so having to interact with it every time would not be feasible.  I just need a way to clean them out of Sage completely since their broken records without a header anyways.

  • 0 in reply to rclowe

    Yes, I did rebuild the sort files as well with no help there.  I believe UFAR is the one Support used as that sounds familiar.  I'm on the latest update for Sage 100 Advanced 2020, but the only reason I found the issue is when I migrated our data over to the latest version of Sage Advanced 2023 for our testing before upgrading.  

  • 0

    As far as I know BOI can only edit SO lines by starting with the header.  Same with VI... you can only deal with lines in relation to an open SO header.

    About the only idea I have would be to manually insert a NULL header record using DFDM, then see if Sage will let you delete that (hopefully taking along the details).

    Otherwise you might need to hire a master developer / providex programmer to do it for you.

  • 0

    I would export the table to Microsoft Access, make a copy of the file, then re-init the file in Sage 100, go into the Access table and delete all the records with no sales order number and then import the table into Sage 100 using ODBC to read the table. This gets around the issue with quotes and commas in descriptions.

  • 0 in reply to BigLouie

    I thought about doing that as my first option, but Support felt that without a header record to go with it, and the fact that it is a history table, that VI would not work in this scenario.  If you believe differently, I'm happy to give it a go.

  • 0 in reply to justinp

    I've never done it, but SO history files are listed as an option in VI.

    I'd be super cautious though... because you'd be importing true history, and history lines mirrored for open SO too.  Be sure to test thoroughly in a test company code before trying anything in your Live company code.

  • 0 in reply to justinp

    I would create a copy company and try it in the copy and if it works do it in the live company. No matter what method you settle on you should do it in a test company first.

  • 0 in reply to justinp

    I am actually suggesting that you correct the Sage table in a test company by exporting all the records, renaming and manually reinitializing the table again in the test company and then reimporting it back into Sage without the null records.   If this fixes this in a test environment and you have a good backup if it doesn't this could be a work around in a non SQL environment.