One of our clients recently came to us with a problem: entering new stock locations into their existing legacy system would take several days, two or three times a year. This task fell to one person, and it wasn’t part of his usual role. Naturally they assumed we would spend some days developing a piece of functionality to spare him this task.
They're one of our smaller clients and they use a ten year-old system which doesn't like being modified very much. We inherited this system within our support contract a couple of years ago, and we’ve fixed various problems and made various modifications since then. There are also no automated tests, and it is laborious to add new bits of architecture to it without unpredictable side effects.
Software should fit the business
So why were stock locations difficult? The system only allowed one location to be entered at a time—they couldn’t be done in bulk. But this doesn’t match the company’s workflow—they often buy new shelves to put in their warehouse in large quantities, so these stock locations become available in the hundreds, and if they have to be added one at a time, that's obviously very cumbersome—a poor user experience and bad for business.
In this case the requirement was for sporadic use, and by and large the stock location part of the system fit their ways of working—so it did not make any business sense to start over. So how could we help?
After some discussion and investigation, it turned out that the stock locations table is literally just a list of stock location identifiers, and doesn't need to interact with any other pieces of the system. This was surprising given that other parts of the system interact in various illogical ways, but this one part was completely on its own—meaning we can be sure there would be no regressions if we add in additional stock locations manually in bulk. Really, all they needed was some way of turning a list of stock locations into a script that would insert them directly into the database (saving someone taking time out of their role to do this manually).
Simpler and Simpler
At first, I considered writing a Python script to do this for them, which would take the spreadsheet as an input and output an SQL file. Just as I was about to start, I realised that even that was overkill—all I had to do was use a spreadsheet with the following value in B1:
=CONCATENATE("insert into part_locations values ('", A1, "');")
The client instantly understood how to use it, copied from their existing spreadsheet, dragged the formula down, and then sent it back to me. I then copied column B, and ran it against the database inside a SQL transaction.
All in all it took me roughly ten minutes. It costs the client only a few minutes each time he wants to do this, and takes seconds to run a new set each time he sends it to us.
This saves him roughly 6-8 days per year which can now be spent doing his actual job. It just goes to show - sometimes the best solution is the simplest one.
