x

Start now

Schedule a demo

  • Date Format: MM slash DD slash YYYY
  • This field is for validation purposes and should be left unchanged.
Field Mapping–Inventory Item List – OneSource: Inventory for QuickBooks Online Field Mapping–Inventory Item List – OneSource: Inventory for QuickBooks Online

Have a Question?

Field Mapping–Inventory Item List

You are here:
< All Topics

The Inventory Item List is the place where ALL parts are stored–1 line for each item–whether you stock the item or not. 

This document explains each column in the @OSImport_InventoryItemList_Template.xls file.  This file explains how data needs to be prepared to import your list of inventory items—whether they are in stock or not.

 TO IMPORT YOUR INVENTORY STOCK LEVELS: At the time of the data conversion, it is anticipated that many of your existing inventory items will have 1 or more items in “stock”.  The actual stock levels, including the quantity in stock, the actual (or approximate) cost of the items, which warehouse the item is in, etc is stored in a separate table in OneSource—thus allowing 1 item to have multiple shipments and/or costs.  To properly prepare this type of data for import, please refer to the notes file and template entitled @OSImport_InventoryItemList_ExistingStockDetails_Template.xls

 TO IMPORT A MATRIX OF PRICES FOR YOUR INVENTORY ITEMS: If any of your inventory items include a price “matrix”, please refer to the notes file and template entitled @OSImport_InventoryItemList_PricingMatrix_Template.xls.

 TO IMPORT ADDITIONAL / ALTERNATIVE SUPPLIERS FOR YOUR INVENTORY ITEMS: If any of your items have more than 1 Supplier, please refer to the notes file and template entitled @OSImport_InventoryItemList_AlternateSuppliers_LayoutOption1_Template.xls OR @OSImport_InventoryItemList_AlternateSuppliers_LayoutOption2_Template.xls.

 TO IMPORT ITEM ASSEMBLY COMPONENTS FOR YOUR INVENTORY ITEMS: If any of your items have 1 or more Assembly (Kit) components, please refer to the notes file and template entitled @OSImport_InventoryItemList_AssemblyComponents_Template.xls

TO IMPORT ITEM SERIAL NUMBERS FOR YOUR INVENTORY ITEMS: If any of your items have 1 or more Assembly (Kit) components, please refer to the notes file and template entitled @OSImport_InventoryItemList_SerialNumbers_Template.xls

TO UPDATE (RE-IMPORT) YOUR INVENTORY LEVELS AND COSTS: If your inventory items and inventory levels are imported on a particular date and then need to be updated x days later to reflect the latest inventory levels and costs, you will need to supply a file with the item number, quantity in stock, cost, etc.  Please refer to the notes file and template entitled @OSImport_InventoryItemList_QtyAndCostUpdate_Template.xls

Field Mapping Spreadsheet

Column Name

OneSource Field Type Field Length or Numeric Range Sample Value Default Value

Additional Notes

Item Number

Text 50 GM1000830

*** REQUIRED *** The ITEM NUMBER is the “primary key” value.  Primary Key values must be UNIQUE (no duplicates) and cannot be blank.

Description

Memo 55,000+ Front bumper cover

Description of the Item

Class

Text 50 Stock Stock

*** SPECIFIC VALUES REQUIRED *** The default value here is typically “Stock”. In OneSource, if the item is a physicaltangible item that you sell (even if you do not stock the item, but typically just “Special Order” or “Drop Ship” the item) it would be still be classified as a “Stock” item.  Other Valid Classes are “Non-Stock”, “Service”, “Labor”, and “Rental”.

Category

Text 50 Chevrolet All of the Category fields are sub to Categorize and Sub Categorize your inventory items.

Sub Category

Text 50

Suburban

SubCategory1

Text 50

2002-2007

SubCategory2

Text 50

Bumper Cover

SubCategory3 Text 50 4545532
Bar Code / UPC Code Text 30 2343222345

This value is used to stock the UPC Barcode value.

Quantity In Stock

