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
• 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

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.

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

# 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

# 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

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

Vehicle Makes/Models

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

Vehicles

# Vehicles
SELECT vehicle.storeid AS store_id, stocknumber AS vehicle_stock_number, inventorytype.name AS vehicle_type_name, vehicle.vin, vehicle.make, 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, titlestatus AS title_status, titletype AS title_type, titlereceived AS title_received, titlestate AS title_state, claimnumber AS claim_number, dismantled, purchasedfrom AS purchased_from, adjuster, filenumber As file_number, sellercontact AS seller_contact, sellerphone AS seller_phone, sellerinfo AS seller_info, pickuplocation AS pickup_location, junkingnumber AS junking_number, purchasedate AS purchase_date, 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;