The first step (in solving problems) is the hardest

Continuing a very erratic series of blogs about problem solving, I want to describe a real life problem which I faced and how I solved it. The psychological aspects of this story are more interesting than the problem itself.

First, a little history. I have described before how my company is actually an amalgamation of two (if not three, four or even five) previously existing companies, each with its own specific product line and way of doing things. In terms of our ERP program, the two main companies each have their own environment; whilst many things are the same between those environments, certain things (some crucial) are different.

The Board of Directors decided that as from 01/01/13, we will all work in one environment. Such a decision was made a year ago as well, but was rescinded almost immediately. I've been working on creating a joint environment for the past month or so and have solutions for almost everything (MRP is going to be a real problem!). We decided that initially the development work would concern itself with changing what needed to be changed in order to allow the two companies to co-exist; only later would efforts be made to change those work habits which I consider to be worth improving.

On Monday, I was informed that the merging of the environments is to be postponed for another year and that I was to stop all development work (some of which was contracted out to our ERP consulting company). I would give equal odds that the joint environment will not be active on 01/01/14, but that's irrelevant.

One small issue in the merging process was that of part prices. Our ERP program allows two mutually incompatible approaches: supplier price lists or 'only one price per part'. Naturally, one company uses the price list approach whereas the other uses the 'one price' approach. As far as I am concerned, the price list approach is far superior, for it allows one to manage several prices for the same part (one price per supplier), to allow quantity discounts (one can define a price for amounts 1-249, a separate price for amounts 250-499 and a third price for 500+; these quantities are not fixed, so a second part might have one price for quantities under 1000 and a second price for quantities over 1000), and of course a proper history of prices is maintained. Tools exist for automatically updating the supplier's price list should prices be increased by a given percentage.

I can't really see any advantages in using the 'one price per part' approach, although I might be blinkered. I think that this approach's supporters would probably say that they only ever have one supplier per part and so don't need the flexibility. I think that they're wrong.

Anyway, we had to make a decision as to which approach would be used in the single environment and supplier price list was the choice. I had informed the people in the purchasing departments that use the single price approach that they would have to begin preparing themselves for the change before I knew that the project was abandoned (or postponed), so theoretically I could now tell them that they could continue working in the same manner to which they were accustomed. Whilst this might be considered a violation of the Prime Directive - don't change work habits which don't need to be changed - I considered this an opportunity too good to be missed.

I considered the problem of implementing the change. Each part has a defined purchase price and a designated supplier; my intention was to take this information and build price lists for each designated supplier. But when I tried to define a price list manually, the program informed me that I could not do so as the environment was working by part prices. When I tried to change the system variable which determined by which approach the program worked, I was told that I can't change the variable as there are extant part prices.

I felt that I was facing a chicken and egg problem - I need the part prices to create the price lists but I can't create price lists without deleting all the prices - and of course, if I do that, then I will have no prices with which to create the price lists!

How would you solve this problem?

After humming and hawing for a bit, I outlined a four stage strategy

1. Write a procedure which will take the existing prices and output them to an external file in a format which will be conducive to reading them back in again 
2. Delete the existing prices
3. Change the system variable
4. Create an interface program which will read the file back and create the price lists

The first three stages went smoothly, but I had difficulty in reading the data in the file, so much so that I was unable to create a single price list (there were also problems with the file itself, but that's irrelevant).

Of course, now I was left with a test database bereft of all the part prices so I couldn't continue with anything. My next step was to copy part prices from the production database into the test database - for around 16 thousand parts! Whilst this step would have taken about fifteen minutes in good circumstances, it transpired that there were at least thirty parts defined so badly that one is unable to update them. This caused my rate of copying to be severely compromised, taking maybe an hour to do so (and some part prices I didn't bother copying). 

Taking the time allowed me to think a little about what I was doing. I vowed that before doing anything else, I would copy the price parts to a separate location within the database so that I would be able to repeat the conversion process if things went wrong without having the same problem of manually copying  all those prices and getting stuck with problem parts. As it happened, there was a vacant field in the parts table which I could utilise for this purpose.

Then the penny dropped. The prices were stored in a field called purprice, and the system variable was checking whether there were any values greater than zero in this field. Copying the prices from purprice to a field (which I'll call savedprice) allowed me to zero purprice - and thus change the system variable - whilst maintaining the price in savedprice. I could write a procedure which would build the price lists from the saved prices. In other words:

1. Save price in new field and zero old price
2. Change system variable
3. Run procedure which reads the prices from the new field and creates supplier price lists

The program needed in stage 3 here was much simpler than the program required in the previous stage 1 and utilised an interface which already exists (no need to write a new interface program which proved problematic). I was able to write the program - and painfully debug it - in about an hour; converting the test database from one approach to the other took about fifteen minutes with a minimum of pain.

As I wrote at the beginning, I am more interested in how I arrived at the insight necessary to solve the problem and less in how I actually solved the problem. It has been my experience that the solution to many problems are simple to implement/code once a suitable approach has been adopted; the problem is finding the suitable approach.

I had so blinkered myself with the chicken and egg aspect that I couldn't see at first how the simple initial step of saving the required data (prices) in a different place would enable me to solve the problem. Cat Stevens once wrote a song called 'The first cut is the deepest' (initially recorded by P.P. Arnold and then possibly by Rod Stewart); in problem solving, I would say that 'The first step is the hardest'.

I noticed this same blinkering effect with people trying to find an optimum solution for the puzzle in which four people have to cross a river with a two person canoe. Those who started with the slowest person crossing first (Andrew and David in my example) had great difficulty in finding the optimum solution, whereas those who had the two fastest people (Andrew and Brian) had less difficulty.

The lesson to be learnt from this example is how to convert what seems to be a chicken and egg problem into something more simple. We'll see whether I've learnt anything the next time such a situation arises.