Home / Freight transportation software / Custom SQL accounting blueprint

Accounting blueprint

Custom SQL to Sage and 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 source here is a carrier SQL database that is not TruckMate. The controller verifies the field map. This note does not state a sync success rate.

When the source is not TruckMate

If the source system is Trimble TruckMate, stop here and use the TruckMate SQL to Sage Intacct blueprint or the TruckMate SQL to QuickBooks blueprint, plus the TruckMate migration page. This page is for another SQL database the carrier already operates. It does not invent table names. The controller exports the fields below and checks them against Sage Intacct or QuickBooks.

Ledger pages: Sage Intacct TMS integration, TMS Sage Intacct integration, TMS QuickBooks Online integration, QuickBooks Online TMS integration, and QuickBooks Desktop TMS integration.

Field checklist the controller verifies

Custom SQL records the controller matches to Sage Intacct or QuickBooks.
Record Field the controller verifies Ledger destination
Customer invoice Invoice number, invoice date, bill-to customer, linehaul, fuel surcharge, accessorial lines, and invoice total. Sage Intacct accounts receivable invoice, or the QuickBooks invoice, after the controller maps the accounts.
Settlement bill Payee, settlement number, pay date, earning lines, and deduction lines. Sage Intacct accounts payable bill, or the QuickBooks bill, after the controller maps the accounts.
Entity Operating company the controller selects for that invoice or bill. Sage Intacct entity, or the QuickBooks company, class, or location the controller selects.
Dimension Location or terminal, department, customer, equipment, and driver, when the books use that tag. Sage Intacct dimension, or the QuickBooks class or location the controller maps. A tag with no ledger field stays off the transaction.
POD Document reference the controller can open from the invoice. Attachment or link the controller confirms is present.

Source, EXSPEEDITE, and accounting fields

The controller ticks off this map for a SQL database that is not TruckMate. The accounting column is Sage Intacct or QuickBooks, whichever the carrier uses. A CPA firm did not co-author this note. This note does not state a sync success rate.

Who verifies each custom SQL field on the way to the ledger.
Source field EXSPEEDITE field Accounting field Who verifies it
Invoice number, invoice date, bill-to customer, linehaul, fuel, accessorial lines, and total on the carrier SQL extract. The same values on the customer invoice in EXSPEEDITE. Sage Intacct accounts receivable invoice, or the QuickBooks invoice, after the controller maps the accounts. Controller
Payee, settlement number, pay date, earning lines, and deduction lines on the carrier SQL extract. The same values on the settlement bill in EXSPEEDITE. Sage Intacct accounts payable bill, or the QuickBooks bill. Controller
Operating company on the carrier SQL extract. Entity on the order. Sage Intacct entity, or the QuickBooks company, class, or location the controller selects. Controller
Terminal, department, customer, equipment, and driver tags on the carrier SQL extract. Dimension tags stored with the order. Sage Intacct dimension, or the QuickBooks class or location the controller maps. Controller
POD reference on the carrier SQL extract. POD document on the order. Attachment or link the controller confirms. Controller

Check one closed invoice and one settlement bill in the ledger the carrier actually uses. 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 sections above and below, once each.

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

The field map above, for the ledger the carrier actually uses. If the source system is TruckMate, use the TruckMate blueprint instead of this page. 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 SQL row for one invoice, the settlement bill that belongs with it, and the POD the controller expects to open.

Call 763-244-1373.