matt murtaugh.
about. skills. work. uses. blog.

Inventory Management System

When we started selling board games at Divrsion, I realized that I needed more control over my inventory than Square had to offer on their POS systems. I originally had a large spreadsheet that contained all the information that helped me run my business. The spreadsheet had:

  • MSRP

  • What we sell for

  • What we pay for the product

  • Our lowest price we could do with cash & credit card after fees

  • If the manufacturer had any MAPP on specific products

  • What distributor do we purchase that product from

I also used this list to export for our brother label printer, and we can use this to print labels.


For the most part, this list worked great for the first year. But as we grew, so did the list. We eventually had over 2,000 items on the list. Not to mention that printing labels was slow because the printer only printed one product at a time.

That isn't the story of what created our IMS system, but I will talk about each phase of the product below.

Phase 1 - Inventory Counts

The origins of our inventory management system are inventory counts. At the time, even on Square Retail, there was no easy ability to do inventory counts at the end of the year. I spent a few hours exporting our inventory and formatting a spreadsheet. The most significant problem is that it was a mess. Only one person could work on the spreadsheet at a time.

I created the first version to help assist with inventory counts. We would locate the item, determine how many were in stock, and see the difference. If the inventory numbers were correct, we would move on. If it was off, we needed to see if any were hiding in the backroom, waiting for customer pickup, or just missing.

Phase 2 - Replacing the old spreadsheet

One day while working on inventory, I realized that the spreadsheet had gotten corrupted somehow. Luckily I had a backup with very little missing. But it showed me that I couldn't rely on that anymore.

So I took the working backup, formatted the data, and imported it through TablePlus. I wired up a few models in Laravel, and I was off.

At first, I still used Square to add inventory, exported the list to print labels, and managed a lot of the data by hand.

I should note that many of the features shown in the screenshots happened a little later.

Phase 3 - Restocks & labels

One of the things I like the most about Square for Retail is the ability to scan a group of products to restock. The downside was that the register required the Retail version of SquarePOS to use this feature. Overall, the retail version just wasn't suited for our business.

I browsed through the Square Developer API and realized this was a relatively simple task, or so I thought. Working with external data is never really as simple as it seems. 

I rewrote how the models interact with the database, imported even more data from Square, and wired up my app to send restocks back to Square.

The process honestly works excellently. After I send the restocks to Square, it will tell me if there are any discrepancies, and I can adjust by hand.

I also realized this was the perfect time to batch print labels for products. I found a label paper that matched what I currently use in the store. I added the ability to print a batch of tags based on the items I restocked. I consider whether the item should have a label (a simple toggle in the database) and the quantity of the item.

The best part is scanning the barcode to add an item to a restock. It makes the process so easy for anyone to do.

My favorite feature of the restocking system is that it gives me pricing information and warns me when my margins are incorrect. This feature has been handy as pricing continues to rise. It means that I don't forget to update my pricing when checking in an existing item or that I don't incorrectly type a sale price when adding new inventory.

Phase 4 - Catalog sync / Current inventory

After redoing much of the database structure for restocks, it seems like the next step was directly communicating changes from the IMS and Square back and forth. Modifications made on Square find their way to the IMS and vice versa.

This sync lets me sync the current inventory directly on my app, making it easy to order new products. I can look at what is missing and add the items to an order.

The final part was adding items directly to Square from the IMS app.

© 2024 matt murtaugh. All rights reserved.