ITrack/Enterprise/Imports

This page describes the structure and content of the files that ITrack Enterprise can accept as input for importing data into the system. Some of these files are optional and can be omitted. Each import takes the form of a spreadsheet (.csv file) that must be filled out in a particular format before it can be imported. Each import's purpose is described below, along with a list of all columns in the spreadsheet, along with their data types, meaning, and requirements.

Field Data types

In the tables below, each field will have a data type. Import rows must have a value that matches this data type (or be blank if the field is not required). Below we describe each of the data types.

• integer: a numeric value
• decimal: a fractional numeric value, such as a currency value. Format should omit any currency symbols. Example: 519.23
• string: any text is allowed, up to the specified length in the restriction column
• choice: specific text options are allowed, which are specified in the restriction column
• boolean: either True or False
• date: a date in ISO standard (YYYY-MM-DD). Example: 2018-12-30

Spreadsheet Downloads

In each import file section, several downloads are available. All import files are .csv files (comma separated values). These can be edited in Excel, google spreadsheets, and exported from most database systems. Each section should have an empty import file, allowing for someone to manually submit data. In some imports, an example csv is specified with test data. Test data is not ensured to make sense for all domains, and please disregard the arbitrary nature of some of the test data.

Import File Descriptions

Below, find descriptions of each type of import file. Each table has detailed field descriptions, a .csv file download link (a spreadsheet that can be filled out with import data), and a MySQL query for internal use that documents generating import files from ITrack Enterprise schema.

Inventory Types

Inventory types represent the full list of types of inventory that can be entered in the system. A valid inventory type is required for every part, vehicle, or other inventory unit. However, this import is optional, as inventory types will be created automatically as part of the inventory or vehicle import, as needed.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
inventory_type_name	string	50	Yes	A unique name describing the inventory type	Crankshaft
assembly_type_name	string	50	No	A 'parent' inventory type. This is optional, but if it is filled out, it represents that this inventory type comes from an assembly of the specified part type	Engine Assembly
inventory_type_label_1	string	20	No	An extra question/option for all inventory records of this type. This is one of 4 field labels that can be specified as part of the inventory import.	Horsepower
inventory_type_label_2	string	20	No	An extra question/option for all inventory records of this type. This is one of 4 field labels that can be specified as part of the inventory import.	Jake brakes
inventory_type_label_3	string	20	No	An extra question/option for all inventory records of this type. This is one of 4 field labels that can be specified as part of the inventory import.	CPL/ARR#
inventory_type_label_4	string	20	No	An extra question/option for all inventory records of this type. This is one of 4 field labels that can be specified as part of the inventory import.	Oil Pressure
vehicle_inventory_type	boolean		Yes	If this is true, this inventory type applies to vehicles, otherwise it applies to part inventory.	False

Downloads

Inventory types spreadsheet (empty)

Inventory types spreadsheet (w/ examples)

Queries

# Inventory types (optional)
SELECT inventorytype.name AS inventory_type_name, parenttype.name AS assembly_type_name, inventorytype.typelabel1 AS inventory_type_label_1, inventorytype.typelabel2 AS inventory_type_label_2, inventorytype.typelabel3 AS inventory_type_label_3, inventorytype.typelabel4 AS inventory_type_label_4, inventorytype.vehicleunit AS vehicle_inventory_type FROM inventorytype LEFT JOIN inventorytype AS parenttype ON parenttype.inventorytypeid = inventorytype.typesetid AND inventorytype.inventorytypeid != parenttype.inventorytypeid;

Inventory Questions

Inventory questions represent additional attributes that can be filled out on every inventory record. Each inventory type can have any number of additional questions.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
inventory_type_name	string	50	Yes	A unique name describing the inventory type (see the inventory type import)	Transmission/Transaxle Assembly
question_text	string	50	Yes	The text of an extra attribute for all inventory records of this type.	Gear ratio

Downloads

Inventory questions spreadsheet (empty)

Inventory questions spreadsheet (w/ examples)

Queries

# Inventory Q&A
SELECT inventorytype.name AS inventory_type_name, `option` AS question_text FROM inventoryoption JOIN inventorytype USING (inventorytypeid) WHERE inventorytypeid = 4350;

Part Manufacturers

