Sage 100

Upgrading client from 4.30 to 2013 and the Pre-Migration utilities turned up 8500 inventory items that have no valuation method or product line. Turns out the client had imported this mess. We have decided to export the IM1 file to Access and remove the bad records, then reint the IM1 file and import the good one back. Attempted to use VI to export IM1 but after only 3300 records, received error ""Report output exceeds printer width or record length exceed on file write operation"". I don't have Access or Excel on the client's server so an ODBC connection is not an option. Thoughts?

Use Crystal for the export

Worth a shot but will I have a similar problem with the import?

Hopefully the problem children are the ones you will be removing...

This a legacy Error 1. Did you try to rebuild key files on IM1?

Can you use an ""ALE"" lookup from a workstation with Excel?

Customized and export all the fields.

It's been so long since I've dealt with 4.3 (old framework IM). I took Phil's advice and created a Crystal Report using IM1 and selected all fields. Exported it to Excel. Found that although all items exported, the UDF fields were missing. Ugh. Back to Crystal and noted that they are not in the IM1 data dictionary. Did a new Crystal Report, this time selecting CI_Item where the UDF fields are defined. Exported the lot but there was nothing in the UDF fields, The data is in IM_90, but for the life of me, I can't figure out a way to dump this to a file.

Oh, I should mention that I did force the UDF data dictionary update and try again to no avail.

Your Crystal report needs to link IM1 with IM_90 to get the UDFs. You will need two separate imports when putting them back in.

That's the rub. In Crystal, there is no IM_90 table. I can see IM1 and CI_Item. CI_Item shows the UDF fields, but nothing shows on Browse or if I export the report.

I just created a report using IM1 and CI_item and was able to get the UDF from CI_Item and the rest of the fields from IM1

And one would think I could as well. That's what's so frustrating. I can see the data in IM_90 but if I look at an item in Inventory Maintenance, the UDF fields are blank on the screen. That's why I forced the screen update. If I add a new item, the UDF data does appear. I've rebuild key files too.

Starting on v4.1. All the UDF's are mapped to the Ci_item table. If you create a crystal report join the im_im1inventorymasterfile and ci_item with a left outer join (im1 table being the left table ) you should get all you items and Udf's. note do not use im_itemcodemasterfile it is a work table only.

OK. I've recovered. Here is what I learned from the school of hard knocks. CI_Item and IM1 are related meaning if you reinitialize IM1, corresponding items are removed from CI_Item. IM_90, even though it had UDF data, IS NOT USED leading me down the primrose path. Once I restored CI_Item from a recent backup, all was well. UDF data now appears on the screens and on the Crystal Reports. The moral of this story is make darn sure you've got a complete backup for reinitializing files

Time for a shot and a beer.....

Don't you know it!

I want to know what benefit there was to add the CI_Item file to Inventory. I know I am just a simple-minded consultant, but I just don't get it. Aren't there enough bloody data files already?? Don't we already have difficulties linking files in Crystal? Sheesh, when was the last time we were given a cross reference tool.......OMG, am I showing my age or what?? Sorry, I will take my old self and go do something constructive.......

And, if it's not bad enough that we don't have cross reference tools, every standard report is based on a workfile whose source is sometimes difficult to ascertain.