A French client supplied office worktop designs via Dropbox. For each order, a legacy Excel file written in VBA — untouched for at least 5 years — calculated the required piece quantities and returned the CNC filenames to be executed on the cutting machines. It ran poorly, and maintaining it was not straightforward. That file was the starting point.
The export department — responsible for all communications with clients outside Portugal — came to me with a second Excel file. “This is the Carnet,” the manager said, with some pride. “I need you to build something better but like this — so the client knows when they're receiving what.”
The complexity came from the nature of the orders. The client could send an order where the designs were identical but the raw material differed — one used directly in production, the other requiring an intermediate production step first, meaning a longer process and a higher probability of error. On top of that, the client could request two items from the same order to be delivered on different dates.
The original Carnet handled this with horizontal columns split across multiple rows. It was difficult to read, difficult to maintain, and impossible to extend.
The core structural problem was a many-to-many relationship — one order, multiple items, multiple delivery dates — being forced into a flat horizontal layout. The fix was to go fully vertical: one row per line item, with order values repeated across rows where necessary. The relational solution that would be natural in SQL wasn't available here, so the design had to work around it within Excel.
The concept of cycles was introduced: the client sends order data, I return the Carnet with estimated delivery dates, the client approves or pushes back. Each cycle documented.
Version 1 — structure and scheduling
Rebuilt the layout from horizontal to vertical. Added colour coding: red for problems, green for fulfilled, a third colour for planned or in production. The Carnet became a shared document — sent to the client with delivery forecasts, returned with approval or revision requests.
Version 2 — SageX3 integration
Using product colour codes and additional fields from the order data, I built a MASTERKEY — a composite identifier constructed in SQL using 4 to 5 CASE statements, each handling a different condition depending on the combination of available fields. The result could almost always be matched to the IT_REF in SageX3. This made it possible to trace orders directly in the ERP, pull additional values such as prices, dispatch dates, and distinguish between a delivery note and an invoice.
It was the first time I implemented this kind of conditional composite key in SQL. The pattern proved reusable and appeared in other projects that followed.
The Carnet was no longer just mine. It was used by the client to track expected deliveries, and by the export department to manage loads, control shipments, and track payments.
A shared, versioned order tracking document covering the full lifecycle of each order — from client request to delivery confirmation and payment. The export department had visibility they didn't have before. The client had a structured schedule they could approve or negotiate. Traceability into SageX3 was built in.
The MASTERKEY match worked “almost always” — edge cases existed where the composite key failed to match the SageX3 IT_REF, requiring manual intervention. The tool was still Excel — all the limitations of that prototype layer apply here as well. No audit trail beyond what was manually maintained in the file.
The natural evolution would be a proper order management interface connected directly to SageX3 — eliminating the MASTERKEY workaround and making the client-facing schedule a live view rather than a manually updated file shared by email.