Home / Freight transportation software / TruckMate SQL to QuickBooks

Accounting blueprint

TruckMate SQL to QuickBooks blueprint

This is an EXSPEEDITE technical note a transportation CPA can use. A CPA firm did not co-author it. Exspeedite LLC has been in freight technology for more than 18 years. Founder Scott Elliott is the transportation systems architect. Strong Tower Consulting, LLC, founded in Minnesota in 2008, is a separately organized sister company under common ownership. Consulting does not require a software purchase. The controller verifies the field map for QuickBooks Online or QuickBooks Desktop. This note does not state a sync success rate.

Where the TruckMate SQL extract starts

The TruckMate to EXSPEEDITE migration page describes a SQL extract of customers, tariffs, equipment, and settlements. This blueprint does not replace that page and it does not invent TruckMate table names. Read the product pages for the ledger connection: TMS QuickBooks Online integration, QuickBooks Online TMS integration, and QuickBooks Desktop TMS integration. If the ledger is Sage Intacct, use the TruckMate SQL to Sage Intacct blueprint.

Field checklist the controller verifies

QuickBooks stores company, class, and location differently from a Sage Intacct dimension. The controller names the QuickBooks field that will hold each tag. This note does not rename those QuickBooks fields.

TruckMate SQL records the controller matches to QuickBooks.
Record Field the controller verifies QuickBooks destination
Customer invoice Invoice number, invoice date, bill-to customer, linehaul, fuel surcharge, accessorial lines, and invoice total. QuickBooks invoice, after the controller maps the income accounts. Online or Desktop is the file the carrier uses.
Settlement bill Payee, settlement number, pay date, earning lines, and deduction lines. QuickBooks bill, after the controller maps the expense accounts.
Entity Operating company the controller selects for that invoice or bill. QuickBooks company, class, or location the controller selects.
Dimension Location or terminal, department, customer, equipment, and driver, when the books use that tag. The QuickBooks class or location the controller maps. A tag with no QuickBooks field stays off the transaction.
POD Document reference the controller can open from the invoice. Attachment or link the controller confirms is present on the QuickBooks transaction or on the TMS invoice the books reference.

Source, EXSPEEDITE, and accounting fields

The controller ticks off this map before a batch. QuickBooks stores company, class, and location differently from a Sage Intacct dimension. A CPA firm did not co-author this note. This note does not state a sync success rate.

Who verifies each TruckMate SQL field on the way to QuickBooks.
Source field EXSPEEDITE field Accounting field Who verifies it
Invoice number, invoice date, bill-to customer, linehaul, fuel, accessorial lines, and total on the TruckMate SQL extract. The same values on the customer invoice in EXSPEEDITE. QuickBooks invoice, Online or Desktop, and the income accounts the controller mapped. Controller
Payee, settlement number, pay date, earning lines, and deduction lines on the TruckMate SQL extract. The same values on the settlement bill in EXSPEEDITE. QuickBooks bill and the expense accounts the controller mapped. Controller
Operating company on the TruckMate SQL extract. Entity on the order. QuickBooks company, class, or location the controller selects. Controller
Terminal, department, customer, equipment, and driver tags on the TruckMate SQL extract. Dimension tags stored with the order. The QuickBooks class or location the controller maps. A tag with no QuickBooks field stays off the transaction. Controller
POD reference on the TruckMate SQL extract. POD document on the order. Attachment or link the controller confirms on the QuickBooks transaction or on the EXSPEEDITE invoice. Controller

Check one closed invoice and one settlement bill before a cutover. If a row does not match, stop that row. This note does not state a sync success rate.

If the carrier wants to estimate unbilled detention before that invoice exists, use the carrier-entered formula on the margin leakage benchmark. This blueprint does not publish that dollar amount. The links to that benchmark and to the other blueprints are in the list below, once each.

What should a fleet CFO review before the first QuickBooks batch?

The field map above: invoice, settlement bill, entity, dimension, and POD. The controller names the QuickBooks company, class, or location. This note does not state a sync success rate. A CPA firm did not co-author it. Consulting does not require a software purchase.

Request an EXSPEEDITE TMS demonstration

Tell Exspeedite LLC how the office runs. This form posts to the EXSPEEDITE demonstration intake. Bring a redacted invoice, a settlement bill, the class or location the controller uses, and a POD the controller can open.

Call 763-244-1373.