Production Traceability System

Problem

Production and shipment information was spread across multiple Excel sheets and workbooks, making it time-consuming to investigate a production lot, device serial, shipment reference, component lot or production history.

• Which shipment contained a specific device serial or production lot?
• Which component lots or related production records were connected to a finished product?
• Which shipment reference was connected to a specific batch or serial range?
• How can production records, component structures and shipment data be connected without searching manually?

As the system grew, traceability was no longer only about searching one table. It needed to connect production records, component structures, shipment entries, service-related records, stock movements and supporting reference data.

Solution

I built a centralized traceability system using Excel, Power Query, structured tables and supporting Python tools. The system combines production and shipment data into a searchable structure for faster investigation and clearer follow-up.

By entering a serial number, batch, article, shipment reference or component lot, the system can identify related production records, shipment rows, parent records, component usage and connected traceability paths in one structured view.

Part of a Connected Production Data System

This project is the central search and investigation layer of a larger workflow. FIFO Planning helps decide which stock should be picked, the Shipment Entry Assistant helps register shipment data safely, and the Traceability System connects the data afterwards for searching and investigation.

FIFO Planning Shipment Entry Assistant Traceability System

Key Features

• Centralized search across production, shipment, stock and component data
• Serial number, batch, article and shipment reference search logic
• Connection between parent records, component lots and finished products
• Shipment traceability linked to production records
• Power Query structure for combining production and component data
• Support for device records, component data, service-related records and material lots
• Structured output for faster investigation and clearer traceability review

Recent Improvements

• Expanded the project from a search workbook into a connected traceability workflow using Excel tables and Power Query.
• Connected production records, component structures, component lots, service-related records, shipment entries and material lot information.
• Added logic for showing manufacturing and expiry-related dates only when relevant information exists.
• Improved traceability between parent records, component records, production lots and shipped products.
• Added support for service-related records so returned or updated units can be included in the traceability chain.
• Improved the workbook structure to support portable file paths instead of depending only on fixed drive locations.
• Started building a Python desktop helper app for faster traceability searches and smart connection paths.
• Improved performance by using cached data and CSV-based lookup logic for faster searches.

Current Status

The project has developed from a simple Excel search idea into a wider production data system. It now connects planning, production records, component data, shipment entry and traceability search into one more controlled workflow.

Tools Used