x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 13 replies
  • Subscribers 90 subscribers
  • Views 965 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
964
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
justinp

SO_SalesOrderHistoryDetail has records with a null SalesOrderNo

Posted By

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.

Parents
  • 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.

Reply
  • 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.

Children
  • 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.  

*Community Hub is the new name for Sage City