In-house programming insights…

When I was 12 I began flipping through my first real programming book (BASIC doesn’t count): Teach Yourself C in 21 Days. Without a doubt I can say that I did not learn C in 21 days. I wouldn’t really pick up programming again until I was 28 and I couldn’t expect myself to remember anything that I had read regarding C when I was 12.

When I came back to programming at 28 I went for the platform that I love, iOS. I released two apps in the Apple app store in 2011. One a nightstand/weather app that I developed after a couple of years of not being able to find what I wanted from another developer (imagine that considering all of the apps in the app store these days!) and the other a tipping calculator that was really just something to keep my mind challenged.

I’ve sold several hundred copies of my nightstand app since it was introduced in mid-2011 and while that hasn’t made me rich or an acquisition target for Google, it does pay for my $99/annual Apple developer’s fee and part of my Starbucks habit.

Before I went on Christmas vacation for my day job I decided to start on a new project that took some of my newly found knowledge and apply it to something that could help the company be more efficient and ultimately help reduce errors and keep costs down.

I looked into doing this with Objective-C, but the database that we use doesn’t have a driver with any SDK available for iOS that I could find. It does support ODBC though, which made making this a web app the best solution.

The first step was giving me a connection to the database to work with of course. This ended up being a Windows 7 virtual machine (I converted our physical servers and lots of desktops over to vSphere in 2011) running IIS, PHP and the client for our database engine with the ODBC connections in place.

My place of work manufactures thousands of automotive parts and we just moved to a ODBC/SQL based ERP solution in 2011 to help increase our efficiency and enhance our processes going forward. In 2011 I deployed location-wide wifi by way of ~20 access points. That’s another feat in and of itself since you have to deal with channel interference between the AP’s and contend with other interferences around. This gives us the framework to move around between office/manufacturing/warehouse locations without so much as a hiccup in connectivity to our back-end resources.

I looked into a third-party solution with Motorola handheld computer/scanners that integrated directly with our ERP system, but because I’m so adamant about iOS and web based technologies driving the future of computing (even in the workplace), I decided that it would be a waste of money and resources to implement such a solution. The cost was astronomical and I set myself on a path to see if I could come up with a self-programmed alternative solution.

My first solution was the simpler of the two that I had on my plate. Inventory look-up. Since we have so many parts and locations, it becomes an issue in locating where a certain item is and more so checking the quantity that is on hand at any given time during the day. Traditionally we have workstations placed throughout to do these lookups which require a constant back and forth when looking up items. We could use laptops, but it adds heft and bulk that is over the top IMO.

Using a combination of PHP, HTML and Javascript, I was able to create a simple web app to do this lookup. It makes sure the part number entered or scanned exists in our system and if it does it displays the locations where that item exist and the total quantity on hand:

My next ask would bring a level of complexity that required me to really learn some more advanced PHP skills while I made the program. This is really my first real dealings with PHP to create a complete application, so my knowledge was fairly minimal when I started the project. To create my next application which would create an order picking system would test my endurance.

Several things were needed for order pullers using the application. For the above inventory lookup solution and this one I chose to go with the latest 4th generation iPod Touch. This would be coupled the Linea Pro 4 2D scanner from Infinite Peripherals. This is the same barcode scanning case that Apple uses in their stores. I also needed a way for my web app to talk to the Linea Pro 4. SwipeTrack Solutions has a program that does this so that’s what I ended up going with.

The application itself had a host of its own requirements. The order number, order date, salesperson, item # to be pulled, quantity to be pulled, quantity currently available in our warehouse(s) and where the item is located in our warehouse(s).

In addition, the puller would need to know that an item had already been pulled and have it automatically crossed off of the list so to speak. To do this part of the application I had to decide if I wanted to go with a flat file or a database. In the end I chose that a flat file was the easiest method to implement and has the easiest upkeep for what I’m using it for.

When a puller scans the barcode of the order’s picking ticket or enters it manually, the order is brought up on the device and lists all of the items and their quantity with location(s) in order of location to make their pulling as efficient as possible. The puller scans a barcode (2D Data Matrix type is what I went with) at the item’s location or on the item itself and physically pulls the item and places it into his or her cart. If the wrong item is scanned the puller is presented with an error and must manually continue the pulling process. This eliminates errors of the wrong item being pulled for the customer.

If the right item is scanned then the item number is appended to a file that has the order number as the file name. If the order doesn’t exist and the first item from the order was scanned, the order file is created and thus tells the program that the order is in the process of being pulled and places a notification at the top of the web app of that.

On each refresh of the page (happens each time an item is scanned, right or wrong) the application checks to see if an item in the order exists in the order file that was created. If it exists then that of course means that the item was pulled (because it was scanned) and I have it turn a different color and place a *P* (for pulled) beside of the item number. On each refresh the puller is taken back to the same spot on the order that he was at previously…this way he or she doesn’t have to scroll down the page to find where they left off. This is helpful on very large orders.

Here is what the end result looks like for an example order:

It’s simple on the surface, but I ran into several issues that I had to overcome. There are arrays within arrays which confused me to no end, but eventually I came up with a solution. I’m pulling from fairly complex SQL queries with joins, ordering, etc which also baffled me in the beginning. I’ve learned a lot about writing SQL queries over the last couple of weeks which I’ll no doubt reap benefits from for years to come.

We’re in the testing stages and have one puller using the app full-time now. It dazzles me to no end that something that I created, from scratch, has been worthwhile enough for the company to request a dozen or so of these things eventually after the test phase is over. At the same time, it is a scary thought to know that something that was created in spare time will end up being an essential part of doing business and will no doubt add another level of stress to my already growing levels.

Being the lone IT person for a medium sized company is daunting. I’m given freedom to do projects like this one, implement things like virtual machines and thin clients and other goodies, but in the end I have to support those technologies and it is forever becoming more challenging to keep up with.

I am always up for a good challenge however. Stuff like this makes me want to come back day after day, especially when others pronounce their appreciation for such hard work.

Leave a Reply