A Lot Register for a Small Lab: Tracking Received Material Without a LIMS

A lot register is one table in which every container of material that enters the laboratory gets a row, written on the day it arrives and never afterwards overwritten. For a group of one to five people that is a spreadsheet with about fourteen columns, a locked header, a sequential internal identifier that is never reused, and a second sheet recording every movement of a container between receipt and disposal. The software is not the hard part. Deciding what a row means, and who is allowed to create one, is.

Build it before the shelf fills rather than after, because a register is retrospective by nature: its value appears only when somebody asks which container was used in work that has already happened. Freedman, Cockburn and Simcoe placed roughly US$28 billion a year of United States preclinical research spend in the category that cannot be replicated, and named biological reagents and reference materials as one of four weighted contributors to that estimate ^[4]. A register improves no material. It makes the question answerable.

What a lot register is, and what it is not

A register sits between two records that homemade systems tend to collapse into it. A goods-in record describes an event: a parcel arrived on a date, in a condition, with paperwork attached. A movement log describes what leaves the shelf. The register itself describes state — what physically exists in the laboratory right now, container by container, and what each one is.

• Goods-in record — the event of a shipment arriving: date, carrier, condition, discrepancies. One entry per delivery.
• Lot register — the inventory of what exists: one row per container, carrying identity, dating, location and status.
• Movement or custody log — the history of each container: every transfer, withdrawal, relocation, and eventual disposal.

The separation matters because the three have different readers and different lifespans. ISO/IEC 17025 requires a laboratory to operate a system for the unambiguous identification of items, retained for the whole time an item is in the laboratory, and to keep technical records in a form where amendments stay traceable ^[1]. The Good Laboratory Practice regulations state the same expectation in enforceable language: receipt and distribution of each batch must be documented, and that documentation must include the date and quantity of each batch distributed or returned ^[3]. Neither text mentions software.

The columns a small lab actually needs

Fourteen columns cover almost every small-laboratory case, abbreviating the fuller record set traceability practice expects. Add further ones reluctantly: each addition is a cell somebody will leave blank, and a mostly empty column teaches everyone that blanks are normal.

Two rules about filling cells matter more than the column list. Transcribe supplier identifiers exactly rather than tidying them — a lot printed as GP-2410-B is recorded as GP-2410-B, never gp2410b, because the supplier's systems will only match the printed form. And write every date in year-month-day order: a US laboratory reading 03/04/2026 and a Canadian supplier writing it do not mean the same day, and this is the document where that ambiguity costs most.

One row per container, not one row per order

The commonest failure in a self-built register is a row that means an order rather than an object. Ten containers of one lot arrive and become a single row reading quantity ten. Three then go to a colleague, two move to another storage unit, one is broken on the bench. The row now describes nothing that exists.

One row per container fixes this at the price of some typing. The internal ID belongs to the container rather than the lot, so a lot of ten produces ten identifiers, issued in sequence and never reused — including after a row is voided, because reuse is what makes an old notebook reference unresolvable years later. When the lot needs to be seen as a group, that is a filter on the supplier-lot column, not a merged row.

• Splitting or decanting a container creates a new row whose notes cell names the parent internal ID.
• A voided row is marked void with a reason and a date. It is not deleted, and its identifier is not reissued.
• Above roughly two hundred live containers, keep one row per lot in a parent sheet and one row per container in a keyed child sheet — the same design, in two tables.

Entries that survive being questioned later

A register is worth only the confidence somebody can place in it, and that rests on four properties: the entry is attributable to a person, it was made at the time rather than reconstructed later, the original stays visible after a correction, and the correction says why.

ISO/IEC 17025 states the correction rule plainly — when a technical record is amended, both the original and the amended content must be retained, with the date of the change and the identity of the person who made it ^[1]. In a spreadsheet that means never editing a committed cell in place: strike the old value and enter the new one beside it, or keep an amendments tab. In regulated settings the same principle is a control requirement, since 21 CFR 11.10(e) calls for secure, computer-generated, time-stamped audit trails that record operator entries and do not obscure previously recorded information ^[8]. A laboratory outside that rule's scope still gains from copying the design.

• One account per person; no shared editor credentials, ever.
• Protect the header row and the range holding committed rows; leave the append area open.
• Keep an amendments tab with internal ID, field, old value, new value, reason, person and date.
• Date and initial each row inside the row at creation, not only in the file's metadata.

Chain of custody without a custody officer

Chain of custody in its strict sense is a documented, unbroken record of who held physical control of an item, with a signed handover at every transfer. Forensic and clinical laboratories need that; most research groups do not, and attempting it produces a log nobody completes. The useful subset is a movement log with a short fixed column set anybody can fill in while standing at the storage unit.

