Shopdata is Dynatron's custom file format.
Shopdata is a custom CSV export specification used to transmit DMS/SMS data from your shop into our data integration platform. This spec was designed for easy integration with your Development Department as a series of 10 CSV files. This Shopdata spec was designed to handle service department data, and all other types of future data formats as needed.
Due to updates that may be made in the format, please reference these permalinks in all communications.
CSV column headers ARE required and are case-sensitive.
Service data typically includes all repair orders closed the previous day, (or specified date range,) detailed labor lines, parts lines, employees, vehicle information, and customer information related to those repairs. Keep in mind that if ROs are backdated, but the format only exports data that does not include that date, we will not receive data for the backdated ROs. EG: An RO is closed on 5/5/2019 with a close date of 5/3/19. If the format only pulls and exports data from the previous day (5/4/19), this RO will not be included in the export. This service spec is versioned. The version number is defined in the filename you send to us.
The current spec version is v1
This is an obfuscated example of 718 ROs exported as a single set of 10 required files.
The service feed requires 10 CSV files in a transmission "set". All 10 files are required to be sent, even if some are not implemented.
Filenames must be exact and are case-sensitive (all lowercase, even the .csv extension).
service_v1_1234_ros_1503711531.csv service_v1_1234_lines_1503711531.csv service_v1_1234_techs_1503711531.csv service_v1_1234_parts_1503711531.csv service_v1_1234_misc_1503711531.csv service_v1_1234_complaints_1503711531.csv service_v1_1234_recommendations_1503711531.csv service_v1_1234_customers_1503711531.csv service_v1_1234_vehicles_1503711531.csv service_v1_1234_employees_1503711531.csv
All files are CSV and REQUIRE a HEADER line. The header line allows columns in any order and even optional columns.
Main repair order CSV file. Example Filename: service_v1_1234_ros_1503711531.csv
ColumnName Description -------------------------------------------------------------------------------- * RoNum - Repair order number. Can be up to 20 characters long (letters and numbers). * RoStatus - Current status of the repair order. Valid options (Case sensitive, can ONLY be one of these statuses): CLO=Closed, OPN=Open, VOI=Void, INV=Invoice, PAY=Payment. Blank fields or other statues will defaultDefaults to CLO=Closed. Blank fields or other departments will default to Closed Department - Department the repair order was worked in. Defaults to SRV=Service (most common). Valid options (Case sensitive, can ONLY be one of these departments): SRV=Service, BOD=Body Shop, QLB=Quick Lube, SAL=Sales and F&I, FLE=Fleet, PDI=Pre Delivery Inspection, PTS=Parts, DIS=Distributor, TRK=Truck, INV=Individual, BUS=Business. Blank fields or other departments will default to SRV=Service. * CloseDate - RO close date in 'YYYY-mm-dd' * OpenDate - RO open datetime in 'YYYY-MM-DD hh:mm:ss' format (ex: 2017-01-09 17:03:09) PromiseDate - RO promised datetime in 'YYYY-MM-DD hh:mm:ss' format (ex: 2017-01-09 17:03:09) EstCompleteDate - RO estimated completion datetime in 'YYYY-MM-DD hh:mm:ss' format (ex: 2017-01-09 17:03:09) InvoiceDate - Customer invoice date in ''YYYY-mm-dd' LedgerPostDate - Date this RO was posted to accounting in ''YYYY-mm-dd format WarrantyPostDate - Warranty post date in ''YYYY-mm-dd' format * CustNum - Customer number in DMS/SMS, must link to valid customer also exported in the customers CSV file. Can be up to 20 characters long (letters and numbers). CustHasApp - Customer had an appointment for this RO. Nullable boolean where 0=false, 1=true, empty/blank column if not applicable for this line, or omit column altogether if never used in DMS/SMS CustIsWaiter - Customer is waiting on RO completion. Nullable boolean where 0=false, 1=true, empty/blank column if not applicable for this line, or omit column altogether if never used in DMS/SMS CustHasRental - Customer has rental. Nullable boolean where 0=false, 1=true, empty/blank column if not applicable for this line, or omit column altogether if never used in DMS/SMS CustTransport - Type of customer transportation if provided. (Case sensitive, can ONLY be one of these letters) C=Cab, D=Night drop, L=Loaner, O=Other, R=Rental, S=Shuttle/courtesy, T=Town Car/Limousine. Blank fields or other letters will default to Other. CustComment - Customer comment for entire RO (comments on line level use complaints CSV file). * VehVin - Vehicle Identification Number (not more than 17 characters long), must link to a valid VIN also exported in the vehicles CSV file. StockNum - Vehicle stock number. Up to 20 characters. * VehMileIn - Vehicle mileage when brought to the shop. Numerals only, up to 8 digits (do not include commas/decimals). VehMileOut - Vehicle mileage when leaving the shop. If you don't have in/out, just use VehMilIn as the single mileage field. Numerals only, up to 8 digits (do not include commas/decimals). AdvisorNum - Service advisor employee number in DMS/SMS, MUST link to valid employee also exported in the Employees CSV file. Up to 32 Characters. PricingGrid - Pricing grid used for this RO. Up to 5 characters. TagNum - Key tag number. Up to 10 characters. PaymentMethod - Payment method (CC, Credit Card, Cash, Check etc...) Up to 32 characters. Comment - Technician (or advisor) comment for entire RO (per line uses complaints CSV file). Up to 32 characters. ActualHours - Total actual (tech work) hours for entire RO. Numerals and decimal only- Up to 8 digits including 2 decimal places (123456.78) BilledHours - Total billed (sold) hours for entire RO. Numerals and decimal only- Up to 8 digits including 2 decimal places (123456.78) LaborSales - Total labor sales amount for entire RO. Numerals and decimal only- Up to 10 digits including 2 decimal places (12345678.90) LaborCost - Total labor cost for entire RO. Numerals and decimal only- Up to 10 digits including 2 decimal places (12345678.90) PartsSales - Total parts sales amount for entire RO. Numerals and decimal only- Up to 10 digits including 2 decimal places (12345678.90) PartsCost - Total parts cost for entire RO. Numerals and decimal only- Up to 10 digits including 2 decimal places (12345678.90) MiscSales - Total misc sales amount for entire RO (combine all MIS, GOG, SUB, PNT, SHP, FRT, DIS, HAZ, OTH... as "misc"). Numerals and decimal only- Up to 10 digits including 2 decimal places (12345678.90) MiscCost - Total misc cost for entire RO (combine all MIS, GOG, SUB, PNT, SHP, FRT, DIS, HAZ, OTH... as "misc"). Numerals and decimal only- Up to 10 digits including 2 decimal places (12345678.90)
Repair order labor lines. Also known as Jobs, ops/operations, Detail, or Labor Lines. Most DMS have labor lines, while small SMS systems have "detail" which includes both parts and labor detail. We only want labor detail in this file, parts detail will be in the Parts files.
Example Filename: service_v1_1234_lines_1503711531.csv
ColumnName Description -------------------------------------------------------------------------------- * RoNum - RO number as the main linkage back to the ros CSV. Must exactly match the RoNum in the ROs file * OpenDate - RO open datetime in 'YYYY-mm-dd HH:ii:ss' format as the main linkage back to the ros CSV. Because most shops REUSE RO numbers, we need these 2 columns to link back to proper RO. * LineNum - Sometimes seen as JobNum, LineID etc... some identifier for a line. Can be 1, 2, 3, etc for every RO, or specific to your database primary key 44323, 44324 or something globally unique like a UUID or GUID. Best if you do NOT generate/increment this yourself during export of this CSV. If you do generate, be sure to SORT your lines consistently. So re-exports will be in the same order and generate/increment in the same order (1, 2, 3, 4)... ParentLineNum - Some DMS/SMS support "sublines" or nested lines. Relate sublines to parent lines using LineNum and ParentLineNum if possible. * Paytype - Long paytype for this line. Paytypes must begin with a C, W, or I (customer, warranty, or internal paytypes) Up to 10 Characters, or simply C, W, I paytype if you don't have long paytypes. Will default to C if blank (customer pay). * OpCode - Operation code (or repair code). Some SMS dont use opcode, they use canned jobs or some other form of job categorization. Up to 32 characters. * OpDesc - Operation code description (or repair description). Please remember to properly escape quotes and other special characters. Up to 500 characters. IsComeback - Whether the customer is returning (1/true) or first-time customer (0/false) Nullable boolean where 0=false, 1=true, empty/blank column if not applicable for this line, or omit column altogether if never used in DMS/SMS. IsUpsell - Upsell flag (nullable boolean 0=false, 1=true, empty/blank column if not applicable). Nullable boolean where 0=false, 1=true, empty/blank column if not applicable for this line, or omit column altogether if never used in DMS/SMS. * ActualHours - Total Actual hours for this one line. ActualHours are the sum of all tech work, which should match the sum of your tech's hours for the operation in the Tech's CSV file below. Up to 8 digits, including 2 decimal places (123456.78). * BilledHours - Total Billed hours for this one line. Up to 8 digits, including 2 decimal places (123456.78). * LaborSales - Labor sales amount for this one line. Up to 10 digits, including 2 decimal places (12345678.90). * LaborCost - Labor cost for this one line. Up to 10 digits, including 2 decimal places (12345678.90). * PartsSales - Parts sales amount for this one line. This should match the parts detail provided in the parts CSV file below. Up to 10 digits, including 2 decimal places (12345678.90). * PartsCost - Parts cost for this one line. This should match the parts detail provided in the parts CSV file below. Up to 10 digits, including 2 decimal places (12345678.90). * MiscSales - Misc sales amount for this one line. Combine all MIS, GOG, SUB, PNT, SHP, FRT, DIS, HAZ, OTH... as this one "misc" value. This should match the misc detail provided in the misc CSV file below. Up to 10 digits, including 2 decimal places (12345678.90). * MiscCost - Misc cost for this one line. Combine all MIS, GOG, SUB, PNT, SHP, FRT, DIS, HAZ, OTH... as this one "misc" value. This should match the misc detail provided in the misc CSV file below. Up to 10 digits, including 2 decimal places (12345678.90).
Tech information as it relates to each line item (Job). Many DMS/SMS support multiple techs per line each with separate hours, rates, days and descriptions. This file allows a one-to-many relationship with the Lines CSV file. See also, Employees CSV (for non-tech employees).
Example Filename: service_v1_1234_techs_1503711531.csv
ColumnName Description -------------------------------------------------------------------------------- * RoNum - RO number as the main linkage back to the ros CSV. Must exactly match the RoNum in the ROs csv. * OpenDate - RO open datetime in 'YYYY-mm-dd HH:ii:ss' format as the main linkage back to the ros CSV. Because most shops REUSE RO numbers, we need these 2 columns to link back to proper RO. * LineNum - LineNum as the main linkage back to the lines CSV file. Must exactly match the RO's LineNum in the Lines csv. There can be multiple techs per line. * TechNum - Technician employee number in DMS/SMS, must link to valid employee also exported in the employees CSV file. TechRate - Technician's hourly rate for this job. Up to 10 digits, including 2 decimal places (12345678.90) * ActualHours - Actual hours tech worked on this line for this RO (not billed hours, that is not per tech but on the job as a whole, as noted in the Lines file). WorkDate - Date in 'YYYY-mm-dd format. Some techs work on the same job multiple days and work is clocked individually per day. WorkDesc - Some DMS/SMS provide a description each time the tech worked on the job. See also: complaints file.
Part information as it relates to each line item (Job).
Generally, in most large DMS systems, parts are UNDER a line, meaning they belong or are related to a line. This defines a 3rd level hierarchy which might be represented like so:
RO #1234: Advisor #1234, Custom #523, VIN XYZ - Line 1: Opcode Xyz, Tech #1234, Hours 12, LaborCost $43, LaborSales $300... - Part UF2432B - Quantity 10, Unit Cost $12, Unit Sales $400 - Part XZ324AF - Quantity 2, Unit Cost $1, Unit Sales $120 - Line 2: Opcode Abc, Tech #1234, Hours 1, LaborCost $4, LaborSales $210... - Part ... ... ...
But many small SMS do not report parts under a line. A part is simply more detail to an entire RO. This means labor lines and parts lines are equal in hierarchy, all simply "detail" under an RO. LineNum is optional in this file for this very reason. Leaving LineNum blank signifies these are RO level parts and you have no line-to-part relationship, meaning they are not under or attached to a line in any way. Filling out the LineNum field signifies these parts belong under a line and are related to a line.
Example Filename: service_v1_1234_parts_1503711531.csv, file is required, if you don't have this data send an empty CSV with proper name.
ColumnName Description -------------------------------------------------------------------------------- * RoNum - RO number as the main linkage back to the ros CSV. * OpenDate - RO open datetime in 'YYYY-mm-dd HH:ii:ss' format as the main linkage back to the ros CSV. Because most shops REUSE RO numbers, we need these 2 columns to link back to proper RO. LineNum - LineNum as the main linkage back to the lines CSV file. Optional because some small SMS do not link all the parts to a job. They are considered RO level parts, equal in hierarchy to jobs themselves. LineNum - LineNum as the main linkage back to the lines CSV file. Optional because some small SMS do not link all the parts to a job. They are considered RO level parts, equal in hierarchy to jobs themselves. * PartNum - Part number. Up to 32 characters. * PartDesc - Part description. Up to 500 characters. * Quantity - Count of how many parts were used. Decimals allowed, such as 6.5 quarts of oil. Up to 12 digits (with 4 decimals) (12345678.1234). Negatives are NOT allowed - please use positive part counts with negative sales/cost * UnitSales - Sales amount per individual part. This is sales per individual whole item. Total would be calculated Quantity * UnitSales. Up to 10 digits including 2 decimals (12345678.90). * UnitCost - Cost per individual part. Up to 10 digits including 2 decimals (12345678.90). This is per individual whole item. Total would be calculated Quantity * UnitCost
Miscellaneous information as it relates to each line item (Job). See the description in the parts CSV section above about parts being related to a line or to an RO. The same concept applies to misc items. You can have both misc items at an RO level and misc items at a line level. This file allows for both since LineNum is optional.
Example Filename: service_v1_1234_misc_1503711531.csv, file is required, if you don't have this data send an empty CSV with proper name.
ColumnName Description -------------------------------------------------------------------------------- * RoNum - RO number as the main linkage back to the ros CSV. Must exactly match the RoNum in the ROs file. * OpenDate - RO open datetime in 'YYYY-mm-dd HH:ii:ss' format as the main linkage back to the ros CSV. Because most shops REUSE RO numbers, we need these 2 columns to link back to proper RO. LineNum - LineNum as the main linkage back to the lines CSV file. Optional because some small SMS do not link all the parts to a job. They are considered RO level parts, equal in hierarchy to jobs themselves. * MiscCode - Miscellaneous code. Up to 32 characters. * MiscDesc - Misc description. Up to 500 characters. * MiscType - Three character misc type. Defaults to MIS=Miscellaneous if blank. Valid options: MIS=Miscellaneous, GOG=Gas Oil Grease, SUB=Sublet, PNT=Paint, SHP=Shop Supplies, FRT=Freight, DIS=Discount, HAZ=Hazard, OTH=Other * Quantity - Count of how many misc items were used. Decimals allowed, such as 6.5 quarts of oil. Up to 12 digits (with 4 decimals) (12345678.1234). Negatives are NOT allowed - please use positive counts with negative sales/cost * UnitSales - Cast per individual misc item. This is per individual whole item. Up to 10 digits including 2 decimals (12345678.90). Total would be calculated Quantity * UnitSales * UnitCost - Sales amount per individual misc item. This is per individual whole item. Up to 10 digits including 2 decimals (12345678.90). Total would be calculated Quantity * UnitCost
Complaint, Cause and Correction (CCC) information as it relates to each line item (Job). These are line level CCC only. There are no RO level CCC, but the ROs CSV file does allow Comment and CustComment on the RO level.
Example Filename: service_v1_1234_complaints_1503711531.csv, file is required, if you don't have this data send an empty CSV with proper name.
ColumnName Description -------------------------------------------------------------------------------- * RoNum - RO number as the main linkage back to the ros CSV. Must exactly match the RoNum in the ROs file. * OpenDate - RO open datetime in 'YYYY-mm-dd HH:ii:ss' format as the main linkage back to the ros CSV. Because most shops REUSE RO numbers, we need these 2 columns to link back to proper RO. * LineNum - LineNum as the main linkage back to the lines CSV file. Must match the line number for the RO in the Lines csv. * Complaint - Complaint (comment text, remember to strip/escape quotes first or you will break a CSV). * Cause - Cause (comment text, remember to strip/escape quotes first or you will break a CSV). * Correction - Correction (comment text, remember to strip/escape quotes first or you will break a CSV).
RO level recommended services by OpCode number.
Example Filename: service_v1_1234_recommendations_1503711531.csv, file is required, if you don't have this data send an empty CSV with proper name.
ColumnName Description -------------------------------------------------------------------------------- * RoNum - RO number as the main linkage back to the ros CSV. Must exactly match the RoNum in the ROs file. * OpenDate - RO open datetime in 'YYYY-mm-dd HH:ii:ss' format as the main linkage back to the ros CSV. Because most shops REUSE RO numbers, we need these 2 columns to link back to proper RO. * OpCode - OpCode of recommended service. Up to 32 characters. * OpDesc - Operation code description (or repair description). Up to 32 characters. * Performed - Was the recommendation performed? (boolean where 0=false, 1=true; defaults to 0.)
Customer information. All CustNum defined in ROs CSV must have a full entry here. Example Filename: service_v1_1234_customers_1503711531.csv
ColumnName Description -------------------------------------------------------------------------------- * Num - Customer number in DMS/SMS. Main linkage back to the ros CSV file. * Name - Customer full name (we prefer if you use the 3 first/middle/last name fields below instead). Up to 200 characters. * FirstName - Customer first name. Up to 50 characters. * MiddleName - Customer middle name. Up to 50 characters. * LastName - Customer last name. Up to 50 characters. * Company - If not a person, company name should be used. Leave the other name columns blank as necessary. Up to 200 characters. Salutation - Salutation (Mr., Mrs...). Up to 10 characters. Suffix - Suffix (Jr., Sr...). Up to 10 characters. Title - Customer title at place of work. Up to 100 characters. Department - Customer department at place of work. Up to 100 characters. IsPerson - Customer is person or company. Boolean where 0=customer is a company, 1=customer is an individual. Defaults to 1 if blank. Address1 - Customer address line 1. Up to 100 characters. Address2 - Customer address line 2. Up to 100 characters. District - Customer district. Up to 50 characters. City - Customer city. Up to 50 characters. State - Customer state. Up to 50 characters. (Preferred two-letter state name (eg: TX, NY, etc) Zip - Customer zip. Up to 20 characters. Country - Customer country (short, US, defaults to US (up to 50 characters)). PhoneHome - Customer home phone number. Up to 20 characters. PhoneHomeExt - Customer home phone extension. Up to 10 characters. PhoneBus - Customer business phone number. Up to 20 characters. PhoneBusExt - Customer business extension. Up to 10 characters. PhoneCell - Customer cell phone number. Up to 20 characters. PhoneFax - Customer fax number. Up to 20 characters. PhonePager - Customer pager number. Up to 20 characters. PhoneOther - Customer other phone number. Up to 20 characters. PhoneOtherExt - Customer other extension. Up to 10 characters. Email1 - Customer primary email address. Up to 50 characters. Email2 - Customer secondary email address. Up to 50 characters. BirthDate - Customer birthdate in 'YYYY-mm-dd' format Language - Customer preferred language (2 char ISO 639-1 code, defaults to EN). ContactMethod - Customer preferred contact method (possible values: EMAIL, HOME, WORK, CELL, FAX, PAGER, MAIL, OTHER. Up to 5 characters. SolicitMail - Allowed to solicit via Mail (boolean where 0=false, 1=true, defaults true). SolicitPhone - Allowed to solicit via Phone (boolean where 0=false, 1=true, defaults true). SolicitEmail - Allowed to solicit via Email (boolean where 0=false, 1=true, defaults true).
Vehicle information. All VehVin defined in ROs CSV must have a full entry here. Example Filename: service_v1_1234_vehicles_1503711531.csv
ColumnName Description -------------------------------------------------------------------------------- * Vin - Vehicle Identification number. Up to 17 characters. Main linkage back to the ros CSV file. * Year - Vehicle year. Up to 5 digits (preferred YYYY format) * Make - Vehicle make. Up to 50 characters. * Model - Vehicle model. Up to 50 characters. Color - Vehicle exterior color. Up to 50 characters. StockNum - Stock number. Up to 20 characters. StockType - Stock type (possible values: NEW, USED, DEMO, MISC, NEWLEASE, USEDLEASE, FMLLEASE, FLEET, SPECIAL, RENTAL, UNKNOWN. Up to 10 characters. SalesmanNum - Salesperson employee number in DMS/SMS, must link to valid employee number also exported in the employees CSV file. FleetNum - Fleet number. Up to 20 characters. License - License plate number. Up to 20 characters. Certified - Certified pre-owned (nullable boolean where 0=false, 1=true, empty/blank column if not applicable for this line, or omit column altogether if never used in DMS/SMS). VisitCount - Count of visits to shop. Up to 5 digits (eg 12345). Default = 0. ProductionDate - Manufacturer production date in 'YYYY-mm-dd' format InServiceDate - Date vehicle was placed in service in ''YYYY-mm-dd format SoldDate - Sold date in ''YYYY-mm-dd' format WarrantyExpDate - Warranty expiration date in 'YYYY-mm-dd' format DeliveryDate - Delivery date in ''YYYY-mm-dd' format DeliveryMileage - Delivery mileage in ''YYYY-mm-dd' format FirstRoDate - First RO close date in ''YYYY-mm-dd' format
Employee (advisor, tech, salesman) information. Example Filename: service_v1_1234_employees_1503711531.csv
ColumnName Description -------------------------------------------------------------------------------- * Num - Employee number in DMS/SMS. Main linkage back to ros CSV AdvisorNum, techs CSV TechNum and Vehicles CSV SalesmanNum. Must link to valid employee number also exported in the employees CSV file. * Name - Employee full name (we prefer if you use the 3 first/middle/last name fields below instead). Up to 60 characters. * FirstName - Employee first name. Up to 20 characters. * MiddleName - Employee middle name. Up to 20 characters. * LastName - Employee last name. Up to 20 characters. Phone - Employee phone number. Up to 20 characters. Email - Employee email address. Up to 50 characters.
