If you’re using Magento to sell food and beverage products (including supplements and animal feed) you may be subject to FDA requirements — or other safety programs — that require you to implement lot-level tracking. “Lot tracking” is a system whereby a manufacturer records information about each batch that is produced.
The idea is simple: each batch you manufacture is part of a single “lot”. The items produced as part of that batch are marked with the lot number. As the items eventually end up with consumers, the manufacturer (or their distributor) needs to retain lot-level tracking on each item. If you’re ever put in a situation where you have to recall a specific lot, knowing which items were part of that lot (and who they were sent to) will allow you to contact affected customers quickly and directly. Furthermore, if your customers call in with a complaint, knowing the lot number of the item they received can help you spot batches of defective products.
Who this article is for
This article demonstrates how you could easily add lot-level tracking to your Magento shipments in your Magento 1 or Magento 2 database. Your process should look like the one below:
- Customer purchases items through Magento (or the Magento API receives an order request)
- The Magento order is fulfilled in one or more shipments — either manually, or through an API connection with a warehouse system.
- The shipment includes items picked from inventory — each item which is subject to lot tracking will have a specific lot.
- (Optional) Your shipment and order information is exported to an ERP system like Netsuite, Batchmaster, etc
Even if you are performing your lot tracking through your ERP system, you still need a way to get the relevant lot information from Magento into the ERP. So whether you’re managing inventory directly out of the Magento admin (or in your ERP) it’s a good idea to store lot-tracking information inside Magento.
Who this article is *not* for
If your fulfillment process happens directly out of your ERP (bypassing Magento) and you’re just using Magento to report tracking numbers to your customers, you don’t need to tracking lot numbers in Magento. Your process might look like the below
- Customer purchases items through Magento (or the Magento API receives an order request)
- The Magento order is exported to your ERP
- The shipment is fulfilled in the ERP system — either manually, or through an API connection.
- The items picked from inventory have their lot numbers recorded in the ERP.
- The ERP sends back a tracking number to Magento, marking the order as fulfilled.
Notice that in this scenario, the shipment is not originated in Magento, but rather directly in your ERP. The critical difference is that at no point does the lot number need to be communicated by Magento to your ERP.
How to implement lot-level tracking
To implement lot-level tracking, I’m going to propose some very basic database changes to Magento. I’m going to present the concept of the changes and show you what the SQL might look like. As I want this to apply to both Magento versions 1 and 2, I’m not going to write version specific code.
Database Changes: High Level
- First, we need to add a new product attribute which indicates whether a product utilizes lot-level tracking. This attribute will go into table eav_attribute, and will be recorded in catalog_product_entity_int as a boolean field (YES or NO value).
- Second, we need to add a new table which connects records from sales_flat_shipment_item (Magento 1) or sales_shipment_item (Magento 2) with their appropriate lot numbers
Can I just add a new field to the shipment_item table?
You may be wondering: can we not just add a new field lot_number to sales_flat_shipment_item (Magento 1) or sales_shipment_item (Magento 2) ?
I don’t recommend this for the following reason: Magento records a shipment for multiple quantities of the same item as one entry in this table. The schema of the table is approximately:
item_id | row_total | product_id | order_item_id | qty
So, if I’m selling an Avocado and I ship 4 of them to the same person, I’m only going to get one entry in either table. That opens you up to a tracking issue — what if those 4 items are not all from the same lot? If you’re certain that you’ll never have more than 1 lot of the same item in any given location, you could move forward this approach, however for most vendors this isn’t a requirement they want to implement on their warehouse staff.
Adding a new product attribute
Adding a product attribute in Magento 1 or Magento 2 is beyond the scope of this tutorial, however you can view the below links for an in-depth guide using the magento data install script I recommend naming the attribute require_lot
Adding a new table for lot tracking
Below is my proposed table schema for lot tracking. Again, I’m not going to show you Magento 1 and Magento 2 specific implementation tactics.
CREATE TABLE `cadence_shipment_item_lot` (
id int unsigned not null primary key auto_increment,
lot varchar(255) not null,
qty decimal(12,4) not null,
shipment_item_id int unsigned not null,
constraint `fk_shipment_lot_shipment_item` FOREIGN KEY (shipment_item_id) REFERENCES sales_shipment_item (entity_id) ON DELETE CASCADE
)engine=Innodb default charset=utf8;
Notice we’ve added a table which supports the below:
- An association to a shipment item (in the example I reference the Magento 2 table in the foreign key — if using Magento 1 change sales_shipment_item to sales_flat_shipment_item)
- The lot number
- The qty of that shipment item which is part of the given lot.
This structure supports data like the below:
SELECT * FROM sales_shipment_item where entity_id = 1\G
*************************** 1. row ***************************
entity_id: 1
parent_id: 1
row_total: NULL
price: 44.0000
weight: 24.0000
qty: 3.0000
product_id: 63
order_item_id: 157396
additional_data: NULL
description: NULL
name: Avocado
sku: AVC
SELECT * FROM cadence_shipment_item_lot WHERE shipment_item_id = 1\G
*************************** 1. row ***************************
id: 1
lot: ABC123
qty: 2.0000
shipment_item_id: 1
*************************** 2. row ***************************
id: 2
lot: BCD2345
qty: 1.0000
shipment_item_id: 1
Notice that we have 1 shipment item of qty 3 — but we have 2 entries in cadence_shipment_item_lot — the reason is that the 3 items included in this shipment are from 2 different lots.
Recording lot numbers when shipments are created
This is the section of the tutorial in which you’ll need to improvise depending on your fulfillment process. Generally speaking, here are my recommendations:
Manual fulfillment
If fulfilling manually, you will want to add a new field to the Sales Order – Create Shipment -> Items Grid. This new field will allow you to record a qty and lot number for each item in a shipment.
You’ll also want to hook into the new attribute you created above (require_lot) and only show this field if fulfilling an item that requires lot tracking
Automated fulfillment
If using an API connection (or some other form of automated fulfillment) you’ll need to work with your warehouse’s development team to get the lot numbers into Magento correctly. You’ll have a variety of options:
- You can download a CSV from your warehouse which contains the lot number, and then map it into your Magento database
- You can write a new API endpoint which extends the shipment endpoint and accepts lot numbers
- You can add a separate API endpoint which is called after the shipment is created, and records the lot numbers into the Magento database
Conclusion
As you can see, the process of adding lot-level tracking in Magento is possible, but there is a lot of complexity depending on your specific setup and fulfillment process. I recommend developing a plan around lot tracking prior to changing your fulfillment process or switching to a new fulfillment provider — you want to make sure you’ll be able to trace all your products should the need arise.
Need Help?
Here at Cadence Labs, we have years of experience customizing Magento to work with a variety of fulfillment processes — and can help you implement lot-tracking specific to your business.
If you would like assistance implementing lot-level tracking, call (719)-286-0751 or visit our contact page to have a real person contact you today!
