Inventory Management & Barcode Scanning

I don’t know about you, but I jump on every change to get FileMaker to connect with and do cool things outside of FileMaker. So admittedly I was excited when a client requested an Inventory Management System that would utilize FileMaker Go on iPads connected to bluetooth barcode scanners.

Our client wants to achieve several goals. They want to reduce costly errors resulting from inaccurate receiving, packing and shipping accounting practices. In other words, when their software shows that they have 48 products in stock, they want to be sure that they actually have 48 products in stock; when a packing list calls for a 1/4” part, they want to be sure that the warehouse employee doesn’t grab a 1/8” part off the shelf. In addition to the efficiency gained by reducing human error, the client also wants to capture more information about the products they are warehousing in order to more efficiently organize those products and track their movement from the moment they enter the warehouse to the moment they leave.

The client ships somewhere between 3000-4000 different products from their warehouse. They currently keep track of these products by recording how many are ordered in and how many are shipped out. However, between the original purchase order and the final invoice sent to their customers, the system is vulnerable to a high degree of human error.

That’s where the barcoding comes in.

At first, the solution appeared obvious: build a library of codes and then scan against it. Employees would scan the products as they arrive to build out the inventory data and the detailed product information data; they would scan them as they appeared on pick lists as the employees move the products from shelf to shipment; and then they would do a finally scan against the shipment’s packing list, once again updating inventory data.

So I planned and built a system to scan the products in the warehouse in order to build that prerequisite library of codes.

“No battle plan ever survives first contact with the enemy.”

After scanning 10% of their warehouse, I realized that my plan had relied on the assumption that the barcodes the warehouse employees would be scanning would not change Purchase Order to Purchase Order.

Why not slap a custom, internal barcode on every product?

OK, so if the codes that appear on the products are going to change every time our client receives a new shipment, why don’t we just create our own unchanging barcodes and affix them to every shipment. You might also have been thinking, “What if the vendor of a certain product changes? Won’t you have to update your BarcodeData table with the new vendor’s barcode information? Even if you don’t switch vendors, what if the vendor changes their barcode? What if the barcode changes for each shipment?” These are all valid points, and part of the reason that Dan Weiss strongly suggested applying custom barcodes to each product in order to achieve the highest degree of confidence in the accuracy of the system. But in the end, we decided not to go this route. Let’s answer each of the above questions to find out why:

What if the vendor of a certain product changes?

Imagine you are selling a commodified product such as bulk coffee. You get coffee from Vendor A and Vendor B but you sell the coffee from these two vendors as one product, lets call it Product C. If you decide that you’re going to instead source your coffee from Vendor B and Vendor D, you still have your Product C that remains unchanged. Why go through the trouble of updating the system for Vendor D if you can simply apply your own internal barcode to all incoming and outgoing products?

Won’t you have to update your BarcodeData table with the new vendor’s barcode information?

As described above, relying on manufacturer and vendor barcode data demands that your system be constantly updated to handle changes in manufacturer, vendor, and product offerings.

What if the barcode changes for each box or shipment?

THIS is where things really get interesting. What if, in January, you receive your first order of Product A from Vendor A. You add the barcode information into the system. But in February when you receive your next order of Product A from Vendor A, the barcode is totally different.

What if the reason why it is different is because the particular kind of barcode used contains variable information about the product such as Expiration Date, Lot Number, Batch Number, Country of Origin, and much more?!

If we have a manufacturer or vendor barcode like this, it becomes less of a liability—something to hide and ignore if at all possible—and more of an opportunity, especially when you consider that the client is in the business of medical and EMS supply and device sales. Their products expire. Their products are recalled. Their products can sometimes mean life or death.

Imagine the possibilities.

A vendor alerts you that there is a recall on batch numbers G8#### through H9####. Currently, the client has the ability to check their stock of products and return the recalled items and alert those customers that they know received this product to check their own stock for defective or dangerous items. However, the client currently does not know the exact batch numbers of the products they sell because they do not capture that information from the barcode. Their best guess of which customers to alert would be a broad sample based on several possible dates. This could encompass an enormous number of customers and is inaccurate and inefficient.

What if, instead, you could know immediately exactly how many products you had in stock were affected by this recall? What if you knew precisely which customers received the defective products, when they received them, the invoice that they appeared on, etc.?

What about expiration dates? Products go bad. If the warehouse is not organized properly, with those products expiring first placed toward the front of their respective rows so that employees will grab them before products expiring in the more distant future, expired products or nearly expiring products might be accidentally shipped to customers. If the customer notices and requests a refund or a replacement, the client has just been hit with an avoidable cost. Even if it is noticed that product has expired and it is not accidentally sent to a customer, the client has to throw out the product and write off the expense.

Imagine an employee preparing an order for shipment. They are pushing their cart through the warehouse, iPad mounted on the handle showing them what they need to pull from the shelves. They pull an item and scan it to add it to the order. The system knows that the particular product scanned expires in 5 years, but that there are still 10 of that product in stock that expire in only 1 year. The system can remind the employee of this fact so that they instead ship the appropriate product and avoid loss, customer dissatisfaction and avoidable costs.

Implementation

OK, so this is easy, right? First, you build a table of BarcodeData that is related to your Product. Then, when you scan a box from a Purchase Order delivery, the system compares the scanned code to your table of BarcodeData and determines which product and quantity is contained therein. You do this for each box in the delivery. Now you know exactly what you’ve received—which products and how much of each.

Same thing for shipping to customers, right? The warehouse employee is given an electronic packing list on an iPad mounted on a rolling cart. He is instructed to scan the appropriate amount of each product on the packing list and to immediately place it on the cart. If, for whatever reason, a product needs to come off the cart, the employee will scan it back into inventory. When they’ve scanned the correct products and quantities, the iPad confirms that the shipment is correct and the employee can pack it up and send it out.

What’s the big deal?

Barcode symbology, that’s what.

UPC/EAN, Coupon (UPC portion)

As I had initially assumed would be the case with our client, barcodes are commonly unchanging from shipment to shipment, as in the case of the UPC/EAN barcode family. Normally, when we think of barcodes, this is the family that we are thinking of. UPC (Universal Product Code) and EAN (European Article Numbers) are interchangeable with the notable difference that the EAN contains a country code at the beginning. These codes do not contain any variable information; they contain a country code (if EAN), a manufacturer code and a product code. If every single product you are working with carries a UPC or EAN code, you can simply use the manufacturer code with very little worry that you will have to frequently update your database. Each time that particular product comes in or out the door, it will have that same number on it, ever unchanging (ok, maybe not ever, but you get the point).

To be continued…

So, I’ve got my work cut out for me… My job now is to build a system to differentiate the different symbologies and extract the data contained therein. Then we can begin to build our library of barcodes. Stay tuned for Part 2 and leave a comment below!