JB Route Builder v1.1

This tool takes raw BCC planting job data, expands it by tree quantity, splits jobs geographically across your trucks, and produces a single Detrack-ready xlsx file for upload. This manual covers everything you need to use it correctly.

Overview

The Route Builder solves three problems that previously required manual work in Excel:

Row Expansion

BCC sends one row per job regardless of tree quantity. Detrack requires one row per tree. The tool automatically duplicates rows based on the Qty field, with each row getting a unique asset number.

Truck Assignment

Jobs are split across trucks using geographic clustering — suburbs are grouped by proximity so each truck covers a contiguous area rather than bouncing across Brisbane.

Detrack Formatting

Street and suburb fields are concatenated into a single address field with QLD, AUSTRALIA appended — the format Detrack's routing engine requires.

Quick Start

Upload your Data xlsx

Drag and drop or click to browse. The tool reads the "Data" sheet automatically.

Enter the starting asset number

Get this from the asset database. This must be entered fresh every run — it is never saved.

Check your truck configuration

Confirm truck names and tree capacity limits are correct for today's run.

Click Generate Route Files

Review the breakdown and preview, then download the xlsx and upload it to Detrack.

01 — Uploading Data

The tool accepts .xlsx or .xls files exported from your job management system. It looks for a sheet named "Data" first — if not found, it reads the first sheet.

Expected columns

Column names are detected automatically so minor variations are fine, but the following fields must be present:

Column	Used for
Job Number	Task Number in Detrack output
Notification	Notification No. in Detrack output
Street	First part of address field
Suburb	Second part of address, also used for geographic routing
Work Order	Work Order Number in Detrack output
Qty	Controls how many rows are generated per job
Tree Species	Description field in Detrack output
Scheduled Date	Delivery Date in Detrack output
Size	Unit of Measure in Detrack output (e.g. 300mm)

Note — Assign To column The "Team Assigned" or "Assign To" column from your data is intentionally ignored. The truck assignment is handled entirely by the tool based on geographic clustering.

02 — Asset Numbers

Each individual tree in the output requires a unique asset number. These are sequential — if a job has a Qty of 3, it will consume 3 asset numbers.

⚠️ Critical — Asset numbers must never be reused Always check the asset database before running the tool to confirm what number to start from. The tool will tell you the range used (e.g. 1880–1923) so you know where the next run should begin. The starting number is never saved by the tool for this reason.

How to find your starting number

Check the asset register in the Google Sheet database. The starting number for your run should be one higher than the last asset number recorded. After your run, record the end number shown in the Results summary so the next person knows where to start.

03 — Truck Configuration

Truck names and capacity limits are saved in your browser between sessions. You only need to change these when your fleet changes.

Default trucks

Planting 1 and Watering 1 are pre-configured with a default capacity of 20 trees each.

Adding a truck

Click + Add Truck, enter the truck name exactly as it should appear in Detrack, and set the maximum number of trees it can carry.

Removing a truck

Click the × button on the right of any truck row. The remaining trucks will have jobs rebalanced across them.

Tip — Truck names matter The truck name you enter here becomes the value in the "Assign To" field in Detrack. Make sure it matches exactly what Detrack expects — including capitalisation and spacing.

Generating Output

Click Generate Route Files to process the data. The Results section will appear showing:

Section	What it shows
Stats bar	Total jobs, unique stops, number of trucks, and asset number range used
Truck Breakdown	Which suburbs each truck is covering and how many trees vs capacity
Warnings	Any suburbs not found in the coordinate database (these are assigned by load balancing instead)
Preview	First 100 rows of the output so you can spot-check before downloading

Click Download Jobs (Detrack Ready) to save the xlsx file. The file contains a single sheet named Jobs with all rows sorted by truck then address.

Uploading to Detrack

Log into Detrack

Go to your Detrack dashboard and navigate to the Jobs section.

Select Import

Use the Import / Upload option and select the xlsx file you downloaded.

Map columns if prompted

Column names in the output match Detrack's expected format. If Detrack asks you to map columns, match them by name.

Confirm and submit

Review the import preview in Detrack and confirm. Jobs will be assigned to the correct drivers based on the Assign To field.

Output Column Reference

Column	Source	Notes
Task Number	Job Number	From your data
Delivery Date	Scheduled Date	Formatted as DD/MM/YYYY
Work Order Number	Work Order	From your data
Address	Street + Suburb	Concatenated as "123 Example St, Suburb, QLD, AUSTRALIA" in title case
Instructions	—	Always blank — fill in Detrack if needed
Assign to	Tool	Set by truck assignment, not from your data
Notification No.	Notification	From your data
Quantity	Qty	Original quantity from your data (same on every expanded row)
Asset No.	Tool	Sequential, unique per tree
Description	Tree Species	From your data
Qty	Qty	Repeated — Detrack uses both columns
Unit of Measure	Size	e.g. 300mm from your data

Routing Logic

The tool uses geographic coordinates for Brisbane suburbs to cluster jobs so each truck covers a contiguous area. The algorithm works as follows:

Suburbs are plotted by latitude and longitude, then grouped into clusters using k-means — one cluster per truck. The tool then checks if any cluster exceeds its truck's capacity and attempts to move suburbs to neighbouring clusters that have room. Suburbs that aren't in the coordinate database are assigned to whichever truck has the most remaining capacity.

⚠️ Unknown suburbs If you see a warning about suburbs not in the coordinate database, those jobs are still included in the output but their geographic placement may not be optimal. Report any missing suburbs so they can be added to the database.

Frequently Asked Questions

Why are some rows duplicated in the output?

This is intentional. Detrack requires one row per tree, not one row per job. If a job has a Qty of 3, it appears 3 times — identical except for the Asset No. This is how Detrack tracks individual trees on a delivery.

Why is the asset number field always blank when I open the tool?

By design. Asset numbers must never be reused, so the tool never saves or pre-fills this value. You must check the asset database and enter the correct starting number every single run.

My truck settings disappeared. What happened?

Truck settings are saved in your browser's local storage. Clearing your browser cache or using a different browser or device will reset them. Re-add your trucks and they'll be saved again from that point.

The tool put two suburbs I expected to be together on different trucks. Why?

The geographic algorithm prioritises keeping each truck under its capacity limit. If putting two neighbouring suburbs on the same truck would exceed capacity, it will split them. Try increasing the truck capacity or adding another truck.

Can I override which suburb goes to which truck?

Not currently in the tool — manual adjustments would need to be made in the downloaded xlsx before uploading to Detrack. This is something that could be added in a future version.

A suburb is showing in the unknown suburbs warning. What do I do?

The job will still be included in the output and assigned by load balancing. Report the suburb name so it can be added to the coordinate database in the next update. The output is still safe to upload.

Changelog

Version	Changes
v1.1	Default truck capacity changed to 20 trees. Asset number no longer saved between sessions.
v1.0	Initial release. Row expansion, geographic truck clustering, Detrack-ready xlsx output, title case address formatting, Brisbane suburb coordinate database.