Role: Operations System Consultant and Designer
Client: (Client name changed for privacy.)
Headquarters: Illinois, USA
Industry: Logistics / Freight Transportation
Team Size: 30+ drivers, operations staff
Tools: Airtable
Year: 2024
Areas Impacted: Accounting, Dispatch, Safety, Operations
This is a logistics and freight company with a fleet of drivers working under two models: Company Drivers and Owner Operators. Dispatchers coordinated loads, the safety team managed drivers and equipment, and accounting handled weekly payroll. Information moved between departments by hand, often via phone or email.
The operations manager contacted me, looking for a single system to keep everything organized in one place.
Before I could build anything, I needed to understand what was happening across all three departments and not what was supposed to be happening. So I spent time mapping all workflows. What data was available, where it resided, how it got from one place to another, and if it was getting lost along the way.
My assessment showed that load records weren’t properly linked to the drivers who completed them. This was important because the system needed accurate data to automate calculations. If the records were wrong or incomplete, the formulas would just produce incorrect results more quickly, which is worse than doing it by hand.
So before working on the system’s structure, I reviewed and cleaned up the existing records. I fixed what I could, flagged what I couldn’t, and made sure there was a solid foundation.
The three-base Airtable structure built. Safety, Dispatch, and Accounting each operate as a connected base, sharing data between departments.
I built the system using three connected Airtable bases, one for each department, all set up to share information with each other.
The Safety Base tracked people and equipment, including driver profiles, truck and trailer records, and which driver was paired with which truck and trailer at any time. I used a concatenated formula to make driver and equipment names easy to find and search throughout the system. This helped operations quickly locate and assign drivers.
The Dispatch Base handled load management, tracking each load from booking to delivery, including shipper and receiver details, broker info, routes, stops, rates, miles, and status at each stage. I restructured the Loads table to link properly to driver records and used a formula to make names easy to find when logging trips. While building, I noticed that certain operators could see other team members’ positions, which wasn’t right for the business. I fixed this by adding a user field system so everyone only saw what they needed.
The Accounting Base brought everything together. Driver data came in from Safety, and load data came in from Dispatch. For the first time, accounting could see each driver’s weekly activity, all trips, miles, deductions, credits, and advances in one place, without having to ask anyone.
How a single trip moves through Dispatch, Safety, and Accounting.
The business has two types of drivers and two very different pay structures. Company Drivers earn 30% of the total load amount for each trip they complete in a week. Owner-Operators get a set rate per mile based on total miles driven. These needed different numbers, data, and logic. You have to know which type of driver you’re calculating for before you start.
I set up formula fields in the Accounting Base to handle all of this automatically. The system identified the driver type, pulled the right data, load amounts for Company Drivers, miles for Owner Operators, applied the correct formula, and included any deductions, credits, or advances for that week. The final payment was ready without anyone having to do it one by one.
The master payroll table was built to be flexible, so the accountant could view the data in whatever way was most useful. They could filter by month to see all trips, deductions, and credits for a period. They could group by driver type to see Company Drivers and Owner Operators separately, or by deduction type to understand where money was withheld and why. All these views could be combined, so the accountant could answer any question without having to re-sort or recompile anything by hand.
Before the system, payroll meant manually gathering information from dispatch and safety, cross-checking it, and calculating each driver’s pay one by one. After the change, the accountant just confirmed the week’s trips were logged, which dispatch was already doing, and the system handled the rest. What used to take all day Friday now took only a fraction of the time.
I also built dashboards for dispatch and driver management, because seeing what’s happening across the fleet was as important as payroll.
For dispatch, the dashboards tracked total load amounts and the number of loads booked, giving the team a clear view of weekly and monthly volume at a glance. For driver management, leadership could see how many drivers were active at pickup and in transit and how the fleet was split by driver type.
I want to be clear about one thing this system couldn’t do, because it needs mentioning.
When a driver’s per-mile rate changed from, let’s say, 70 cents to 75 cents, the system updated to the new rate. But the old rate history is lost which is a major Airtable issue, making it harder to track a driver’s performance over time. There was no way to look back at earlier months and confirm what a driver had been paid before. The new rate simply replaced the old one.
For a trucking company that might need to audit past pay or check compensation from earlier periods, that’s a limitation. I explored workarounds like separate rate history fields, linked records with versioning, and manual tracking tables, and found a practical solution to recommend.
I explained this directly to the operations lead, documented it clearly, and presented the alternatives with honest assessments. They understood the limitation and made an informed decision on how to move forward.
By the project’s conclusion, this business achieved a new integration: dispatch, safety, and accounting operating from the same data. Drivers received accurate, timely payments, stopping the need for manual data collection.