This is an optional import that allows the user to specify a list of valid manufacturers for all parts inventory. Manufacturers are not inventory type specific, but part models are. If this import is omitted, manufacturers are created automatically as inventory is imported.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
manufacturer_name	string	50	Yes	A unique name of the manufacturer	Allison
short_name	string	10	Yes	A unique code/short name for this manufacturer, sometimes used as part of a default sku_identifier in inventory. If the importer doesn't care about this feature, they can simply assign a unique number or the first couple characters of the manufacturer name (such as the first 3 characters).	ALN
default_accounting_category	string	50	No	The name of a GL category to assign (by default) to any inventory records created with this manufacturer.	Used Inventory

Downloads

Manufacturer spreadsheet (empty)

Manufacturer spreadsheet (w/ examples)

Queries

# Manufacturers (optional)
SELECT manufacturer.name AS manufacturer_name, manufacturer.code AS short_name, glcategory.name AS default_accounting_category FROM manufacturer LEFT JOIN glcategory USING (glcategoryid) WHERE manufacturer.name IN ('ALLISON', 'CHEVROLET');

Part Models

This is an optional import that allows the user to specify a list of valid models for all parts inventory. Each part model is specific to an inventory type and a part manufacturer; they will be created if they don't exist. If this import is omitted, models will be created as inventory is imported.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
manufacturer_name	string	50	Yes	The name of a manufacturer that this model is produced by	Allison
inventory_type_name	string	50	Yes	The name of the inventory type that this model represents	Transmission/Transaxle Assembly
model_name	string	50	Yes	The name of a part model. It must be unique per inventory type/manufacturer, ignoring any special characters (dash, slash, space, period, hyphen, etc).	AT540
first_year	integer		No	The first year the model will apply to	2000
last_year	integer		No	The last year the model will apply to	2018
weight	decimal		No	Default shipping weight of inventory of this model	250.0
weight_unit	choice	'mg','g','kg','N','oz','lb','ton'	No	The unit of the weight	lb
default_category	string	50	No	An inventory category to be assigned (by default) to newly created inventory records with this model.	Used

Downloads

Manufacturer spreadsheet (empty)

Manufacturer spreadsheet (w/examples)

Queries

# Models (optional)
SELECT manufacturer.name AS manufacturer_name, inventorytype.name AS inventory_type_name, model.name, firstyear AS first_year, lastyear AS last_year, weight, weightunit AS weight_unit, defaultcategory AS default_category FROM model LEFT JOIN manufacturer USING (manufacturerid) LEFT JOIN inventorytype USING (inventorytypeid) WHERE manufacturer.name IN ('ALLISON', 'CHEVROLET') AND inventorytypeid != 9990;

Vehicle Makes/Models

This is an optional import that allows the user to specify a list of valid makes and models for all vehicle inventory. If this import is omitted, vehicle models will automatically be created based on the vehicle import.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
make	string	50	Yes	The name of a vehicle make	PETERBILT
vehicle_model	string	50	Yes	The name of the vehicle model	567
first_year	integer		No	The first year the model was produced	2000
last_year	integer		No	The last year the model was produced	2010

Downloads

Models spreadsheet (empty)

Models spreadsheet (w/examples)

Queries

# Vehicle Models (optional)
SELECT make AS make_name, vehiclemodel.model AS model_name, IF(firstyear = 0, '', firstyear) AS first_year, IF(lastyear = 9999, '', lastyear) AS last_year FROM vehiclemodel WHERE make = 'International';

Vehicles

