A batch record has three readers, and it is written for whichever one arrives first: you, six months later, working out why one batch tasted different; your client, asking for evidence before they place another order; and an investigator or auditor, asking what went into a specific unit of product.
All three want the same thing. What was made, from exactly what, when, by whom, and where it went. Most batch records answer the first and last of those and skip "exactly what", which is the one that matters in a recall.
The fields that earn their place
| Field | Why |
|---|---|
| Batch code | The unique handle everything else hangs off |
| Production date | Bounds the window when scoping a withdrawal |
| Product and client | Which brand's product this batch is |
| Recipe name and version | What was actually made, not what the recipe says today |
| Planned batch size | Lets you see when a batch ran short |
| Actual batch size | What went in |
| Yield | Actual output against expected, in percent |
| Units produced | The number that gets withdrawn |
| Pack size | Because a withdrawal is scoped in units, not kilos |
| Made by | Attribution, and who to ask |
| Checked by | A second pair of eyes, where you have them |
| Notes | Where the useful anomalies end up |
Then, separately and this is the important part, one row per ingredient lot used:
| Field | Why |
|---|---|
| Batch code | Links the lot back to the batch |
| Ingredient | What it was |
| Supplier | Who it came from |
| Supplier lot code | The single field that makes a recall answerable |
| Received date | Distinguishes two deliveries of the same lot |
| Quantity used | Lets you reconcile a bag against the batches it fed |
| Unit | Because kg and L are not interchangeable |
Why one table cannot work
The instinct is one row per batch, with columns for the ingredients. It fails
immediately: a batch uses a variable number of ingredients, each with its own lot
code, so you end up with ingredient_1_lot, ingredient_2_lot and a column count
set by your most complex recipe.
Worse, it makes forward tracing impossible to search. "Which batches used lot 4471" becomes a scan across fifteen columns rather than a lookup in one.
Two tables joined on the batch code — one row per batch, one row per ingredient lot per batch — is the shape that answers both questions. It is also, not coincidentally, how the transformation event in FDA's traceability rule is described: input lot codes, output lot code.
Your own lot codes
Your finished product needs a code of its own, assigned by you, and it should be the batch code. That is the handle a client quotes back at you and the one printed on the case.
Keep it boring. Year plus a running number, never restarted, never encoding anything that might change. If a code has meaning baked into it — product, production line, shift — then reorganising any of those either breaks the scheme or produces two batches with the same code, and the second is much worse.
Print it where it survives the supply chain. A batch code on a label that comes off in a chiller is a batch code you do not have.
The mistakes that cost the most
- Recording the lot at goods-in and not at use. You know what arrived. You cannot say which batch it went into, so a recall scopes to every batch in the window the bag was open.
- "Recipe: current". A recipe that has been edited since means the record describes something that no longer exists. Version it, and freeze the version a batch points at.
- Filling the sheet in at the end of the week. The yields get rounded, the
anomalies get forgotten, and
made_bygoes blank. A record written during production is a different document from one reconstructed afterwards. - Yield recorded only when it is bad. A yield column with gaps tells you nothing about drift, which is the thing it is uniquely good at showing.
- Untracked sub-recipes. A base made Tuesday and used in four products Thursday needs its own batch code, recorded as an input downstream. Otherwise the lot trail dies at the base.
A template to start from
Two CSVs in the shape described above, with a couple of example rows so the relationship between them is obvious:
- batch-record-template.csv — one row per batch
- batch-ingredient-lots-template.csv — one row per ingredient lot used
Join them on batch_code. In a spreadsheet, that is a filter on the second sheet
once you know the batch, or a filter by supplier_lot_code when you are tracing
forward from a recall notice.
Where a spreadsheet stops
It stops in three predictable places, and none of them are about the spreadsheet being the wrong tool for holding data.
The first is that nobody fills it in during a busy production day, because it lives on a laptop in the office rather than on the bench. The second is that the recipe it names has since changed, and the sheet has no way to freeze it. The third is that tracing forward across a year of batches means reading rows by eye, and under time pressure people widen the scope rather than finish the search.
If you are hitting those, the fix is not a better spreadsheet. How to trace a recalled ingredient lot sets out what the chain has to do end to end.