Number Single 20 0

(REQUIRED) Also known as the “Quantity on Hand”.  This should be equal to the total of all stock receipts (from PO’s) that have not been “shipped” on Sales Orders.  NOTE: This number will be set to 0 if no value is entered.

Minimum Stock Level

Number 15 5 0

If you intend to make use of Min/Max levels reports and tools, a value that represents the minimum stock level should be entered here.  For instance, if the “Quantity In Stock” was 10 and the “Minimum Stock Level” was 16 and the “Maximum Stock Level” was 32 the Automated PO #4 function would suggest a re-order quantity of 18 (in order to bring the Quantity in Stock back up to 32).  NOTE: Other factors are also considered in the calculation process such as case quantities, quantities on back-order, etc…) Actual Field Name: Reorder Point.

Maximum Stock Level

Number 15 30

See Notes Above. Actual Field Name: AdjustToQuantity

Unit Of Measure

Text 15

This is the unit of measurement, such as case, each, lb., etc

Weight

Number Double 89.5 0

(THIS MUST HAVE SOME TYPE OF VALUE – EVEN IF ONLY 0) – Weights of any specific items provided you set these weights for that item. Possibly used for shipping costs etc.

Weight Unit

Text 15 Lbs

Weight as to how the item is ordered for example if you order some unites in a case, this is the weight of that case.

Case Pack

Text 15 Ea.

This is the Case Pack quantity of the item. How many items are in a case.

Case Pack Unit of Measure

Text 15 Ea.

The Weight of the Case Pack, how much does it weigh.

Round up to Case Qty on PO Batch Create

Yes/No NA Yes/No

True/False

-1/0

No

When using the Automated Purchase Order system in OneSource, enter Yes if you would like OneSource to automatically round the Quantity being ordered to a multiple equal to one (or more) cases of the item.  (For instance, if you needed to buy 12 but the product came in multiples of 8 per case, then OneSource would round the Quantity to be ordered up to 16).

Convert to Case Qty on PO Batch Create

Yes/No NA Yes/No

True/False

-1/0

No

