|
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 physical, tangible 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. Possible 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 where 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 short fall 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 record of where your Inventory Item is located in your warehouse
|
|
Bin
|
Text
|
50
|
|
|
This field will maintain 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 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 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.
|