This import is required to bring any vehicles into the system. Each vehicle record should have its total vehicle cost set equal to the total expenses associated with that vehicle and any of its constituent inventory records. The total vehicle CoGS (cost of goods sold) should be the sum of all costs that have been sold out of the system previously (such as previous component part sales). The remaining cost (total vehicle cost minus total CoGS) can be assigned to the 'cost' field of any associated inventory records that are available in inventory. Any cost still remaining will be associated with the vehicle record itself, and will either be consumed as remaining inventory is sold (based on a percentage of sale price) or when the vehicle itself is sold or scrapped. For more information about how vehicle costing works in ITrack Enterprise, please contact ISoft support.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
vehicle_stock_number	string	25	Yes	A unique name that identifies this vehicle in the system	PETERBILT
vehicle_type_name	string	50	Yes	The name of an inventory type for this vehicle. This is often used to distinguish vehicles for sale from vehicles for disassembly.	Parts Trucks
vin	string	20	No	A vehicle identification number	1FAFP53U24G129846
make	string	50	Yes	The make of the vehicle	PETERBILT
vehicle_model	string	50	Yes	The model of the vehicle	567
year	integer		Yes	The year the vehicle was produced	567
mileage	integer		No	The mileage of the vehicle (currently or at the last time of service)	16000
body_style	string	25	No	Information about the style of the vehicle body	DAY CAB
external_color	string	25	No	Description of the outside color of the vehicle	BLUE
external_color_code	string	25	No	A paint color code for the outside color of the vehicle
internal_color	string	25	No	Description of the inside color of the vehicle	BEIGE
internal_color_code	string	25	No	A paint color code for the inside color of the vehicle
location	string	50	Yes	The current location of the vehicle at the yard	Y1-R3-B6
category	string	50	Yes	The inventory category of the vehicle	Flatbed Trucks
description	string		No	A long form description of the vehicle. Any contents of this field are public and will be displayed in online listings and on customer-facing documents.
notes	string		No	A long form set of notes about the vehicle. Any contents of this field are internal and will not be displayed in public places.
status	choice	A,H,S,C,D,B	Yes	A short status of the vehicle. Descriptions follow: A - Available: default for a vehicle that is in stock for sale or if any cost remains S - Sold: the vehicle has been sold on a document or written down on an internal document, consuming any remaining cost. C - Crushed: the vehicle has been marked as crushed, and is no longer available. Any remaining inventory constitute all remaining vehicle cost distributions. D - Deleted: the vehicle was a mistake and has been marked as deleted. A vehicle in this state should not have any remaining cost. B - Bid: a vehicle that has been bid on but has not yet been won. Vehicles of this type do not show up in normal searches.	A
title_number	string	25	No	A document number for the vehicle title
title_status	string	20	No	The current status of the vehicle title
title_type	string		No
title_received	string		No	A long form set of notes about the vehicle. Any contents of this field are internal and will not be displayed in public places.
title_state	string	2	No	The abbreviation of the state the vehicle title is in.
dismantled	boolean		No	Whether the vehicle has been dismantled into component parts or not.	True
purchased_from	string	50	No	A short description of the company or individual that the vehicle was purchased from
purchase_date	date		No		2018-05-16
seller_contact	string	25	No	The name of a contact at the selling company or individual that sold the vehicle
seller_phone	string	25	No	The phone # of the contact at the selling company
seller_info	string		No	Additional notes about about the vehicle seller or its purchase
claim_number	string	25	No
adjuster	string	50	No
file_number	string	50	No
junking_number	string	25	No
vehicle_accounting_category	string	50	Yes	The GL accounting category associated with the vehicle unit itself. This can affect the accounts hit when the vehicle is purchased, sold, or crushed.	Vehicles For Sale
default_inventory_accounting_category	string	50	No	The GL accounting category to be used by default on any new inventory associated with the vehicle.	Used Parts
total_vehicle_cost	decimal		Yes	The total expense associated with this vehicle that has been capitalized into cost. This should include any cost that has been consumed on sales into CoGS or allocated onto parts inventory. Essentially, total vehicle cost should be equal to total CoGS plus total cost allocated to remaining available inventory.	1450.50
total_vehicle_cogs	decimal		Yes	The total amount of vehicle cost that has been consumed on documents, including any component inventory that has been sold. If the vehicle has been sold, crushed as scrap, etc, this should be the entire vehicle cost that has not been manually allocated on remaining inventory.	500.50

Downloads

Vehicles spreadsheet (empty)

Vehicles spreadsheet (w/examples)

Queries

# Vehicles
SELECT vehicle.storeid AS store_id, stocknumber AS vehicle_stock_number, inventorytype.name AS vehicle_type_name, vehicle.vin, vehicle.make, vehicle.model AS vehicle_model, vehicle.year, mileage, vehicle.bodystyle AS body_style, externalcolor AS external_color, externalcolorcode AS external_color_code, internalcolor AS internal_color, internalcolorcode AS internal_color_code, vehicle.location, vehicle.category, vehicle.description, vehicle.notes, vehicle.status, titlenumber AS title_number, titlestatus AS title_status, titletype AS title_type, titlereceived AS title_received, titlestate AS title_state, dismantled, purchasedfrom AS purchased_from, purchasedate AS purchase_date, sellercontact AS seller_contact, sellerphone AS seller_phone, sellerinfo AS seller_info, pickuplocation AS pickup_location, claimnumber AS claim_number, adjuster, filenumber As file_number, junkingnumber AS junking_number, glcategory.name AS vehicle_accounting_category, inventoryglcategory.name AS default_inventory_accounting_category, f_vehicle_cost(vehicle.vehicleid) AS total_vehicle_cost, f_vehicle_cost_consumed(vehicle.vehicleid) AS total_vehicle_cogs
FROM vehicle LEFt JOIN glcategory ON (glcategory.`glcategoryid` = vehicle.`glcategoryid`) LEFT JOIN glcategory AS inventoryglcategory ON (inventoryglcategoryid = inventoryglcategory.glcategoryid)
JOIN v_vehiclewholeunit ON (v_vehiclewholeunit.vehicleid = vehicle.vehicleid)
JOIN inventory ON (inventory.inventoryid = v_vehiclewholeunit.`inventoryid` AND inventory.storeid = v_vehiclewholeunit.storeid)
JOIN inventorytype ON (inventory.`inventorytypeid` = inventorytype.`inventorytypeid`);

