Scope

My initial task was to replace an old, in-house, error-prone, and often manual, Inventory System with a newer one. The only major request was to integrate the new system with the ERP by checking incoming inventory against the ERP's Purchase Orders and ensuring that the attributes of incoming items aligned with the Raw Material manifest in the ERP.

Because I knew the general apathy for new processes and technology in our workplace, I felt my chances for user buy-in would increase if I focussed on the needs of the most frequent users of the system and users that input critical or financial data.

I started with the user stories for purchasing and receiving. Researching the best practices and accounting methods for these allowed me to identify areas or procedures that would compromise integrity. For manufacturing companies like ours, weighted average costing was considered the defacto costing method. While that would eventually be the case, my initial goal was to implement an underlying specific inventory system to trace each package back to its shipment; this allowed me to track how users received goods and ensure that the automatic costings were accurate.

Software

After speaking with a few key users, it was clear that my understanding of these processes was subject to change. I would need to prototype something and adjust the entire process based on its efficiency in practice and correctness. At this stage, I decided to bite the bullet and use Microsoft's PowerApps platform. Was it as pretty or as quick as a React App? No. Did it engage me as a web developer? No. UI/UX Prototyping was easy, and so was handling user authentication — which I needed. The back end for the entire system would rely on an Azure SQL database and about two dozen Power Automate procedures. For testing the Database procedures, I used docker-compose and mocha, which dropped and recreated the tables with the necessary triggers and constraints between tests. Getting this setup to work was the most technical aspect of the entire project, and it has fueled my desire to work with a dedicated dev-ops and QA team.

Hardware

I decided to use cheap and replaceable hardware that users prefer, like tablets and $30 Bluetooth scanners, instead of absurdly costly, enterprise-grade barcode scanners with outdated design. After doing some research, I settled for Zebra printers but decided to bypass their native software solutions for Seagull's Bartender — the support was better outside of the US and Canada, and the API was far more accessible. So I wrote a small Express.JS middleware that would listen for HTTP requests from Power Automate and relay the print statuses from the Bartender API back to the user.

Faded Ink, Lost Labels and Other Issues ...

Shortly after the first iteration, I noticed reports of 'rogue' serial numbers that were never applied to a package — for whatever reason. These numbers would erroneously inflate our received numbers versus the invoices and decrease the item's average cost. In addition, the printer stock from our handheld printers was subject to sunlight and rain when applied to certain raw materials, causing them to fade.

To mitigate both problems, I had to remodel the receiving process. Instead of users being able to print a single barcode directly from their handheld scanner, they would print many at once from a thermal printer that produced labels that would not fade in the sun. I also added UX friction by making it necessary to validate the label once it was printed by scanning it and applying the unit of measurement (UOM); this was especially useful in increasing accuracy for packages with varying quantities, like fabrics. Unvalidated labels would never have a UOM and thus would not contribute to the received items count. Finally, the cardinality between shipments, invoices & orders needed to be reviewed, as some suppliers would split shipments or send damaged items.

Power BI and DAX

After almost a year of iterating and developing other functionality: warehouse locations, transferring and issuing items, and stock-checking procedures (an entire post on its own), my final task was to ensure that users from accounting, purchasing, warehousing could view data in ways that could inform their actions and processes. Already committed to the Power Platform, I used PowerBI and DAX to provide users with about a dozen reports. In conjunction with training and buy-in from other managers, these reports gave users greater confidence in the system and strengthened accountability between staff members.