Purpose'

To import a list of items and prices into a fresh install of FA v2.3.x from some other accounting system into FA manually.

Procedure

  • Each Item is stored as one record in the stock_master table (primary key: stock_id) along with costs and CoA Account codes for various transactions like manufacturing, sales, etc.
  • Each Item is also stored in the item_codes table as well with information pertaining to block quantity of sales and whether it is a foreign item (in which case the Item description here will override the one in the stock_master table and an alternate name for the Item is stored in the item_code field).
  • Each item is allotted a price in the prices table for each sale_type_id listed in the sales_types table which has defaults of
  1. Retail
  2. Wholesale

Items Accounts Settings

Assumptions

  • Company # = 1
  • Currency = USD
  • Default Sales Types of Retail and Wholesale related to Cost Price:
    • Item Cost Price field name = CP
    • Item WS Markup factor = WMK (eg., 1.15 => 15% Markup)
    • Item Wholesale Selling Price = CP * WMK
    • Item Retail Markup Factor = RMK
    • Item Retail Price = CP * WMK * RMK
  • Item Description field = ItemDesc
  • Numeric CategoryID field = category_id
  • Stock Item ID field = stock_id
  • Unit of measure = UOM (eg., each)
  • All items bear Tax Type Id = 1 (taken from id in tax_types table)
  • All items are sold in blocks of quantity of 1
  • All items are at Default Location = DEF (taken from loc_code in locations table)
  • Reorder Level field = ROL
  • Table name containing raw items data = items_list
  • Fields in items_list table to import from => stock_id, ItemDesc, category_id, UOM, ROL, CP, WMK, RMK
  • All items are of "B" type (Bought Out / Purchased)
  • sales_account = 4010
  • cogs_account = 5010
  • inventory_account = 1510
  • adjustment_account = 5040
  • assembly_account = 1530

Import SQLs

  • First we import the items into the stock_master table: 
INSERT INTO 1_stock_master
SELECT stock_id
    , category_id
    , 1 AS tax_type_id
    , ItemDesc AS `description`
    , '' AS long_description
    , 'each' AS units
    , 'B' AS mb_flag
    , 4010 AS sales_account
    , 5010 AS cogs_account
    , 1510 AS inventory_account
    , 5040 AS adjustment_account
    , 1530 AS assembly_account
    , 0 AS dimension_id
    , 0 AS dimension2_id
    , 0 AS actual_cost
    , 0 AS last_cost
    , 0 AS material_cost
    , 0 AS labour_cost
    , 0 AS overhead_cost
    , 0 AS inactive
    , 0 AS no_sale
    , 0 AS editable
FROM item_codes
ORDER BY stock_id;
  • Then we import the items into the item_codes table: 
INSERT INTO `1_item_codes`
SELECT 
      NULL AS id
    , stock_id AS item_code
    , stock_id
    , ItemDesc AS `description`
    , category_id
    , 1 AS quantity
    , 0 AS is_foreign
    , 0 AS inactive
FROM items_list
ORDER BY stock_id;
  • Populate the loc_stock table with the stock_id's they have: 
INSERT INTO `1_loc_stock`
SELECT 
      'DEF' AS loc_code
    , stock_id
    , ROL AS reorder_level
FROM items_list
ORDER BY stock_id;
  • Now the prices get in 
INSERT INTO `1_prices`
SELECT NULL AS id, stock_id, sales_type_id, curr_abrev, price FROM (
SELECT 
       stock_id
    , 2 AS sales_type_id
    , 'USD' AS curr_abrev
    , CP * WMK AS price
FROM items_list
UNION
SELECT 
      stock_id
    , 1 AS sales_type_id
    , 'USD' AS curr_abrev
    , CP * WMK * RMK AS price
FROM items_list
) a ORDER BY stock_id, sales_type_id;
  • We are now done!

Tips and Tricks