* Inventory - Import/Export

Overview

The Inventory Import/Export screen has been designed to help the user accomplish these tasks:

• Import new parts into the Inventory table.
• Update existing parts in the Inventory table.
• Import new items and/or replace existing items in the searchable Inventory Catalog (tblInventoryAlias table)
• Import new supplier items numbers and supplier item costs (which are linked to existing Inventory items).Update existing supplier item number costs and other values.
• Export certain formats of parts for various purposes.

Using the Import Inventory Items tab

How to import a list of inventory items

• Open the Inventory Item List - Import/Export form. (Inventory > Inventory Item List - Import/Export (Import Inventory Items tab)
• Select an Import Method
• 1--Import/update Inventory Items - Standard (using MS Excel file)
• 2--Import/update Inventory Items - Automotive (using MS Excel file) ***See Below***
• Click the Browse button and browse to the Excel file that has been properly formatted to import.  
• NOTE: To create an excel file that shows the proper column headings click on the button to the right of the Import Method selected called Create Excel Import File Template.
• Click Extract from Spreadsheet button (to load the data from the selected MS Excel file into the import tool.
• Click Update/Commit button to import the list into OneSource.
•  Note: If any errors occur they will be displayed on the screen.
• *** Description of each field in the Excel File.***
• ItemNumber - This is your companies actual Item number they use when selling the parts.
• Active - This is the indicator that is used to indicate that the item is active or not.  -1 = Active and 0 = Inactive.
• Desc - This is the Description of your Item Number
• Category - If you want to group your item numbers for searching/reporting you can enter Categories such as Hoods, Fenders, Lamps, etc...
• SubCategory - You can narrow down your searching/reporting even further you can add a Sub Category such as Hood panel assy, Lt Front fender assy, Rt Fog lamp assy, etc...
• POCategory -  You can group your items into a Category that can be used when using the Automated Purchasing tool in OneSource.  Example: if you buy all your Hoods, Fenders and Bumpers from 1 or more suppliers and you only want to see these type of items when you are making a purchase order for these suppliers you can create a PO Category to put all of these type of items into and then you have the option to select only this PO group when doing your purchasing.
• YearRange - This is the Year Range that this Part will fit.  You have to use the format 00-16 which would mean that this item fits all 2000 to 2016 years.
• Make - This is the Make of the Car. Example: Ford, Chevrolet, Nissan, etc...
• Model - This is the Model of the Car. Example: Fiesta, Silverado, Rouge, etc...
• Taxable - This is the indicator that is used to indicate that the item is taxable or not. -1 = Taxable and 0 = Not Taxable.
• ListRetailPrice - This is the price that you want for your List/Retail price.  This price field will also be used when calculating any Discount pricing.
• GLInventoryID - For Accounting purposes this is the Inventory General Ledger Account Internal ID.  After you have entered your General Ledger accounts into the Chart of Accounts in OneSource you will see an ID field to the left of the Account Description.  This is the ID that needs to be entered on this spreadsheet.  Example:  If your Inventory General Ledger account is 1100 in the chart of accounts look for the ID field to the Left of the Account description and you will see a different number such as 593.  The 593 is what will need to be entered into the spreadsheet.
• GLSalesID - For Accounting purposes this is the Sales General Ledger Account Internal ID.  After you have entered your General Ledger accounts into the Chart of Accounts in OneSource you will see an ID field to the left of the Account Description.  This is the ID that needs to be entered on this spreadsheet.  Example:  If your Sales General Ledger account is 4000 in the chart of accounts look for the ID field to the Left of the Account description and you will see a different number such as 595.  The 595 is what will need to be entered into the spreadsheet.
• GLCogsID - For Accounting purposes this is the Cost of Goods Sold General Ledger Account Internal ID.  After you have entered your General Ledger accounts into the Chart of Accounts in OneSource you will see an ID field to the left of the Account Description.  This is the ID that needs to be entered on this spreadsheet.  Example:  If your Cost of Goods Sold General Ledger account is 5000 in the chart of accounts look for the ID field to the Left of the Account description and you will see a different number such as 597.  The 597 is what will need to be entered into the spreadsheet.
• OEMNum - This is the Original Equiptment Manufacturer's part number.
• PLNum - This is the Parts Link number
• ProPricingGroupID - You can setup various Product Pricing Groups that can be used to give parts in similar groups the same type of pricing.  When you create the Product Pricing groups there is an ID assigned to each Group.  This is the ID number you need to enter onto this spreadsheet.  Example:  If you wanted to give all Bumpers a Discount of 35% you can create a Product Pricing Group called say Bumpers.  And then put that pricing group for all Bumper Items.  Then you can create custom pricing templates in OneSource for this group of items giving all of the items in the Bumpers group 35% discount. 
• QualityIndicator - This is a Quality Indicator that is used for Audatex, APU, CCC, Mitchell, PartsTrader, and Car-Parts to indicate the Quality like A-Aftermarket, C-Capa, R-Recycled, etc...
• Min - This is the Minimum Qty of inventory that you wish to have in stock before you want to purchase more.
• Max - This is the Maximum Qty of Inventory that you wish to order when you reach the Minimum Qty level in the Min Field above.  Example:  If your Minimum is
• PickingOrd - This is the Picking Order that the warehouse people will use when pulling the inventory off the shelves.
• Location - This is the Location in the warehouse where this item is stored.
• Bin - This is the Bin located in the Location above where the item is stored.
• CuSqFt -This is the Cubic Square Foot that the item takes up when ordering containers of the product.
• CasePack - This is Case Pack.  It is the number of items that come in a case when purchasing.
• RndUpToCasePack - This is an indicator called Round Up to Case Pack.  -1 = Round Up to Case Pack and 0 = Do Not Round Up to Case Pack.  This allows you to indicate that when using the Automated PO ordering if it suggests that you order 4 based on past sales history and your case pack is 6.  It would then tell you to order 6 if this indicator is selected.  If the indicator is not selected it would just tell you to order 4.
• SpecOrd - This is Special Order.  -1 = Special Order and 0 = Not Special Order.  Example: If you have an item that you want to always special order and do not stock on a regular basis you would put -1.  Often times these are either very expensive items or items that you rarely sell.  We do have a feature in OneSource to automatically mark an item as special order on a Sales Order when you are out of stock on the item.  In that case you would not need to have the item marked as special order using this feature.  This option in OneSource will then let you sell the item as normal when you do have inventory in stock for the item.  If the special order indicator is set to yes.  It will always want you to special order the item.
• LastinvDate - This is Last Inventory Date which is the date you last counted the inventory on this item.
• MasterItemnumberSort - This is the Master Item Number Sort.  This is used in the Quick Price check to allow you to view items in the sort order you wish.  Typically you want to see the front of the car to the rear of the car.  So if you were looking up a 13 Honda Accord you may want to set the Front Bumpers with a lower sort order number than the Rear Bumpers.  Most customers indicate the sort order by the PTYPE of the item as they are typically sorted in that order.
• DefSuppNum - This is the Default Supplier Number.  When you add new suppliers into OneSource you are required to have a Supplier Number.  This is the number you would enter into this field for the Supplier that you purchase this item from most of the time.  Or the one with the lowest price that you want to try to purchase it from when you can.
• DefSuppItemNum - Default Supplier Item Number.  This is the Suppliers Item number that you use to purchase this item from the Default Supplier listed above.
• DefSuppItemCost - Default Supplier Cost for the item number from the Default Supplier listed above.
• DefSpecOrdSuppNum - Default Special Order Supplier Number.  Typically this is a Local Supplier where you can get the item the same day or within a few days.  There is a setting in the company setup and preferences that you can set to select the Default Special Order Supplier when you have the setting set to Auto mark the item as special order when item is out of stock.  This allows you to put your local suppliers for the Default Special Order Supplier and your Overseas suppliers for your default suppliers when purchasing containers of inventory.
• DefSpecOrdSuppItemNum - Default Special Order Supplier Item Number. This is the Default Special Order Supplier Item Number that you use to purchase this item from the Default Special Order Supplier listed above.
• DefSpecOrdSuppItemNumCost - Default Special Order Supplier Item Number Cost. This is the Default Special Order Supplier Item Number Cost for the item number from the Default Special Order Supplier listed above.
• InterchangeNumber -  This field allows you to enter an additional item number that can be used when searching using the Quick Price Check.  Example:  If you have your item number as FO1000103 and you also have customers ordering this item number as FO012ABC.  You can enter FO012ABC into the interchange number field and then in the Quick Price check you can enter this into the PartNumber* field and find the item FO1000103.
• Unit of Measure - Unit of Measure for the item above i.e. Each, Case, Roll, Sheet.

Export File Data Types and Field Explanations

Using the Import/Update Supplier Costs tab

This topic shows how to Import or Update Supplier Item Lists ( and link them to existing Item Numbers in OneSource )

• Step 1: Obtain an MS Excel file from your Supplier (or a .txt file that can be opened up (and viewed in Excel) and SAVED as an Excel file in .xls format) and place in a location of your choice on the OneSource file Server.
• Step 2: Make sure the file has been properly formatted to have the exact file headings (on row 1 of the .xls file) as the headings listed below:
• OurItem (This is a KEY value. This is going to be the way this list your are importing will be LINKED to an existing item number in the OneSource Inventory list/table.  This value must ALREADY exist in OneSource as the OneSource Item Number.  Note: For Auto Body Supply companies, this field is required if any duplicates to PartsLink # exist, such as Capa items, etc)
• SupplierItem  (This field is required)
• ItemDescription
• OEM  (This data will be imported into the Model field in OneSource - where Model is equal to the Manufacturer's OEM #)
• Partslink (This data will be imported into the ManufacturerItemNumber2 field)
• SupplierCost  (This is the supplier cost of the item from the Supplier and will be imported into the Cost from Mfg field in OneSource in the Inventory Table) 
• ListPrice (This data populates into the Retail price field in the Inventory table)
• Make  (This data populates into the SubCategory2 field in the Inventory table)
• Model  (This data populates into the SubCategory3 field in the Inventory table)
• YearRange  (This data populates into the SubCategory1 field in the Inventory table)
• CuSqFt  (This data populates into the SubCategory2 field in the Inventory table)
• CasePack
• QtyOrdered
• QtyShipped
• BinLocation
• LotNum
• DateExpected
• Do Not Edit

Setup Notes
1--If the file that you received from your Supplier has extra columns please delete them (as there will be no place to store the data. If you feel that there needs to be a place to store additional data please contact OneSource).
2--If the file that you received from your Supplier is missing any required columns, please insert a column with the exact column heading as shown above.
3--It is OK if certain fields (most of the fields) are blank (so long as they are not one of the required fields).

Additional Notes: If the list is from Supplier X and if the item already has a 'Default Supplier' associated with the item (from Supplier Y) THEN the Supplier Item # info WILL be imported and associated to the item in OneSource as an Alternate Supplier. THE SINGLE EXCEPTION TO THIS RULE...is when the 'Default Supplier' for an item has a Supplier Number = NoSupplier, then in this case it will remove the NoSupplier as the Supplier Number for the item and insert the Supplier Number of the selected Supplier when you import the list.

• Step 3: Import the file by following these steps:
• With OneSource open click: Inventory >Inventory Item List - Import/Export > Import Supplier RFQ's
• Click the Browse... button next to the long field under the headingBrowse for RFQ File.
• Select the Supplier that sent you the data from the Select Supplier drop down list.
• Click the Import button.
• Click the Update Suppliers button.  (After the Verification process completes click the OK button).
• Click the Close button.

Note: Once this process is complete the items on the list you imported will be visible on any and all item records in OneSource as the Item Number Default Supplier Number, Default Supplier Item Number, Default Supplier Cost, etc OR as linked Alternate Supplier Items on the Inventory Item Record.

 Using the Special Export tab