Finding the simplest solution

How one client saved 6-8 working days a year with a one-line formula

Sam Millner
Sam Millner Technical Consultant & Project Manager

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.

Sam Millner
Sam Millner
Technical Consultant & Project Manager

We think you'll also enjoy

5 Brilliant Examples of User Experience Design

Good UX design is essential for a successful product - in this blog post we look at some examples that are nailing it.
Learn more

Tackling cyber security – a people problem?

Concerns about the security of IT infrastructure and applications have grown steadily year on year in this blog post we share insights from Bristol companies on the steps they take to protect their organisation’s reputation and day-to-day productivity.
Learn more

Do you need an extra pair of hands to deliver your 2022 technology plans?

If you are considering your options for delivering your ambitious 2022 plans then this blog post will help you identify when working with a partner could help and how to get the best out the relationship.  
Learn more

Subscribe to our newsletter

The latest news and industry insights, straight to your inbox