When using the Automated Purchase Order system in OneSource, enter Yes if you would like OneSource to automatically convert the quantity being suggested for re-order to the nearest “case” quantity. (For instance, if you needed to buy 12 but the product came in multiples of 8 per case—and were ordered and sold by the case (rather than each), then OneSource would round the Quantity to be ordered to 2 cases (rather than 16 individual pieces).

Active

Yes/No NA Yes/No

True/False

-1/0

Yes

(REQUIRED) Enter “Yes” to indicate if Item is Active.

Taxable

Yes/No NA Yes/No

True/False

-1/0

Yes

(REQUIRED) Enter “Yes” if the Item is taxable (if the Bill To Customer record is also Taxable).

Special Order

Yes/No NA Yes/No

True/False

-1/0

No

Enter Yes if you would like OneSource to automatically check the ‘SO’ checkbox on the Sales Order (line item), which will mark it for automated ‘Special Order’ using the Automated PO #2.

Multiple Warehouse

Yes/No NA Yes/No

True/False

-1/0

NO

(REQUIRED—if using multiple warehouses) Enter Yes if you would like OneSource to treat the item as a Multi-Warehouse item (which would require the user to select a particular warehouse to ship the item from when the item is entered on a Sales Order).  (NOTE: If you currently have any items that have more than one shipment in stock, stored in more than one warehouse, the stock details will need to be exported and imported as well.  Please see the following data conversion template/field mapping worksheet: @OSImport_InventoryItemList_ExistingStockDetails_Template.xls )

Lot Tracking

Yes/No

 

NA Yes/No

True/False

-1/0

No

(REQUIRED—if receiving, tracking, and selling items in LOTS) Enter Yes if you would like OneSource to treat the item as a Style/Color/Lot Tracking item (which would require the user to select a particular LOT # when placing the item on a Sales Order). (NOTE: If you currently have Items in stock that have a particular LOT# that will need to be imported, as there may be multiple shipments and/or LOT#’s, these will need to be exported and imported as well. Please see the following data conversion template/field mapping worksheet: @OSImport_InventoryItemList_ExistingStockDetails_Template.xls )

Serialized

Yes/No NA Yes/No

True/False

-1/0

NO

(REQUIRED—if receiving, tracking, and selling items with a unique Serial #) Enter Yes if you would like OneSource to treat the item as a Serialized item (which would require the user to select a particular Serial # when placing the item on a Sales Order).  (NOTE: If you currently have Items in stock that have individual Serial #’s these will need to be exported and imported as well.  Please see the following data conversion template/field mapping worksheet{: @OSImport_InventoryItemList_SerialNumbers_Template.xls )

Matrix Pricing

Yes/No NA Yes/No

True/False

-1/0

No

(REQUIRED—if you plan to have a pricing matrix for the item) Enter Yes if you would like OneSource to display a pricing matrix for the item (which can be used with or in place of Custom pricing templates).  (NOTE: If you currently have items that have a pricing matrix that you plan to use in OneSource these will need to be imported as well.  Please see the following data conversation template/field mapping worksheet:

@OSImport_InventoryItemList_PricingMatrix_Template.xls )

Assembly (Kit)

Yes/No NA Yes/No

True/False

-1/0

NO

(REQUIRED—if the item is an assembly of 2 or more other items in your list) Enter Yes if you would like OneSource to treat the item as an Assembled or Kitted item.  (NOTE: If you currently have Items in stock that have an assembly of more than one component (Item Number), these will need to be exported and imported into a separate list/table as well.  Please see the following data conversion template/field mapping worksheet: @OSImport_InventoryItemList_AssemblyComponents_Template.xls )Actual Field Name: Assembly

Force Pre Assembly

Yes/No NA Yes/No

True/False

-1/0

No

(REQUIRED—if the item is an assembly item AND if you want OneSource to NOT auto deplete inventory levels when the items Quantity in Stock falls below 0) Entering Yes here will prevent Sales Orders from auto depleting the (Assembly) item components stock levels in order to fulfill a shortfall in available inventory (when the item is entered and shipped on a Sales Order).

Editable Assembly

Yes/No NA Yes/No

True/False

-1/0

No

(REQUIRED—if the item as an assembly item AND if you want to be able to edit the assembly components directly from the Sales Order) This option works almost actually opposite of the Force Pre-Assembly option.  Entering Yes here will cause OneSource to auto deplete stock levels to fulfill stock being sold and shipped on a Sales Order—even if the item has a Stock level greater than 0).  This option also allows the user to add, change, or delete item components on the fly when entering the item on a Sales Order.

Upload to eCommerce

Yes/No NA Yes/No

True/False

-1/0

No

(REQUIRED—if you want the item to be utilized with an optional E-Commerce shopping cart)

Commissionable

Yes/No NA Yes/No

True/False

-1/0

No

(REQUIRED—if you want to the item to be included on Commission reports) This field maintains track of all Sales Orders and which Salespersons made the sale. Thus maintaining a running total for commissions to be paid to each Salesperson.

Picking Order

Number Long Integer 123

This field will create a Picking Order to all Inventory Items selected on a Sales Order allowing you to select which Item is picked up first to last.

Location

Text 50

This field is to maintain a record of where your Inventory Item is located in your warehouse

Bin

Text 50

This field will maintain a record of which Bin an Inventory Item is located within a Specific Location.

Last Inventory Date

Date General Date 07/06/07

This is the date that you last counted this inventory item.

List/Retail Price

Currency NA 45.00 0.00

(VERY IMPORTANT) This is the base price that will be inserted onto Quotations, Sales Orders, Invoices, and Credit Memo’s (unless overridden by the Default Pricing Method and/or different pricing established on the customer’s Custom Pricing tab).  NOTE: If your Default Pricing Method or Custom Pricing price makes use of a particular “Discount”, this Retail Price is used as the starting price to Discount.

Product Pricing

Group ID

Text 50 ABC123

(VERY IMPORTANT)  If you plan to use special Custom Pricing Templates (on the day you Go Live) you will need to manually create Product Pricing Groups (in OneSource) and then enter the Product Pricing Group ID in this column before the data is imported.

(For help with this topic, please see FAQ # U1116 entitled

What is a Product Pricing Group and how does it work?”)

Last Price Change Date

Date General Date 07/08/07

Text: This is the date to which prices were changed on a particular Item

Price Unit

Text 2 1

(optional) This field is only used on the MFR Rep Order Type if you sell

the item in units of 1 or 100 or 1000

Default Supplier Number

Text 50 ABC123 Item Number

(VERY IMPORTANT) In order for OneSource to be automatically linked to each items’ default Supplier (which is used in numerous places in OneSource to create Purchase Orders) the unique Supplier Number for the item (the number imported into OneSource or auto generated by OneSource when a Supplier record is created by hand). NOTE: If your list of Suppliers will be imported, you may be able to utilize the same number.  If not, you will need to enter, at the very least, each Supplier Name into OneSource and then make note of each newly created Supplier Number and enter the applicable Supplier Number in this column).

Default Supplier

Item Number

Text 50 ABC123

(VERY IMPORTANT)  This is your Default Supplier’s Unique ID for the Item.  This value can be auto inserted on the printed PO’s sent to your Supplier so they will be able to identify the items you are ordering from them.

Default Supplier Cost

Currency 50 55.25 0

(VERY IMPORTANT)  This is the cost value that will be auto entered the PO when the item is placed on a PO.  This value is also (usually) copied into the Inventory Products table (which is the table that stores all current inventory stock levels.  This cost value, multiplied by the Quantity in Stock, will help you determine your beginning inventory value for your Balance Sheet).

Default Supplier

Case Qty Cost

Currency NA 23.67 0

Text: the Cost to which your Default Supplier sells to your company by the case.

 Default Special Order Supplier Number

 Text  50  XYZ1234

 This is the Supplier Number for the Supplier that you’d like to use to auto create PO’s for items that you will ‘Special Order’ from local suppliers.

Default Special Order Supplier Item Number

 Text  50  123456789

 This is the Supplier Item Number that you will use to auto create PO’s for the items that you will ‘Special Order’ from local suppliers.

Default Special Order Supplier Item Cost

 Currency  NA  25.56  0

Default Supplier

Lead Time

Text 20 3 Days

This field displays the approximate lead time required for the item after a PO is placed with your Default Supplier. Actual Field Name: Lead Time.

Manufacturer

Text 50 Ford

This field displays the name of the Manufacturer of the item.  Unlike the Default Supplier Number (in column 40) this is simply a text value of the name of the Manufacturer.  (Optional)

Mfr Model Number

Text 50

This is the model number assigned to the item by the manufacturer of the item (which may be the same as the Default Supplier Item Number or may be different).

Comments

Memo 55,000

This field is used to store virtually unlimited comments or notes about the item.

Inventory GL Account

Numbers Long Integer Company Setup Inventory GL Account

(REQUIRED) In order for the proper link to be created between the GL Inventory asset account and this item (when the item is placed on a Sales Order), you will need to determine and enter the unique GL Account ID assigned by OneSource for your Inventory Asset account. (For help with this topic, please see

Sales GL Account

Numbers Long Integer Company Setup Sales Account

(REQUIRED) Same issue as above.

Sales Discount GL Account

Numbers Long Integer Company Setup Sales Discount GL Account

(REQUIRED) Same issue as above.

COGS Account

Numbers Long Integer Company Setup COGS GL Account

(REQUIRED) Same issue as above.

 

 

 

 

Previous Field Mapping–Inventory Item Import – 5a – Inventory Search Catalog
Next Field Mapping–Open AP Aging
Was this article helpful?
0 out of 5 stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
How can we improve this article?
Table of Contents