Tools

Field Mapping--Inventory Item Import - 1a - Main Item List

Author: Steve Childs Reference Number: AA-01850 Created: 2012-02-13 10:19 Last Updated: 2016-09-14 17:59

0 Rating/ Voters

Overview of the Inventory Item - Main Item List Template

This is the MAIN Inventory Item List field mapping spreadsheet. This import method is for Auto Body Supply companies. Items placed on this spreadsheet will be imported into the Inventory table in OneSource. The following 2 tables should be as a template to cut and paste into a new SINGLE spreadsheet.

Spreadsheet Template Example of Column Headings (with some sample data)

Sample Import Spreadsheet (Part 1)

ItemNumber	Description	Partslink	OEM	ItemType	Category	SubCategory	YearRange	Make	Model	QualityIndicator
HON1000	98 Honda Accord - Front Bumper Cover	HO1000178	04711S84A90ZZ	Part	Bumper	Front Bumper	97-02	Honda	Accord	A
HON1500	08 Honda Accord - Front Bumper Cover	HO1000254	04711TA0A90ZZ	Part	Bumper	Front Bumper	08-12

Sample Import Spreadsheet (Part 2) (These column headings should be cut and pasted into the same spreadsheet your create for Part 1 above)

ListPrice	OEMPrice	UploadToOSOnline	Commissionable	Taxable	PricingCategory	MatrixPricing	Min	Max	Weight	CasePack	UPC	BinLocation	Manuacturer
80.50	250.35	Yes	Yes	Yes	Bumpers	No	1	4	25	4	234234566234	H-10-12	Honda
112.50	378.89	Yes		Yes	Bumpers	Yes						AA-1-H10	Honda

Big Picture Note: There will be other related inventory values that will need to be imported (or manually filled in) after this Inventory Item List is imported, including:

Inventory Qty in Stock. (This will be imported automatically when you fill out the Inventory Item Import - 2- Existing Stock Levels & Costs spreadsheet).
Supplier Information. (This will be imported and automatically added/updated using Inventory Item Import - 3a - Supplier Item Numbers and Costs spreadsheet(s)).
Pricing Information. (If you use a 'Price Matrix' for any or all parts these will be imported when you use the Inventory Item Import - 4 - Item Price Matrix spreadsheet).
Catalog Information. (If your Inventory Item List spreadsheet has parts that you know fit more than 1 make and model do not worry as the catalog will be imported using the Inventory Item Import - 5a - Parts Search Catalog spreadsheet.
Accounting Information. (After you create GL Accounts in OneSource for Sales, Sales Discounts, Inventory, and Cost of Goods Sold we will use the Account ID for each of those account to update your item list).
All items imported will be set to Active = True and will update the Date Added as the date of the import.

Additional Notes about the fields listed above

ItemNumber: This field is required. This is YOUR item #. Item ItemNumber on your spreadsheet must be unique.
Partslink: This is the Partslink #
OEM: This is the OEM number for the part.
ItemType: Type Part if the item is a tangible item that you sell--regardless of whether or not you 'stock' the item. If the item is some type of 'Service' type Service in the column. If the item is some type of 'Labor' type Labor in the column.
QualityIndicator: If the part is a typical 'Aftermarket' part of standard quality type A in the field. If the Part is a 'CAPA Certified' part type C in the field. If the Part is an 'NSF Certified' part type N in the field.
PricingCategory: If you want to be able to price certain types of items with a larger or smaller discount (or larger or smaller markup percentage) depending on the type of item it is type the Category in that field. We will then import all of the PricingCategory values into the Product Pricing Group table.
MatrixPricing: If you want to be able to create 2 or more prices for a given part type Yes in the MatrixPricing field. You will create the price levels when you create the Inventory Item List - Matrix Prices spreadsheet.
Min: If you plan to use the Auto PO tool in OneSource that helps you determine which items need to be re-ordered, because they have fallen below a particular 'Minimum' level enter that level in the Min field.
Max: If you plan to use the Auto PO tool in OneSource that helps you determine which items need to be re-ordered, because they have fallen below a particular 'Minimum' level enter the Maximum level you what to have on the shelf after you create a PO to re-order the part. For Instance. If you wanted to order Item X when it falls belows 2 left in stock and then bring it back up to 10 you would enter 2 in the Min field and 10 in the Max field.
BinLocation: If your items are typically stored in just 1 location enter the BinLocation in this field. If you use some type of 3rd party Warehouse Management System (WMS) or if you end of putting parts in multiple locations (when you put away parts received on a PO) you can leave this field blank. Be sure to put the BinLocation track, per PO, where you put the par