Vehicle Answers (Additional Vehicle Attributes)

This table represents optional 'answers' for the 'questions' imported earlier. Each vehicle can have up to one answer for each question that is registered for the vehicle inventory type specified as part of the vehicle import. A vehicle can omit answers for any questions that do not apply to that particular vehicle.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
vehicle_stock_number	string	25	Yes	A unique name that identifies this vehicle in the system	PETERBILT
question_text	string	50	Yes	The text of the question as previously imported in the inventory question import type. If it is not found, it will be added to the list of registered questions.	Mileage
answer_text	string		Yes	The text of the answer for this specific vehicle. This field has an unlimited length	25750

Downloads

Vehicle answers spreadsheet (empty)

Vehicle answers spreadsheet (w/examples)

Queries

# Vehicle Q&A
SELECT vehicle.stocknumber AS vehicle_stock_number, inventoryoption.option AS question_text , inventoryoptionvalue.value AS answer_text
FROM vehicle JOIN v_vehiclewholeunit ON (v_vehiclewholeunit.vehicleid = vehicle.vehicleid)
JOIN inventory ON (inventory.inventoryid = v_vehiclewholeunit.`inventoryid` AND inventory.storeid = v_vehiclewholeunit.storeid)
JOIN inventoryoption ON inventoryoption.`inventorytypeid` = inventory.inventorytypeid
JOIN inventoryoptionvalue ON (inventoryoptionvalue.inventoryid = inventory.inventoryid AND inventoryoptionvalue.`inventoryoptionid` = inventoryoption.inventoryoptionid);

Inventory

