Field Mapping--Inventory Item Import - 3a - Supplier Item Numbers & Costs

Author: Steve Childs Reference Number: AA-02432 Created: 2016-09-14 10:41 Last Updated: 2016-09-14 14:35 0 Rating/ Voters

Overview of the Inventory Item List - Supplier Item Numbers & Costs Import Template

OneSource provides a way for each item to have an unlimited number of suppliers for each item.  This import spreadsheet is designed to help you import the proper Supplier information for each item.  It may be helpful here to explain more of the 'Big Picture' of how this works. Before this list can be imported the Inventory Item List has to be imported first.  This will create a record for item inventory item for the items on this list to 'link' to (by the ItemNumber).  Next, the simplest way to get the proper Supplier cost for each item is to use the Auto PO system to output an excel file of the items you need to buy (by the Supplier(s) you select). Then you can send this spreadsheet to the Supplier(s) and ask them to update the Costs, their item #, case pack, etc.  Then you will be able to 'Import' this spreadsheet directly into OneSource.  For each item on the list OneSource determines if there is already a 'Default Supplier' for the part. If not it makes that supplier, along with their item # and item cost the defaults for the item.  If there is already a Def Supplier for the part it add the same information into OneSource as an Alternate Supplier.

Sample Import Spreadsheet 

(Note: This is the excel spreadsheet format the OneSource Exports on the PO, the Auto PO, and the Inventory menu (Inventory Item - Import/Export, tab 2).

OurItem SupplierItem ItemDescription OEM Partslink SupplierCost ListPrice Make Model YearRange CuSqFt CasePack QtyOrdered QtyShipped BinLocation LotNum DateExpected QtyInStock UnitOfMeasure
 HO1000 HO1000178  98 Honda Accord - Front Bumper Cover   04711S84A90ZZ   HO1000178 $35.34  $150.00  Honda  Accord  97-02  2.1  16  16  H-10-12    8/1/2016  50  Each 

Additional Notes about the fields listed above

  • OurItem: This is YOUR Item Number.  It is called OurItem because you typically send this spreadsheet to your Suppliers to get an updated prices on a list of parts.
  • SupplierItem: This it your suppliers internal Item Number.
  • OEM: This is the OEM part #.
  • SupplierCost: This is one of the primary fields you will ask your supplier to fill in before sending back to you.
  • ListPrice: This is the Supplier suggest List price.  This field can be useful when available but is not required.
  • CuSqFt: This is the Cubic Square Feet that this item will take up in a Sea Container.  This is helpful when trying to determine what % of a container you have filled up when creating a Container PO.
  • QtyOrderd: This is the Qty of the item that you MAY order when you are submitting a spreadsheet to get quotes from.  This is also the amount you ARE ordering when you use the spreadsheet to PLACE the order.
  • QtyShipped: This is left blank when first sent to the Supplier for price quotes.  After you send the spreadsheet back to the Supplier (when you PLACE the Order) they need to use the spreadsheet to update the Qty they actually loaded onto the Sea Container and then send back to you--so you can import (update) the Qty ordered on the PO.
  • BinLocation: When you use this spreadsheet format to Order parts from a Supplier, if your supplier is willing to pre-print labels with your BinLocation they can get your BinLocation from this column.
  • LotNumber: If you company is NSF Certified, or would like to be in the future you can ask your Supplier to insert the Lot # on each line in the LotNum field.
  • DateExpected: This is for you Supplier to enter for you, or for you to enter after receiving the spreadsheet back from the supplier so the Date Expected on the PO can be updated (after you import the spreadsheet on the PO).
  • QtyInStock: Like the List Price field above, this field can be very useful (when imported into OneSource) especially when the Supplier is located close to your business for Special Order PO's.  This field is very useful but not required.