line 141        if ($i == 0) $hdr .= $k . ";";
line 142             $str .= htmlspecialchars_decode($d) . ";";

using ";" as separator instead of "," when exporting.
so far it works.

Eric

Eric

Hope that's helpful...

Regards,

Chris

Thank you
Eric

Which lines appear wrong?

https://docs.google.com/file/d/0B2hEL1wolzPkYjFwZ1l3M/edit?usp=sharing

Thank you
Eric

tom

I installed csv import modules recently, exported item.csv took a look. but
most fields are placed wrong! eg. "unit" field has value like "services". description string in "category" field...

Am I missing anything?

TIA
Eric

Once you install it, change the pull-down from "Available" to your site and make sure it is enabled.

Then go to Setup->Access Setup, Select your role (System Administrator) and click the check boxes for "Import CSV Items"
at the end of the list.

That will give you an Import option under the "Items and Inventory" tab, Maintenance sub tab on the right.

See the README.TXT in the modules/import_items/ directory

tom

Sorry I have not responded sooner.  Thank you for the very prompt and full reply.  It was a bit above my "pay grade" but it gave me some pointers.  I have the system up and undergoing testing. 

I appreciate your help

Regards

  In order to insert products into FA. You may refer to the table stock_master. For stock category, you may refer to stock_category.

Below are my sample code that I created to synchronize the item in FA. If you need to use it, you need to change the parameter values in the array Object. I have hardcoded for my testing.

-------------------------------------------------------------------------------------------------------------------------------------
$objInfo = array();       

//Array Information           
$objInfo['units'] = 'Each';
$objInfo['units_of_measure'] = '0';
$objInfo['tax_type'] = '0';
$objInfo['qty'] = '1';    
$objInfo['mb_flag'] = 'B';                    

$objInfo['sales_act'] = '';
$objInfo['cogs_act'] = '';
$objInfo['inventory_account'] = '';
$objInfo['adjustment_account'] = '';
$objInfo['assembly_account'] = '';
$objInfo['dim1'] = '';
$objInfo['dim2'] = '';
$objInfo['actual_cost'] = '0';
$objInfo['last_cost'] = '0';            
$objInfo['labour_cost'] = '0';
$objInfo['overhead_cost'] = '0';
$objInfo['no_sale'] = '0';
$objInfo['editable'] = '0';            
       
//Set information
$objInfo['stock_id'] = '123';
$objInfo['description'] = 'abc';           
$objInfo['long_description'] = 'this is a description of the item';
$objInfo['category'] = 'productcategory';            
$objInfo['material_cost'] = '100.00';
$objInfo['price'] = '150.00'
$objInfo['reorder_level'] = '10';
//FA Active Record = 0 and InActive = 1
$objInfo['inactive'] = '0'

Method name: syncItem
-------------------------------------------------------------------------------------------------------------------------------------

/*
* To Sync Item
* $arrObj            -    Item Array Object
* $return            -    Item ID
*/
function syncItem($arrObj) {
    global $log;
   
    $selected_id = '';
    $category_id = '';
    $item_unit_id = '';

    //Retrieve Item Units   
    $sql = "SELECT abbr FROM ".TB_PREF."item_units WHERE UPPER(name) = UPPER(" . db_escape($arrObj['units']) .")";
    $result = db_query($sql, "Unable to find units");
    $row = db_fetch_row($result);
   
    if (!$row) {           
        write_item_unit('', strtolower($arrObj['units']), $arrObj['units'], $arrObj['units_of_measure']);
        $item_unit_id = strtolower($arrObj['units']);
    } else {
        $item_unit_id = $row[0];
    }
   
    //Retrieve Category Id           
    $sqlCategory = "SELECT category_id FROM ".TB_PREF."stock_category WHERE description LIKE " . db_escape(stripslashes(trim($arrObj['category'])));
    $resultCategory = db_query($sqlCategory, "could not get stock category");

    $rowCategory = db_fetch_row($resultCategory);
   
    if (!$rowCategory) { //If category not exist, add new category
        add_item_category($arrObj['category'],$arrObj['tax_type'],    $arrObj['sales_act'],
                        $arrObj['cogs_act'], $arrObj['inventory_account'], $arrObj['adjustment_account'], $arrObj['assembly_account'],
                        $item_unit_id, $arrObj['mb_flag'],    $arrObj['dim1'], $arrObj['dim2'],    $arrObj['no_sale']);
   
        $category_id = db_insert_id();
    } else {
        $category_id = $rowCategory[0];
    }

    //Query Stock ID
    $sql = "SELECT a.stock_id FROM ".TB_PREF."stock_master a WHERE (a.stock_id = " . db_escape($arrObj['stock_id']) . " || a.description = " . db_escape($arrObj['description']) . ")";
   
    $result = db_query($sql, "stock item could not be retreived");
   
    $row = db_fetch_row($result);

    //Default to returning ID
    $selected_id = $arrObj['stock_id'];
   
    //Retrieve the default currency
    $currency_code = get_company_pref('curr_default');

    //Insert or update Item
    if (!$row) {
           
        add_item($arrObj['stock_id'], $arrObj['description'],
            $arrObj['long_description'], $category_id, $arrObj['tax_type'],
            $item_unit_id, $arrObj['mb_flag'], $arrObj['sales_act'],
            $arrObj['inventory_account'], $arrObj['cogs_act'],
            $arrObj['adjustment_account'], $arrObj['assembly_account'],
            $arrObj['dim1'], $arrObj['dim2'],$arrObj['no_sale'],$arrObj['editable']);
    } else {
            $sql = "UPDATE ".TB_PREF."stock_master SET long_description=".db_escape($arrObj['long_description']).",
                    description=".db_escape($arrObj['description']).",
                    category_id=".db_escape($category_id).",
                    tax_type_id=".db_escape($arrObj['tax_type']).",
                    units=".db_escape($item_unit_id).",
                    inactive=".db_escape($arrObj['inactive']).",
                    mb_flag=".db_escape($arrObj['mb_flag']) . " WHERE stock_id=".db_escape($arrObj['stock_id']);
           
            db_query($sql, "The item could not be updated");
       
            update_item_code(-1, $arrObj['stock_id'], $arrObj['stock_id'], $arrObj['description'], $category_id, $arrObj['qty']);
           
           
            if (isset($arrObj['currency_code'])) {
                $currency_code = $arrObj['currency_code'];
            }
           
            $sql = "UPDATE ".TB_PREF."prices SET curr_abrev=".db_escape($currency_code).",
                price=".db_escape($arrObj['price'])." WHERE stock_id=".db_escape($arrObj['stock_id']);
           
            db_query($sql,"an item price could not be updated");
        }
       
        //Update Item Codes
         $sql = "SELECT id from ".TB_PREF."item_codes WHERE item_code=" . db_escape($arrObj['stock_id']) . " || stock_id = " . db_escape($arrObj['stock_id']);
        $result = db_query($sql, "item code could not be retreived");
        $row = db_fetch_row($result);
        if (!$row) {
            add_item_code($arrObj['stock_id'], $arrObj['stock_id'], $arrObj['description'], $category_id, $arrObj['qty']);           
        } else {
            update_item_code($row[0], $arrObj['stock_id'], $arrObj['stock_id'], $arrObj['description'], $category_id, $arrObj['qty']);
        }
        
    return $selected_id;
}

-------------------------------------------------------------------------------------------------------------------------------------

Can someone tell me the structure required to import products into the Inventory.  I am testing the latest version (2.3.5) and can't seem to import anything.

If there is not a module for importing the inventory through Frontaccounting can someone tell me the tables that are affected when items are directly added to the database.

Thanks in advance