613parts · AS400 Invoice Export

Each morning, the AS400 needs to dump today's outgoing-delivery invoices as a single CSV file and drop it in the dispatch-invoices/ Google Drive folder. The dispatch dashboard reads the file, geocodes addresses, groups by branch, and feeds the driver tablets. Hand this page to whoever maintains AS400 reports.

01 required columns · don't ship without these

Column name	Required	Description	Example
invoice_number	REQUIRED	Unique invoice ID. We use this as the order_id on the driver tablet.	INV-2026-04832
customer_name	REQUIRED	Bill-to or ship-to name as shown on the printed invoice.	Marcus T.
ship_address	REQUIRED	Full delivery address — street + city + province + postal. We geocode this. Postal code is the most important part — it determines which truck delivers (Belleville vs Kingston).	23 Main St, Belleville, ON K8N 4Z2
customer_phone	recommended	Driver taps this on the tablet to call. Include area code, dashes optional.	613-555-1234
total_amount	recommended	Invoice total in dollars (number, no currency symbol). Drives the daily revenue stat in dispatch.	264.00
line_items	recommended	Parts on the invoice. Format: qty × name (sku); qty × name (sku) separated by semicolons. Driver sees this on the stop screen.	4× ACDelco Pads (ACD-17D1414); 2× Rotor (ACD-18A2657)

02 optional columns · helpful when present

Column name	Required	Description	Example
branch	optional	Force which truck delivers: belleville, kingston, or pickup (latter excludes from route — customer comes to counter). If empty, we infer from postal code.	belleville
date	optional	Invoice date in YYYY-MM-DD. If not present, we use today's date.	2026-05-04
payment_status	optional	Did they pre-pay? Values: paid, cod, account. Helps driver know what to expect at the door.	paid
delivery_window	optional	Time hint for the driver. Free text.	9:00-17:00
notes	optional	Special instructions. Driver sees this on the stop screen with a red flag.	Customer asked for text 30 min before

03 sample CSV · the exact format

invoice_number,date,customer_name,customer_phone,ship_address,branch,total_amount,payment_status,line_items,delivery_window,notes
INV-2026-04832,2026-05-04,Marcus T.,613-555-1234,"23 Main St, Belleville, ON K8N 4Z2",belleville,264.00,paid,"4× ACDelco Pads (ACD-17D1414); 2× Rotor (ACD-18A2657)",9:00-17:00,
INV-2026-04833,2026-05-04,Sarah K.,613-555-5678,"456 Bridge St, Belleville, ON K8N 5K1",belleville,1489.00,paid,"4× DAI Mission (DAI-MISSION-17-GB); 4× Michelin X-Ice (MICH-XICE-21550R17)",,Customer asked for text first
INV-2026-04834,2026-05-04,Dave R.,613-555-9999,"12 Highway 401 W, Trenton, ON K8V 1W7",,89.00,paid,"1× ACDelco Filter (ACD-PF64); 5× AMSOIL OE (AMSOIL-OE5W30)",,
INV-2026-04838,2026-05-04,Walk-in Customer,,"PICKUP at counter",pickup,45.00,paid,"1× Wagner Pads (WAG-QC1212)",,Customer picks up Tue

Notes on quoting: wrap any field that contains a comma in double quotes (the ship_address and line_items fields almost always need this). Embedded double quotes are escaped as "" per RFC 4180.

04 daily routine · 5 minutes each morning

7:30am · Run AS400 report. The "Today's Outgoing Invoices" report. Filter to date = today, status = paid OR account, exclude voided invoices.
Export as CSV. Save with filename invoices-YYYY-MM-DD.csv (replace YYYY-MM-DD with today's date — e.g. invoices-2026-05-04.csv).
Drop in Google Drive. Folder: My Drive / 613parts / dispatch-invoices / — drag from Downloads to the folder. That's it.
Open dispatch dashboard at dispatch.613parts.ca, sign in with admin PIN, click the Report tab → ↑ UPLOAD CSV button → pick the file.
Confirm the import succeeded. You'll see a green box: "✓ imported N invoices · X Belleville · Y Kingston · Z pickup". If anything failed (like a bad address), the warnings show which invoice — fix in AS400 and re-upload.
8:00am · Drivers tap PIN. Today's route loads with all the invoices, optimized order, real customer phone numbers and addresses. Done.

05 common gotchas

Postal code is critical. If the address is missing the postal code (or it's wrong), we can't reliably assign the right truck. Belleville zone = K8N/K8P/K8R/K8V/K0K. Kingston zone = K7K-P/K0H/K7R.
Multi-province customers. If a customer is outside Ontario or far from both branches, we default to Belleville and flag a warning. AS400 should ideally not include those in the daily delivery export at all (those should ship via courier, not truck).
Pickup-at-counter invoices. Set branch=pickup or include "PICKUP" in the address — these get filtered out from the route automatically.
COD invoices. Set payment_status=cod so the driver knows to collect at the door. Worker doesn't process payment — that's still a paper-and-cash interaction.
Re-running the report. If you upload twice in one day (e.g., new invoices came in after the morning batch), the second upload replaces the day's route. Drivers see the new combined list on next refresh. Don't manually combine — let the system do it.
Bad characters in addresses. Apostrophes ('), French accents (é, ç), and quotes (") are fine. Unicode emoji or weird control characters will break the geocoder. AS400 reports usually don't produce these but worth knowing.