How to write the most useful piece of software your small business could own
It takes time to learn where the inefficiencies in a company lie. It doesn’t necessarily require the keenest eye to spot the issues, they are everywhere. Do you find yourself returning the file cabinets to pull out hardcopies of documents, when there are digital copies already available on the network? Are you creating labels by hand when in fact that label software you are using probably has an API and it is possible to automate label creation? Are you filling out forms by hand and official protocol to fix a mistake found on paperwork is to use white out?
While these may sound like archaic problems, they are in fact common problems across many workplaces. These were all true of a company I worked for.
I started at the company in a somewhat lower paid position – I didn’t want to commute for multiple hours to reach my job, and this was pre pandemic. I took the job at a major pay cut from what I should have been making with two college degrees. While my hands were busy assembling, I was noticing all the ways I could improve things around there, maybe simply out of laziness, but not strictly.
I was one of the only two people who knew how to create the labels. I knew how because I understood the rules for the labels. See, the company I worked for created basically two classes of things: units or boards. The rules for which product got which labels were sometimes stored in the BOM for a product, other times not. Usually, the test folder for a given unit that I was building would indicate what the labels should be, but not always. So I had to approach the data for these labels from a bottom up perspective. I had to think, “If I’m a unit, and I know I need labels, how do I write this software to be sure to include all the products. In other words, what rules describe these data in such a way that I’ll be able to grab the fields I need and generate labels as required.”
And this was the first success I had with automation.
The label software does have an API. I was able to write software that would generate a .job file, which the label software understood. The .job file was a text file with commands to do things like open a label format, set variables within the format, select a printer, print some quantity of labels.
Now I needed a way to trigger labels to print automatically when I built a job. I looked into Syspro and found there was a trigger point – at job creation, you can trigger something, or at kit issuance, you can trigger something.
I decided to use both trigger points. For the labels, I would trigger the label creation at job creation, but for that I needed to know what serial numbers were going to be used for the units. I checked with the production supervisor and it turned out that serial numbers were issued at job receipt. But the IT guy knew that there was an option to reserve serials instead at job creation.
So we switched to that methodology. When a job was created, the labels were autocreated as well, and when the job was kitted, I could issue a call to a watcher script that lived on the same computer as the label printers. So when a job was kitted, the labels would automatically print.
It was beautiful.
It saved so much time.
And I was hooked on automation.
Next I would tackle the hardcopy forms that I had to fill out with every job I built. I saw that when parts were issued, they were scanned at the kitting station. So we knew what serial number boards would be supposed to go into which unit.
The first version I made of the electronic traveler document looked exactly like a digital version of the paper job traveler. I had filled out hundreds of these by the time I decided I had had enough, and besides, orders were skyrocketing and there was no way I was going to fill out by hand over a million dollars worth of product tracking documents.
So I set about learning how to grab the fields from the database. I used a program called Process Explorer that let me look at what was running and what kind of information was being used by Syspro. I went online and found an unofficial customer support group, a small group of people that had managed to write some utility or report that was useful to other people.
Information I filled out on the paper form was things like serial number of the top level unit, serial numbers of the boards that I was building into the product, part numbers, revisions, etc. I learned which tables these fields lived in in the database, and wrote a program that takes a job number and its serials, and pulls all the materials issued to that job, then I look at the product classification and decide what role that serial number plays in the product. If the job is for multiple products, I make dictionaries of attributes as keys and their serials as values.
Then I tied my interface to the data. So the form had default values that I filled in at runtime. The user inputs a job number, the program pulls the correct details from file, and populates the form.
Then I made the form interactive. You could double click basically anything on the job traveler and edit the field, automatically saving the change to file. Thereby, if you needed to change out a board from a unit, because it failed, you could type in the change to the traveler. This provided tracking and records to the company. Eliminating the need for a paper traveler.
It took a while for people to get used to it. They didn’t trust it at first. They thought it was going to replace them. Automation was going to replace everybody.
I said no, I made this application to make your lives easier. Not to replace anyone.
I added other tabs to the application. One was for pulling up the electronic copy of the test folder which would reference any label oddities or keyplugs. Another tab was for additional information that I could read from the database but might not necessarily be in a test folder. Another tab showed the entire BOM structure, so the user didn’t necessarily have to open SYSPRO in order to see the BOM – many clicks and the user interface just wasn’t friendly. Another tab served to capture signatures from the various operations a given just must go through in order to be considered shippable.
One thing I didn’t think about was scaling. By the time I left the company, there were over 6 million pickle files housing the information for all the units we had built. I thought about saving all the data to SYSPRO, as it is, after all, a database.