I imported all my inventory, but now how to I import opening balances

Question

Hey Everyone, 
 I'm sure this was answered in the past but I can't find the post, maybe someone can help me out. I have Sage 50 2015 Canadian edition, I imported over 16,000 items (yes, 16,000) as we are moving from DacEasy 9.0 to Sage. All my items are entered but I have 0 units for all. I need create a CSV file and import all my unit quantities and inventory balances. Is there a way to do this with a CSV? 
 Thank you all in advance. 
 ayanefan 
 (Jacques)

RandyW · Accepted Answer

The opening balances should be in tInvByLn, as 
 dInStock 
 Number 
 FT_REAL 
 8 
 Quantity of goods on hand 
 dCostStk 
 Number 
 FT_REAL 
 8 
 Cost of goods on hand 
 dLastCost 
 Number 
 FT_REAL 
 8 
 Most recent recorded unit selling price The documentation is wrong, this is the calculated Average, selling cost at this Location (if you are using Weighted Average Cost.) It is recalculated after each transaction, as far as I can see, it is used for Negative Inventory cost calculation. 
 The Historical balances are in: 
 dHInStock 
 Number 
 FT_REAL 
 8 
 Historical quantity of goods on hand 
 dHCostStk 
 Number 
 FT_REAL 
 8 
 Historical cost of goods on hand 
 Since these entries are not transactions, and are stored in one table, there's no real worry about referential integrity 
 The above is correct as far as I know, when you are using Average Cost. If you intend to use FIFO, starting out with Average Cost and converting should be simpler than creating 16,000 cost pools. 
 If you want to use FIFO, but start out with your existing cost pools, you'll have to work out how the tInvLot table is used by Sage 50, then populate the fields with your data. If the data dictionary is correct, a 4 byte lot ID will run out of gas after 64,000 inventory lots, so at 16,000 items you might not get far usint FIFO if you have a lot of turns.

lId

Number

FT_LONG

4

Inventory Lot ID

If you want to enter the data more 'safely' : 
 - use a keyboarding program, such as 'AutoHotKey'. It's a free program to use, it has saved a great deal of wear and tear on my fingers when a consultant was confused about 'item' versus 'description' on 15,000 items he imported. 
 - have a programmer knock something together using the SDK, or 
 - look for a third party utility. Swagman Software makes an import utility. I don't know whether it does this, or not. 
 and / or 
 Contact Sage and tell them you're not amused, and that they need to provide a reasonable conversion utility. I think they would agree that It should not be harder to switch between Sage products, than away from them.

Sage 50 Canada

I imported all my inventory, but now how to I import opening balances

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING