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

Understanding the Power of Digital Twins

We have been having lots of discussions about digital twins, a concept that's gaining significant traction. But what exactly are digital twins, and why are they causing such a stir?
Learn more

How to make 2024 the year you tackle the big talent shortage

As part of our series on practical business leadership advice for 2024, we’ve put together four steps you can take this year, to make your organisation more effective in its recruitment and retention, and to tackle another common issue: lack of diversity in the team
Learn more

Our investigation into the LastPass Security Incident

Here is our response to the recent news of a security incident at LastPass and our recommendations of pre-emptive actions to keep your data safe.
Learn more

Subscribe to our newsletter

The latest news and industry insights, straight to your inbox