The inventory file allows someone to bring in used and new inventory records. If they are available, the (optional) inventory type, manufacturer, model, and vehicle model imports should be run first. The inventory import will create entries in the inventory type, manufacturer, and model tables for any rows that don't match anything previously imported.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
store_id	integer		Yes	The store number this part exists at. If the part is stocked at multiple stores, a separate row should be created for each.	1
sku_identifier	string	50	Yes	The 'name' of this item. This name/id is used as the internal part number in the system, as well as customer-facing documents and tag printouts. It is preferable that this value be unique per store.	ALN AT540-CORE
vehicle_stocknumber	string	50	No	The stock # of the associated vehicle record (if any). If this vehicle is not found, it will not be created as part of the import. Therefore anything in this field should have already been created as part of the vehicle import, or the part will not be associated with a vehicle and its corresponding profitability and cost pool.	TESTVEHICLE
description	string		No	A long-form description of the inventory record. Note: the contents of this field are public in internet listings and on customer-facing documents.	Rebuilt transmission, certified rebuild.
notes	string		No	A long-form internal description/notes for the inventory record. This information is internal to the system and will not be displayed in public contexts.	Marks along the side, consider repainting
accounting_category	string	50	Yes	The name of a general ledger accounting category that controls the accounting this part affects. All inventory with the same accounting category will hit the same sales/inventory/CoGS/etc accounts for the same context (sale, rebuild, purchase, etc). This field can be left empty if accounting will be disabled in the system.	Used Parts
status	choice	A,H,S,C,D,B	Yes	A short status of the vehicle. Descriptions follow: A - Available: default for a vehicle that is in stock for sale or if any cost remains S - Sold: the vehicle has been sold on a document or written down on an internal document, consuming any remaining cost. C - Crushed: the vehicle has been marked as crushed, and is no longer available. Any remaining inventory constitute all remaining vehicle cost distributions. D - Deleted: the vehicle was a mistake and has been marked as deleted. A vehicle in this state should not have any remaining cost. B - Bid: a vehicle that has been bid on but has not yet been won. Vehicles of this type do not show up in normal searches.	A
quantity_on_hand	decimal		Yes	The amount of stock that is currently on hand. This includes inventory that is on hold but has not yet been sold or consumed.	10.000000
inventory_type_name	string	100	Yes	Name of the inventory type for this SKU. If the user does not wish to use specific inventory types for replenishable (aftermarket) inventory, use a blanket type like "Miscellaneous Parts"	Miscellaneous Parts
category	string	50	No	What inventory category this SKU belongs to. This can be used to categorize the item on documents and internet listings.	USED
condition	string	15	No	A short description of the condition of the item.	GOOD RUNNER
oem_number	string	30	No	A number from the original equipment manufacturer which can be used to lookup the item. If there are several numbers/ids that can be used to find this item, use the inventory_lookup import to add more UPCs or alternate part lookups of other types.
manufacturer	string	50	No	The name of company that produces this item (not necessarily the company you purchase it from). For some part types, items are generally known by their application, not their manufacturer. In those cases, leave this blank and simply fill out the assembly_manufacturer instead. For major assemblies (engine/transmission/etc) where items are known by the OEM, this will often be the same as the assembly_manufacturer.	Allison
model	string	50	No	The model # that identifies this item to the company listed in the manufacturer above. For some part types, items are generally known by their application, not their manufacturer. In those cases, leave this blank and simply fill out the assembly_model instead. For major assemblies (engine/transmission/etc) where items are known by the OEM, this will often be the same as the assembly_model.	AT540
assembly_manufacturer	string	50	No	This is the manufacturer that is used to search this item by what it applies to/fits in. This is generally the manufacturer a part is 'known by'. For major assemblies (engine/transmission), this is generally the same as the actual manufacturer. For assembly components, it will generally be the manufacturer of the assembly this part fits. So a gasket kit may be known by the manufacturer of the engine it fits.	Allison
assembly_model	string	50	No	This is the modelthat is used to search this item by what it applies to/fits in. This is generally the model a part is 'known by'. For major assemblies (engine/transmission), this is generally the same as the actual model. For assembly components, it will generally be the model of the assembly this part fits. So a gasket kit may be known by the model of the engine it fits.	AT540
inventory_type_data_1	string	50	No	Data that corresponds to the first label on this SKU's part type (see inventory_type_label_1 in the inventory_type import)	230HP
inventory_type_data_2	string	50	No	Data that corresponds to the first label on this SKU's part type (see inventory_type_label_2 in the inventory_type import)	Yes
inventory_type_data_3	string	50	No	Data that corresponds to the first label on this SKU's part type (see inventory_type_label_3 in the inventory_type import)	123456
inventory_type_data_4	string	50	No	Data that corresponds to the first label on this SKU's part type (see inventory_type_label_4 in the inventory_type import)	40PSI
deplete	boolean		Yes	Whether this item can be depleted. This defaults to True. If set to False, the quantity_on_hand, average_cost, and average_core_cost of the item will be ignored and selling or consuming the item will not affect the item. Generally this is only False for SKUs that represent fees or items with an arbitrary stock.	True
public	boolean		Yes	Whether this item should be shown on any website or online integrations.	True
taxable	boolean		Yes	Whether this item should (by default) be marked as taxable on any sales documents.	True
serialized	boolean		Yes	Set to True if this item tracks a serial # or other individual # for every item quantity_on_hand. If serialized is True, make sure to specify the serial #'s as part of the import into inventory_locations.	False
average_cost	decimal		Yes	This should represent a capitalized cost 'per quantity' of the items in inventory. After import, average_cost is tracked based on expenses in the system (POs, WOs, etc) and cannot be edited directly by the user.	325.50
list_price	decimal		No	The list price of the item. Used in some online listings as well as customers who pay some markup of list price on inventory.	2250.50
retail_price	decimal		No	The retail price of the item. Used in some online listings as well as the default price customers pay.	2250.50
retail_core_charge	decimal		No	The retail core charge on this item. This only applies if this item has core charges on customer documents. Used in some online listings as well as the default core charge customers pay.	250.00
wholesale_price	decimal		No	The wholesale price of the item. Generally a lower charge than the retail price. This will be used for customers who pay a markup of wholesale.	1800.00
wholesale_core_charge	decimal		No	The wholesale core charge for the item. Generally lower than the retail core charge. This will be used for customers who pay a markup of wholesale. This only applies if this item has core charges on customer documents.	200.00
jobber_price	decimal		No	Generally a lower charge than the wholesale price. This will be used for customers who pay a markup of jobber.	1700.00
jobber_core_charge	decimal		No	Generally lower than the wholesale core charge. This will be used for customers who pay a markup of jobber. This only applies if this item has core charges on customer documents.	185.00
distributor_price	decimal		No	Generally a lower charge than the jobber price. This will be used for customers who pay a markup of distributor.	1700.00
distributor_core_charge	decimal		No	Generally lower than the jobber core charge. This will be used for customers who pay a markup of distributor. This only applies if this item has core charges on customer documents.	185.00
interchange_group	string	20	No	A value that represents an interchangeable 'group' of items. All items that are at least partially interchangeable for a particular year/make/model or year/manufacturer/model application should have the same value entered.	1101
sub_interchange_group	string	5	No	An optional value that represents a sub-group of the specified interchange_group. If two items with the same interchange_group have a different sub_interchange_group, that means the items are partially interchangeable. Generally this means the item must be somehow modified to apply.	B
weight	decimal		No	The average shipping weight of an example of this SKU.	375.000000
shipping_width	decimal		No	The average width of the item when shipped.	36.00
shipping_length	decimal		No	The average length of the item when shipped.	42.00
shipping_height	decimal		No	The average height of the item when shipped.	30.00
replenishable	boolean		Yes	Whether this SKU represents an item that is replenishable. Such items will keep a status of Available if quantity drops to 0. Non-replenishable items will gain a status of Sold if they reach 0 quantity_on_hand.	True
minimum_stock_level	decimal		No	(replenishable inventory only) if the quantity drops below this value, the system will suggest an order to bring stock back up to maximum_stock_level.	5.000000
maximum_stock_level	decimal		No	(replenishable inventory only) if the quantity drops below minimum_stock_level value, the system will suggest an order to bring stock back up to this quantity.	20.000000
default_purchase_vendor	string	50	No	(replenishable inventory only) the company name of the vendor that stock is ordered from by default.	All American Truck Parts
vendor_part_number	string	40	No	(replenishable inventory only) this is the identifier used on purchase orders that identifies purchases of this item to the default purchase vendor.	AA AT540
core_class	string	10	No	(replenishable inventory only) parts that share a core_class are interchangeable when being returned to the purchase vendor in exchange for a core charge.
unit_of_measurement	string	10	No	(replenishable inventory only) denotes what 1 quantity of this item represents (foot, EA, inch, etc)	EA
vendor_product_code	string	50	No	(replenishable inventory only)
popularity_code	string	10	No	(replenishable inventory only) the popularity class of this item. For example: A/B/C popularity class for items that are fast or slow moving.	A
vendor_popularity_code	string	10	No	(replenishable inventory only) the popularity class of this item for the default purchase vendor.
seasonal	boolean		No	(replenishable inventory only) whether this item's usage pattern follows a yearly seasonal pattern.	False
buy_package	integer		No	(replenishable inventory only) this represents (in terms of the company's stock quantity) that the vendor sells in groups of. For example, if the user stocks inches of tubing, but the vendor sells in 5 foot increments, buy_package would be 60.
sell_package	integer		No	(replenishable inventory only) this represents (in terms of the company's stock quantity) that the company sells in groups of. For example, if the user stocks inches of tubing, but sells in 1 foot increments, sell_package would be 12.
vendor_sells_in_packs	boolean		No	(replenishable inventory only) if True, vendor facing documents will represent purchase quantity in terms of the buy_package. For example, if the user stocks inches of tubing, and the vendor sells in 5 foot increments that it considers "1 spool", a purchase order printout of 120 inches would show an order of 2 (spools).	False
stocking_type	choice	Special Order, Stocked, Obsolete	No	(replenishable inventory only) dictates whether the item is actively stocked. Items that are Special Order will be part of suggested orders if they are on any hold orders. Obsolete items will not show up to be ordered.	Special Order
stocking_method	choice	None, Min/Max, History	No	(replenishable inventory only) the stocking methodology. Min/Max means order up to max when quantity is below min. History means use historical ordering fields to decide how many to order.	Min/Max
stocking_days	integer		No	(replenishable inventory only) the average number of days between ordering this item and when it is received. This will be used as part of historical ordering suggestions.	7
safety_stock_percent	decimal		No	(replenishable inventory only) As part of historical ordering suggestions, this is an extra % of stocking levels to order as a 'safety net'	10.00
purchase_cost	decimal		No	For replenishable inventory: this is the expense required to purchase a single unit of the SKU from the default purchase vendor. For parts off of a vehicle: this is a statically allocated section of the vehicle's cost pool to allocate to this part. For all other parts: a general cost valuation of the item. This will be used for customers who pay a markup of cost.	1400.00
core_purchase_cost	decimal		No	(replenishable inventory only) this represents a value that the default purchase vendor will charge in the form of a 'core charge' when ordered.	100.00

Downloads

Inventory spreadsheet (empty)

Inventory spreadsheet (w/examples)

Queries

# inventory
SELECT inventory.storeid AS store_id, tagnumber AS sku_identifier, vehicle.stocknumber AS vehicle_stock_number, inventory.description, inventory.notes, glcategory.name AS accounting_category, inventory.status, quantity AS quantity_on_hand,
inventorytype.name AS inventory_type_name, inventory.category, `condition`, side, oemnumber AS oem_number, manufacturer.name AS manufacturer, model.name AS model,
parentmanufacturer.name AS assembly_manufacturer, parentmodel.name AS assembly_model,
typedata1 AS inventory_type_data_1, typedata2 AS inventory_type_data_2, typedata3 AS inventory_type_data_3, typedata4 AS inventory_type_data_4,

deplete, public, taxable, serialized, averagecost AS average_cost, listprice AS list_price, retailprice AS retail_price, retailcore AS retail_core_charge, wholesaleprice AS wholesale_price, wholesalecore AS wholesale_core_charge, jobberprice AS jobber_price, jobbercore AS jobber_core_charge, distributorprice AS distributor_price, distributorcore AS distributor_core_charge,

interchangenumber AS interchange_group, subinterchangenumber AS sub_interchange_group, inventory.weight, shippingwidth AS shipping_width, shippinglength AS shipping_length, shippingheight AS shipping_height,

replenish AS replenishable, 
minquantity AS minimum_stock_level, maxquantity AS maximum_stock_level, vendor.company AS default_purchase_vendor, partnumber AS vendor_part_number, coreclass as core_class, unitofmeasurement as unit_of_measurement, vendorproductcode AS vendor_product_code, popularitycode AS popularity_code, vendorpopularitycode AS vendor_popularity_code, seasonal, buypackage AS buy_package, sellpackage AS sell_package, purchasefactor AS vendor_sells_in_packs, stocktype AS stocking_type, stockmethod AS stocking_method, stockingdays AS stocking_days, safetystockpercent AS safety_stock_percent, cost as purchase_cost, corecost AS core_purchase_cost

FROM inventory JOIN inventorytype USING (inventorytypeid) LEFT JOIN manufacturer ON (manufacturer.`manufacturerid` = inventory.`manufacturerid`) LEFT JOIN model ON (model.`modelid` = inventory.modelid) LEFT JOIN manufacturer AS parentmanufacturer ON (parentmanufacturer.manufacturerid = parentmanufacturerid) LEFT JOIN model AS parentmodel ON (parentmodel.modelid = parentmodelid) LEFT JOIN vehicle ON (vehicle.vehicleid = inventory.vehicleid) LEfT JOIN glcategory ON (inventory.glcategoryid = glcategory.`glcategoryid`) LEFT JOIN vendor ON (vendor.vendorid = inventory.vendorid)
LIMIT 10;

Inventory Lookups

Importing inventory lookups allows the user to specify alternate numbers or identifiers that should link to specified items. Lookups can be UPCs (used for scanning/entering packaging), alternate inventory record (used when one SKU replaces another), alternate part # (any kind of string that can be used to find the item in search), Supercession (an alternate vendor part # that can be used to find this part; used when a vendor or manufacturer changes identifiers). This import is optional.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
sku_identifier	string	50	Yes	The 'name' of an item in inventory. This lookup record applies to that item.	ALN AT540
sku_lookup	string	30	Yes	Depending on the relationship_type, this represents the value that links to the record.	ALN AT540-B
other_sku_identifier	string	50	No	(Optional) The sku_identifier of another inventory record. If the relationship_type is Alternate Inventory Record, this will be the alternate SKU record that is being linked to. In other cases, it should be omitted.	ALN AT545
package_quantity	integer		Yes	A quantity multiplier to be applied when scanning a barcode or entering this lookup in the mobile warehouse management software. Useful in situations where certain vendor barcodes on packaging represent a package of multiple quantity. By default, this will generally be 1.	1
relationship_type	choice	UPC, Alternate Inventory Record, Alternate Part #, Supercession	Yes	Represents the nature of this record to its linked item/SKU.	UPC
comment	string		No	A long-form user description, explaining the relationship	This record replaced the old item when the vendor changed part #'s back in 2016

Downloads

Inventory lookups spreadsheet (empty)

Inventory lookups spreadsheet (w/examples)

Queries

# inventory UPCs/lookups
SELECT inventory.tagnumber AS sku_identifier, lookup AS sku_lookup, originalinventory.tagnumber AS other_sku_identifier, packagequantity AS package_quantity, relationshiptype AS relationship_type, `comment` FROM inventorylookup JOIN inventory USING (inventoryid) LEFT JOIN inventory AS originalinventory ON (originalinventory.inventoryid = originalinventoryid) GROUP BY inventorylookupid;

Inventory Locations

This import will specify where the items that were imported as part of the inventory import are located at the facility. There should be records enough to account for the sum total of each inventory record's quantity_on_hand. If the rows imported for a particular SKU do not add up to the quantity on hand that was imported, the remaining difference will be put at a location of Variance and marked as lost for the next count.

For serialized items, there should be a row for every available SKU associated with the item, along with the location the item with that serial # is located at. Serialized rows should always have a quantity of 1.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
store_id	integer		Yes	The store number of the inventory record that this location corresponds to.	1
sku_identifier	string	50	Yes	The 'name' of the inventory item this applies to.	ALN AT540-CORE
location	string	50	Yes	The location the item is located at. This location will be created as part of import if it does not exist at the given store.	W1-R1-L2-B6
serialnumber	string	25	No	The unique serial #/item # that represents a single quantity of the associated inventory record. If the associated SKU is serialized, a number of rows should exist with a valid serialnumber value equal to the quantity_on_hand of that record.	1-439874
quantity	decimal		Yes	The quantity of the associated item located at the given location. For serialized records, this should be 1.	1

Downloads

Inventory locations spreadsheet (empty)

Inventory locations spreadsheet (w/examples)

Queries

# inventory serials/locations
SELECT inventorystoreid AS store_id, inventory.tagnumber AS sku_identifier, inventorylocation.location, '' AS serialnumber, inventorylocation.quantity FROM inventorylocation LEFT JOIN inventory ON (inventory.inventoryid = inventorylocation.inventoryid AND inventory.storeid = inventorystoreid) WHERE serialized = 'False'
UNION ALL
SELECT inventorystoreid AS store_id, inventory.tagnumber AS sku_identifier, inventoryserial.location, inventoryserial.`serialnumber`, 1 FROM inventoryserial JOIN inventory ON (inventory.inventoryid = inventoryserial.inventoryid AND inventory.storeid = inventorystoreid) WHERE serialized = 'True';

Inventory Answers (Additional Attributes)

This table represents optional 'answers' for the 'questions' imported earlier. Each inventory record can have up to one answer for each question that is registered for the inventory type specified as part of the inventory import. An inventory record can omit answers for any questions that do not apply to that particular inventory item.

Fields

Field name	Type	Restriction	Required	Explanation	Example Value
sku_identifier	string	25	Yes	A unique name that identifies this vehicle in the system	ALN AT540
question_text	string	50	Yes	The text of the question as previously imported in the inventory question import type. If it is not found, it will be added to the list of registered questions.	Mileage
answer_text	string		Yes	The text of the answer for this specific vehicle. This field has an unlimited length	25750

Downloads

Inventory answers spreadsheet (empty)

Inventory answers spreadsheet (w/examples)

Queries

# inventory Q&A
SELECT inventory.tagnumber AS sku_identifier, inventoryoption.option AS question_text, inventoryoptionvalue.value AS answer_text
FROM inventory
JOIN inventoryoption ON inventoryoption.`inventorytypeid` = inventory.inventorytypeid
JOIN inventoryoptionvalue ON (inventoryoptionvalue.inventoryid = inventory.inventoryid ANd inventoryoptionvalue.`inventoryoptionid` = inventoryoption.inventoryoptionid)
WHERE inventoryoptionvalue.value != ''
GROUP BY inventory.inventoryid;