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

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

File:Inventory types.csv

Download inventory types example 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.

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

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

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.

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

# 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);

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.

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

# Models (optional)
SELECT manufacturer.name AS manufacturer_name, inventorytype.name AS inventory_type_name, model.name AS 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);

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.

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

# Vehicle Models (optional)
SELECT make AS make_name, vehiclemodel.model AS model_name, firstyear AS first_year, lastyear AS last_year FROM vehiclemodel;

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.

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

# 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 Attributes)

# 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 those tables for any rows found that don't match anything previously imported.

# 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

# 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

# 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)

# 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;