• Internal ID of the container being moved.
• Date, in year-month-day order.
• From location and to location, using the same location vocabulary as the register.
• Quantity removed or returned, where the container is not moved whole.
• Person, by initials, and the study or notebook reference the material is being drawn for.

Two design rules separate a log that is kept from one that is abandoned. It has to be physically reachable from where material is handled — a clipboard beside the unit beats a database in another room. And the most recent row for any internal ID must state where that container is now, so relocations get logged even when nothing is withdrawn. Disposal is a movement like any other: a container that leaves the register without a disposal row is indistinguishable from one that was taken.

What the register cannot do: the label on the container

No register compensates for an unlabelled container. The register answers what LOT-2026-0041 is; only the label answers what the object in your hand is. The two obligations are separate, and in the United States one of them is statutory rather than advisory.

OSHA's laboratory standard requires employers to ensure that labels on incoming containers of hazardous chemicals are not removed or defaced ^[5]. The internal ID therefore goes on an additional label, positioned so that it covers nothing the supplier printed. Where material is transferred into a secondary container, the Hazard Communication Standard requires that container to be labelled, tagged or marked either with the information from the shipped label or with a product identifier together with general hazard information supported by the rest of the hazard communication programme ^[6].

In Canada the supplier-side duties — classification, supplier labels and safety data sheets — sit in the Hazardous Products Regulations made under the Hazardous Products Act ^[7], while workplace labelling is set by each province or territory, so the wording of that duty differs across the country even where its substance does not. Keep the second label minimal: internal ID and date received. Anything further printed on it can go stale while the register stays current.

Making dates surface before they pass

A stored retest date that nobody looks at is filing, not tracking. Two mechanisms turn the column into a control. The first is a computed column subtracting today's date from the printed date, with conditional formatting at ninety, thirty and zero days remaining. The second is a fixed monthly review — a calendar entry, ten minutes, the same date each month — whose outcome is written down even when nothing is found, so that a quiet month is recorded rather than assumed.

Record which kind of date the supplier printed. A retest date and an expiry date oblige different actions when they arrive, and a column that flattens both into expiry destroys that distinction at the moment of transcription. Record the storage condition beside it for the same reason the GLP regulations name storage conditions as part of what a test article container must carry, alongside the name, code or chemical abstract number, batch number and expiration date ^[2]. The condition is part of the material's identity as received, not a property of the shelf.

How long the rows have to live

The register outlives the material. This is the rule homemade systems break most often, because deleting a row once the container is empty feels like tidying. It removes the ability to interpret any work that used that container, which was the whole point of writing the row.

Set retention by the longest-lived record that depends on the register, not by the shelf life of anything in it: keep receiving and custody rows at least as long as the data generated from the material. Some obligations run much longer than laboratory habit expects — OSHA's laboratory standard routes employee exposure and medical records into a separate, far longer retention regime ^[5]. Practically: take an immutable export once a year, as CSV and PDF, name it with the export date, and store it somewhere the live sheet is not.

When a spreadsheet stops being enough

The thresholds are mostly behavioural, but several are countable, and worth agreeing in advance rather than discovering during a bad week.

• Two people routinely need the register open at the same moment and overwrite one another.
• Live rows pass roughly five hundred, at which point filtering replaces reading and errors stop being visible.
• Containers need barcode or QR identification because manual transcription of identifiers has started producing mismatches.
• The work falls under a regime requiring validated systems and per-user audit trails, where an ordinary spreadsheet is not a defensible record ^[8].
• The register needs joining to study or instrument records automatically rather than by hand.

There is a wide middle ground before a full laboratory information management system. A shared database — a relational table in a hosted tool, or a small local database behind a form — keeps the same fourteen columns while adding genuine per-user attribution and row-level history, which is most of what the spreadsheet lacked. Open-source electronic lab notebooks with inventory modules cover the step above. Whichever direction you take, insist on one thing: the new system must accept the identifiers already issued. Renumbering during a migration invalidates every reference written in every notebook before the move, and that is how registers die. The column set chosen at twenty containers is the migration specification at two thousand.

Where this method is weak

A register records what was printed on a label and what a person typed. It cannot confirm that either is true. A perfectly transcribed lot number establishes correspondence between your container and the supplier's paperwork; it establishes nothing about correspondence between that paperwork and the contents. Identity and purity are separate questions answered by analysis, and what a purity percentage omits is its own subject; a register's evidence is circumstantial with respect to both.

It also has no enforcement. Nothing in a spreadsheet prevents a Friday delivery being entered on Tuesday, and in a group of one there is no second person for whom the entry has to be plausible. Version history is a weaker instrument than an audit trail: it lives inside an account and a subscription and vanishes with either. These limits are why the annual immutable export and the one-account-per-person rule are worth more than any further column.