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

SOLVED

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)

  • 0

    From doing a lot of research, I suspect this cannot be done simply.   I think I need to open the inventory tables with Access via ODBC (already done) then use a SQL query to enter all the values.    If this is the case then I have a LOT more work ahead of me.  

  • 0 in reply to Ayanefan

    Please be aware that the Sage MySQL database does not enforce any referential data integrity at the database level (it is all done in the program), if you update individual tables via ODBC; it is up to you to update any and all related tables, perform all test work on a copy of your data and TEST carefully.  if you mess it up it is going to be a real headache to fix!!!

  • 0

    Hello Ayanefan

    Currently there is no method of importing balances, quantities or values in to Sage 50 using a CSV. If you are able to open the database using Access and update the inventory values, you would need to make sure the linked accounts to those items are also correctly updated. If the inventory list does not match the gl accounts linked to the items it will give you a discrepancy in your database.

    Harjot Aujla

  • 0 in reply to HarjotA
    verified 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.

  • 0

    Thank you very much everyone for your answers and thank you RandyW for the detailed reply.  Somehow I think I will end up pushing buttons at Sage support for this.  It is ridiculous to think that someone who already has a business and looking to move the data over has to do all this.  *sigh*, I know I can do it, it just looks like it's going to be a full time job for a while.