• paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Topic: Problem Importing Item Purchase Price

I am using FA v.2.4.7 and the extension to import item data into my setup. So far, I have been able to build and import the items, themselves, and their prices. I am having problems importing purchase data or maybe I misunderstand what it is suppose to do, but I get an error anyways.

  When I import BUY purchasing data, the Standard Unit Cost is updated and is correctly calculated using the imported files unit_conversion information along with the price in the file. The Purchasing Pricing tab does not show the predefined supplier nor did the import operation update existing data (after I manually enter the supplier and outdated pricing (for this test)).

  After I click on Import, I get two green banners toward the top of the page:

Supplier MainVendor not found

and

1. Purchasing Data items added or updated.

  I have even exported the same data, saved and modified this file's pricing and tried to import, but I get same results. If I understand the import code, the supplier field is looking in the "supp_name" column of FA database's suppliers table.

  What am I doing wrong, please?!

  Thank you,

Paul

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Problem Importing Item Purchase Price

Are you referring to the Inventory Items CSV Import module?

apmuthu's Website

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Problem Importing Item Purchase Price

Yes - yes. I downloaded those same files. I am looking through import_items.php and am looking at the get_supplier function to try to make sure it's looking at the correct database, table, field, etc. the function returns a 0. I even exported the current data to make sure I understand what I need to input.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Problem Importing Item Purchase Price

Lines 43 to 49 of import_items.php:

function get_supplier_id($supplier) {
    $sql = "SELECT supplier_id FROM ".TB_PREF."suppliers where supp_name = ".db_escape($supplier);
    $result = db_query($sql, "Can not look up supplier");
    $row = db_fetch_row($result);
    if (!$row[0]) return 0;
    return $row[0];
}

If an existing supplier name is available in the target table, then it will return the supplier_id, otherwise it returns a 0. Make sure that the Supplier Name is exactly the same as the one in FA when an import occurs.

apmuthu's Website

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Problem Importing Item Purchase Price

Yes - That's the part I am looking at.

  I attempted to make sure I had exact name. I even added a supplier manually to FA so I could export that data to compare. I saved the export, changed a price and tried to import the otherwise same file, but it could not find supplier.

6 Reply by paul (edited by paul 08/02/2019 01:03:47 pm)

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Problem Importing Item Purchase Price

OK - Maybe this can help. This is my test import file contents:

type,stock_id,dummy,supplier_description,supplier,suppliers_uom,conversion_factor,dummy1,USD,price
BUY,12345,,,MainVendor,Ea,576,,USD,75

It looks like the import extension is requesting the supplier name and also adding "'" (without the quotation marks) around the supplier's name:

...
SELECT * FROM 2_stock_master where stock_id = '12345'
SELECT supplier_id FROM 2_suppliers where supp_name = '"'"MainVendor"'"'
SELECT material_cost FROM 2_stock_master WHERE stock_id = '12345'
...

  (again, without the quotation marks) The request gets the stock_id correctly. I also tried adding quotation marks around the supplier name in my file, but did not help. Anyway to change this issue?

  Thank you...

  • paul
  • Senior Member
  • Offline
  • From: USA, Columbia
  • Registered: 07/22/2019
  • Posts: 106

Re: Problem Importing Item Purchase Price

OK - I don't know if I should be altering files, nor do I know if this change is a good idea, but in the import_items.php file, at the "function get_supplier_id" portion, I changed this line:

$sql = "SELECT supplier_id FROM ".TB_PREF."suppliers where supp_name = ".db_escape($supplier);

to this:

$sql = "SELECT supplier_id FROM ".TB_PREF."suppliers where supp_name = $supplier";

  And my test file seemed to have imported correctly. Please let me know if this is allowed (for me to change it) and if it is acceptable as far as code is concerned.

  Thank you,

Paul

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Problem Importing Item Purchase Price

Check if your input data had any extraneous characters that caused a mess up in escaping (or double escaping?). The escape was to prevent SQL injection.

apmuthu's Website