Sage 100

Have a client who hit 1,000,000 for the index in the WO2 file. Sage says the only thing to do is to export WO1/WO2, reinit, and import back in. Has anyone run into this? Is that truly the only way to handle it? And how to prevent in the future, because this client runs through a crap load of orders. Does Analyze and Relink free up unused indices and need to be run once a week to reclaim purged WO indices?

Analyze and Relink doesn't do the trick! If there is a large gap of unused index numbers, you can change the ""Next"" number to be the start of that gap. However, those records with the 7-digit indices can't be read by the system, so those will need to be removed and redone.

We couldn't effectively find a great way to re-import all of the needed records.

Blech. Thanks, @MichelleTaylor1 . :-( I knew it wouldn't help on the current ones, but I'm assuming it needs to be done regularly going forward to make Sage see the free indices available?

Yes, you are correct :)

I have a client that hits the million records mark about every 2 months if we don't purge. I've found that purge and then rebuilding sort files keeps the problem from happening. We setup an alert to email us if when WO2 reaches 850,000 records. We then purge and rebuild sort files. to answer the first question, when it hits a million records the only way I have found to fix it is to export and reimport

And the day just gets better and better. They can't put WO in the framework fast enough. And speaking of that - who wants to take bets that it is not EVEN going to hit the 2019 release as the schedule says... Right. Sucker's bet. Why did I even ask?

@DavidOverholt , While i've been working on resolving this, i thought an alert would be a great idea too, it has been 2 years since they went live so didn't think about it prior to the errors popping up. They have a bunch of ACS stuff and although getting the data out with crystal works fine, the VI import appears to be placing the data out of sequence resulting in corrupted header records. Left DFDM screenshot is before reinitializing WO1, right is after reinitializing and importing data back in.

When I last did it, I used VI to Export the files and then used VI to pull them back in. When I created the export and import jobs I just checked the box to insert all fields during setup. I checked the layout of both the export and the import to make sure all the columns were in the same order. It's been about a year since I last did this, but I think they were both in the same order without having to make any changes. the fobs took a long time to run though.

i tried VI export first, it encountered an error so had to resort to crystal to export, for the import, i also used the checkbox to insert all fields, this resulted in two fields being added that were not listed as fields i could export nor are they listed in the file layouts so i removed them. I've attached images of these two fields' default properties. All other columns are in the default order as they appear in the list of available columns to choose from. Another thing i noticed was that the file layouts said the ItemBillNumber is 30 characters and yet when this field is added to the job, it defaults to 15 characters.

@BethBowers - While WO may one day be in the framework, do you really think Sage would address the index issue by expanding it or changing the code to insert an alpha into the index so it could grow without changing the field length? Similar to what they did with the GL account number to pump it up to 40 characters.

@JeffSchwenk - I'm assuming they will do what they did with Sales Order using the LineKey, etc

@JeffSchwenk - Most definitely WO framework will have a LineKey (and LineSeqNo) concept, not the stopgap compressed index concept (numbers, alpha characters, and symbols) done for SO and PO. @BethBowers - Even if you and David are successful with the import, I don't think you will get back the same WO2 index numbers. Not sure but thinking this could throw off other areas of MAS that store the WO2 index (Materials tab in W/O Entry) in their own data files. ACS is involved so I don't know if any of their programs rely on WO2 indexes as well. Maybe it's not a big deal, who knows but I think there would be after-effects to deal with of some kind anyway. So with that thinking, another (unpopular) option is restore from backup from prior to when 1M indexes were reached, then as David Overholt said, purge and Rebuild Sorts.

You will get new index numbers during the import, My client does not have ACS so I'm not sure if it would affect them or not.

@BethBowers - Are you on the Beta team??

Don't think there IS a beta team yet. That would indicate that there is actually work being done.

From the looks of these issues, it's official. We are OLD, and MAS is even older. We sound like a bunch of RPG programmers talking about how large a stack of punch cards a program can go...

So restoring from a recent backup wasn't an option. Turns out the reason for the VI import failing is because their sy0dct.soa file was for an prior version which had the item bill number starting at the 8th position. Waiting on ACS to get us a corrected dictionary so VI will work. Since the client couldn't be stuck dead in the water, I wrote a little utility to rename the original WO2 file and then create a blank file using its info. It then writes the first index (0) to replicate what the file would look like had it been reinitialized. It then runs through every index record in the original file lower than 999999 and copies the complete record to the new file. After it completes, i ran the Analyze and Relink Files utility, this correctly recalculates the values that should be in the first index record. I created a couple work orders after that, each had a lot of materials and then inspected WO2 and it appears to be recycling unused indexes correctly. I have also set up an alert to catch this in the future when the number of records reaches or exceeds 800,000. I have attached the pvc file containing the utility. It takes two parameters, company code and file name and is called from syzcon. If you place this in the MAS90\WO folder then you would call it like this (without the brackets), [call ""..\WO\RemoveIndexesOver1M.pvc"",""ABC"",""WO2""] These are the steps i would recommend based on dealing with this when you become first aware that an index over 999,999 has been created assuming restoring from backup is not an option and for whatever reason you also can not use VI like the KB states. 1. Backup data. 2. Using Crystal Reports, ODBC tool, or dfdm to identify all work orders that link to an index in WO2 over 999,999. 3. Export any salvageable data for above work orders. 4. Use Work Order Entry to delete above work orders. 5. Optional, use Rebuild Key Files utility to rebuild WO1. 6. Recommended, use Analyze and Relink Files utility on WO2. 7. Use RemoveIndexesOver1M.pvc utility specifying the company code and file name without the company code suffix and without the file extension suffix. Example (exclude brackets), [call ""..\WO\RemoveIndexesOver1M.pvc"",""ABC"",""WO2""] 8. Use Analyze and Relink Files utility on WO2. 9. Optional, use Rebuild Sort Files utility for the Work Order module. Also, from what i could tell, the Rebuild Sort Files utility did not seem to affect the indexes in WO2 as running it after setting the first index (0) to ""0"",0,1,0 did not correct it like Analyze and Relink Files did. So i cannot state that there is any benefit to running it in an attempt to resolve any index related issues in WO2. I'm sure it does what it needs to regarding quantities used, i'm just not convinced it has any impact on the indexes. Use with caution at your own risk.