Sage 100

Importing Beginning Balances My customer has hit the Providex limitation of 16 2 GB files in Inventory twice, once in 2013 and again in 2015. Their IT department's solution was to cut and paste the 17 files to another location and start with a new <filename.M4T> so they could keep going. What this does is throw off the Inventory > inventory Inquiry > History tab Beginning balances. Literally not one Item has an accurate history. We can't run the Recalculate Item History because it picks up data from some other file and starts recalculating from 2003. As soon as it hits the periods where the data was Cut, all future History is off. Way off. The customer is willing to accept good history from Jan 1, 2015 forward if I can do that. I have the Beginning Balance of every Item as of that date in an Excel Spreadsheet. Sage says this data is held in the IM_PeriodPostingHistory but the Beginning Balance field is not one of the fields we can import into. BTW, Sage says this problem is beyond the ability of Sage Support and wished me luck. At one time, I remember one of the 90Minds group showed us how to turn on a field in the Data Dictionary but I can't find the instructions. I have 3 questions... Does anyone have the instructions to ""open"" a field in VI to be imported? If so, by importing into this field, will that do any damage anywhere else? Is there a better way to enter good Beginning Balances as of say Jan 1, 2015 and have the history flow forward? If I can get this problem worked out, the Customer has agreed to move to Sage 100 Premium to eliminate the Providex file size restrictions going forward.

Possible solution..... Instead of backing up and reinitializing the item history files, backup the live company to a ""History"" company so the item history is retained in the history company (similar to what everyone does with payroll each year). Then, use the IM utility, Purge Item History to reduce the size of the files. I believe (please test this) the beginning balances would be retained. I would test it in a test company before recommending it.

DFDM. It is not efficient and will take time, but should work.

You would probably have to set the year ahead to be able to purge everything out through the end of 2014. Then, again by manipulating the periods hither and yon, be able to import into Physical Count for a 12/31/14 count.

The best solution maybe to get them to Premium and grab the 17 other files and get them into premium also and then use all that data to recalculate the history(since there would be no limit at that point). we might recalculate in a test company all the previous years and then combine the data from the two companies. Premium would make this more easier for sure. Ideally would be to get the recalc process to be ran via SQL and not the pvx code. It would probably knock it out in a tenth of the time. which field do you need opened up in VI to import into?

This may not be the best approach, but here is the solution you were asking for with the data dictionary: How to Activate the Beginning Balance Data field in IM_PeriodPostingHistory. Step 1 1.DFDM > Providex.ddf 2.Key No: ByName 3.Click on Key Scan 4.Find data table [Key] to change/modify: IM_ Period Posting History and select it 5.Change Key No. Primary Key 6.Write down the Key=[xxxxxx] Step 2 1.DFDM > Providex.dde 2.Click on Key Scan 3.Search = Key Value (from step 1, Line 6] 4.From the list select the Key to change e.g. BeginningBalQty Key=000489000005 5.Click on the Edit button 6.Field Number = 20, Starting Position = 1, Length = 15 (the field to be change will be display, so the value can be change to: 0 7.Change = from 1 to 0 8.Click on OK and close DFDM Step 3 1.Create a new V/I import template 2.Select Table Name: IM_PeriodPostingHistory 3.Go to Tab2 [Data] and notice the BeginningBalQty is now available.

I like Beth's approach because her solution creates a permanent transaction. If you instead, import to a periodic ""bucket"" type of file, you run the risk of losing your work when that IT department does a recalc..

2015 and a 2Gb limit? I am confused. I am currently working with test converting a 4.30 to 2015 upgrade and was curious about how it was going to handle the fact that the GL_DetailPosting file was over 2Gb - without me doing a thing, the conversion simply split the file into multiple tables. GL_DetailPosting.M4T and GL_DetailPosting.M4T.001 - DFDM sees it as one logical table. Are you telling me I/M does not do this?

Randy I/M does do that but in this case there are 16 files at 2GB (last file is .015). That appears to be a system maximum.