After several thousand orders processed and v2 deployed…

There were a few issues with the first version of my mobile order picking software, mostly revolving around my initial choice of avoiding using a real database and instead using a text file(s) as a database. With v2 I chose to completely revamp my mobile software suite and go with a MySQL database. This meant architecting several tables and attempting to predict what I would want to use in the future to extrapolate said data.

I chose to revamp the entire suite to use MySQL so I needed a database to keep a log of bin location changes, all order information, restock ‘to-do’ list items, and a user database. For my mobile order picking system I completely rewrote the code I had to read information from our core ERP database and place it into my own MySQL database for manipulation. This allows me to only store the information I need and in the manner I wish to store it for easy access.

Without going over the details of exactly how I’m doing what I’m doing (I wish to keep some things a secret anyway since it is my code), I’ll explain what gets displayed and how it is an improvement over the first version.

I’m now able to display individuals items and component items that make up a kit in-line and according to bin location. This makes picking items for an order extremely efficient since everything is in order of physical location. I also show additional bin locations for component items now. Component items are one of the most complex things I’ve worked with since you must first find out what items belong in a kit, extrapolate it, get information for the individual component items (e.g. quantity available, locations, etc) and then merge it into the master item array for that order. Maybe this is easily done for others, but I had a difficult time with it but in the end I prevailed! I also show what kit # each component item is a part of and of course do the math so that the correct quantity is picked (e.g. if someone orders three of the same kit with two items where one item has a quantity of one and the other item has a quantity of 4).

I’m also keeping track of whether or not we’re out of something on the shelf when it says we have it in the computer. This shouldn’t happen I know, but it does so I allow the user on the floor to make the needed adjustment and offer a quantity change report for such changes.

Something that I feel is really neat (but very simple to add) is that the remaining items to pick is always at the top of the screen now. This way the order puller is always reminded of how many items are left before the order is complete, which I automatically mark as soon as the last item is picked.

There are many little enhancements and additions to my program like auto time-stamping when the order was scanned and when the last item from the order was picked which is useful for some analytics that I’ll get to below.

The restock list also got a complete revamp and is now multi-user and has three modes when scanning an item. The first scan will add it to the list, the second scan will mark it in red and scanning again will remove it from the list since that is the step that the restocker would have restocked the shelf.

Since I’m using MySQL now, I can extrapolate all kinds of data from it to do some really cool things. To do this I needed to come up with an authentication system requiring managers to login since I don’t want just anyone to have access to such information. I’m using the same user database that I use for other parts of the suite…I just give access to the admin panel to mangers’ login. Once logged in I have the ability to access order picking records for all orders picked or just an individual picker for example. I added pagination to this since there are so many records (and for database load reasons obviously). Bin location change records can also be accessed from this system.

The thing that I’m most proud of is my latest addition however. I’ve been working on this for the last couple of days and I think it will prove to be really helpful! Managers can now see performance analytics for order pickers and salespersons. I use the Google chart API to display the data extrapolated from the MySQL database. This allows a manager to quickly gauge the performance of an employee which happens to be the number of items that that employee can process in any given hour for the last 30 working business days. Here’s what that chart looks like:

It’s pretty easy to see who may be slacking off or what adjustments are needed. Granted that different users may have slightly different roles and can only be compared to others in a similar grouping, but this allows a manager to do this and it’s the first time such thing has been possible in our environment.

Going forward I hope to add to this suite based on input from users and managers but within reason. I am the IT department for a mid-sized company. There are no other full-time employees that I can delegate tasks to and that’s ok because I love what I do, but it also means that I have limited resources to be able to handle all that needs handling. This is one reason I’m very excited about SaaS as it allows me to offload some tasks like email server upkeep so that I can spend more time on projects like this one.

I’m certainly not a master programmer and probably not a master at any one thing, but what I am is confident that I can excel at being the IT department for ~160 employees with around 120 workstations with all the infrastructure in between. I love making IT easier for folks while at the same time giving them better tools to do their jobs better and from the place they feel most comfortable. I find it truly awesome that I can jump on my Mac at home or Starbucks and do probably 90% of what I can do in my physical office. The other 10% would be infrastructure failures which are rare due to the nature of such infrastructure and the redundancy built-in. Having automatic fail-overs for nearly all servers and a large percentage of user’s workstations (e.g. thin clients) makes this entirely possible.

Thank you technology for evolving at a fast enough pace to keep me on my feet and eager for the next day!

Leave a Reply