The Department of Bright Ideas

At work, I introduced the concept of Engineering Changes (an existing module in Priority) several years ago in order to aid communication between sales (who sometimes sell products that have not yet been designed) and engineering (who have to design those products). Over the past few years, this chain has been extended to production, who have to manufacture those products. The traditional way of informing production was to print a copy of the product's blueprint and send it to production.

Someone - not me - suggested putting a terminal on the shop floor so that the workers could scan a bar code on the product, where this bar code contains the EC number. Magically the blueprint would appear on the computer's screen. This is fairly easy to do within Priority, as the bar code would lead directly to the EC record, where the path to the blueprint is stored. Then someone asked whether this could be done outside of Priority: my initial thought was that this would not be possible, as the required files are stored in a labyrinthic directory structure, where the target files are several directories deep. 

Then the penny dropped: I could write a program that receives a barcode for input then performs a recursive search starting with the top level directory in order to find the required file (displaying the file at the end is trivial). This would not be fast but at least it would work. Writing this recursive search was slightly tricky, as most of the examples that I found of recursive directory search simply create a list of all matching files as opposed to looking for one specific file. Another hurdle was that within one terminal directory there could be several files for the same EC - the most recent version is the target (coding this turned out to be much simpler than I originally thought).

So I wrote the program, going through several revisions, tested it at home with a much simpler directory structure and a barcode reader, then tested it at work. The program fulfils its requirements, although it's slow - because of the search.

Last night I was walking the dog when I had another bright idea: instead of constantly performing a recursive search for one target file, I could write a program that performs a complete recursive search, saving its results in a file, then update the current program to search the file (that is loaded into the program's memory via a stringlist). This of course is very fast. Should the target not be found in the stringlist, the original recursive search algorithm gets called; this would happen if a new EC has been created since the directory structure was last mapped.

Writing the mapping program was very easy, being based on the original program, but without the user interface and without the complications of looking for a specific file. I had trepidations about the amount of time that this program would required to create a complete map, but had I thought more deeply about it, I would have realised that it wouldn't require much more time than the current program required to find one file. True, I am 'pruning the search tree' the moment the target file is found, meaning that the original program doesn't have to search the entire directory tree, but I get the feeling that all the target files that are searched appear towards the end of the recursive search, so pruning doesn't save that much time.

But 'the proof is in the pudding': after writing the mapping program (and testing it at home), I ran it at work. The program required two or three minutes to build the directory tree! I had been thinking that it would take half an hour or more. Once I had the directory tree map/index, it was a simple matter to include this in the original program that now works very fast. It's not instantaneous, but I think that the slight delay is due to executing the PDF viewer.

Moral of the story: walking the dog provides one with time for unconstrained thought. As always, these bright ideas seem so simple in retrospect that makes me wonder why I don't think of them earlier. The reason is that we are curtailed, 'blinkered', restrained, in our thinking, and one has to get out of the work environment (by walking the dog) in order to free oneself of the restraints.

The third way, continued

I had another example of finding a third way solution today, although this one is more convoluted and it would be hard to explain. Again there was a situation where one 'side' wanted something whereas the other 'side' wanted something which was mutually incompatible. 

But I found a solution. This 'third way' stuff seems to be good. 

Thinking outside of the box: the third way

I want to document something which happened to me last week, not in order to show how wonderful I am or to pat myself on the back, but in an attempt to learn something and internalise a way of thinking, a way to solve one type of problem.

First of all, the background. Priority has a type of data known as 'business opportunities', otherwise known as presales. The salespeople open these opportunities, recording the customer and the approximate value of the opportunity. When the opportunity reaches the status where the customer sends in his order, automatically a project is opened from the opportunity [this is a non-standard enhancement that I added] and the scope of management changes. After all, a customer order is a 100% promise of further work and so we should know how much work is going on the books and when it is going to be produced.

Priority has two kinds of customers: temporary and permanent. A permanent customer is what we normally think of as a customer: there are orders, delivery notes, invoices and receipts. Naturally a permanent customer has an account in the financial 'books'. A temporary customer, on the other hand, is the complete opposite: no orders and no financial record. About the only data types that are suitable for temporary customers are business opportunities and price quotations; we can hand out price quotations like chocolates, but only a certain percentage will turn into orders and so we don't want to open permanent customers for those that don't. A temporary customer still has all the contact details so in a sense, we're not losing anything.

This is where we have the conflict: what happens when a business opportunity for a temporary customer reaches the status which causes a project to be opened automatically? It's not possible to open a project for a temporary customer, and so when this happens, I get a phone call from a salesperson saying that no project has been opened - or worse, the salesperson opens the project manually (after the customer has been converted from temporary to permanent) and doesn't fill in key fields correctly.

My knee-jerk reaction when I heard about this was to prevent opportunities for temporary customers reaching the critical status. The sales people were up in arms, for apart from anything else, they had no way of signalling to the accounts department that the customer status needed to be changed - this was done by a message being sent when the opportunity reached the critical status. 

So we had a situation with two mutually opposite requirements, with no real way of solving the problem. A third option is required. My usual technique at times like these is to go for a walk; getting away from the computer generally allows a solution to pop up. It's also good to get some exercise, especially in these cold and rainy days.  

After walking up and down the warehouse aisles a few times, a solution did appear. Somehow it must be signalled that the automatic opening of the project must be delayed until the customer becomes permanent. My first instinct was to save the opportunity number in a table and then have a program traverse this table periodically. A few more aisles gave me a better solution: there is no need for a table; instead a flag can be set in the opportunity. Once I had this idea, it wasn't difficult to write the program that opens the project; most of the code was based on the original code that executes when the opportunity changes status.

I was disappointed in that none of the sales staff appreciated - or even understood - the solution. On two successive days, different sales staff contacted me to say that no project had been opened; I told them to wait a little and that soon they would receive an email saying that a project had been opened for a given opportunity. As I had written this in an explanatory email, I can conclude that either people don't read my emails (quite probably) or that they don't understand them. After all, sales people have to sell (they have skills that I certainly don't), and in the same way that I don't understand the ins and outs of selling and of the finer points of our products, they don't understand anything of my world and the constraints under which I have to work. 

But we're not here to talk about how the idea was executed in Priority, but rather how to find a solution when there seem to be two mutually opposite requirements. Step back from the problem and try to find a third way! Don't fixate on those requirements; almost certainly there will be a way of getting around them.

Never underestimate the power of a hot shower

No, this isn't going to be about cleanliness but about solving problems.

I've been very busy the past week and will be in the coming week, traveling almost every day in order to attend meetings in Tel Aviv, Haifa and Karmiel. Today, I have a 'day off' - a chance to work on my own and complete various tasks.

Yesterday, I wrote very rapidly a few reports in-between meetings; the reports didn't work as expected but I didn't have the time to figure out where the mistakes lay. I showered in the evening before going to bed, and as usual my mind went over the events of the day. Scientists say that hot showers cause the body to relax and create alpha waves in the brain; it's the alpha waves which promote problem solving. So there in the shower, I realised that one report was checking orders which had reached a certain status - but in my rush, I had forgotten to define what that status was. Sure enough, this morning I defined the status - and hey presto!, the report produces data (very important data, I might point out).

A similar activity is walking the dog (or mowing the lawn); I'm sure I've written about this before. This morning, at about 5:45 am, the answer to another problematic report popped into my head.

Abstracting these activities, the important thing is to get away from the computer and think things through without typing!

Not playing by the rules

Today's story of frustration, told in excruciating detail, is about a problem which I faced in Priority and how it was solved. If this is not your cup of tea, then don't read on....

The story starts about a month ago, when one of the people in the company where I consult requested a minor improvement. They have defined a default warehouse for a small group of parts (each part has a warehouse specified) and they want that whenever such a part appears in a supplier's delivery note, the part will be entered into that warehouse's inventory. It often happens that parts get assigned to the wrong warehouse and my addition will correct that tendency.

Adding developments to screens in Priority is an activity which I dislike as it is sometimes very difficult to get what seems to be the correct code to work. Also, changes to screens affect everyone who is working, so frequently such development work has to be performed during down time. Theoretically, the screens are well-defined, having several triggers (or event handlers) which make development easy (or less hard). In a sense, this is very similar to programming in Delphi or any of the modern languages developed for Windows: one inserts one's own code which will be executed when the defined event occurs.

So I thought to myself that the event which interests me is when the part is entered into a row of the delivery note, or in Priority-speak, the post-insert trigger of the form INVOICEITEMS_P. The additional trigger is simple (I won't go into details here), but when I added it in the appropriate place, nothing happened when I tested it. I tried moving the code to a few other triggers but it was still not being executed. I then dropped the subject, knowing that the inherent frustration (the gumption trap) would not lead me to a solution; a fresh pair of eyes were needed.

A few days ago, I revived the subject. This time, I started by reading the standard code to see what happens when an order is inserted into a delivery note (this is what causes the lines to be added). The parent form (DOCUMENTS_P) has two triggers which interest me: post-insert and post-update; in both cases they call common code (BUF3) which reads the connected order and inserts the data into the delivery note lines.

I was astounded when I discovered that this code directly inserts the data into the TRANSORDERS table instead of employing an interface to do so. This is in direct contravenence to the instructions in the SDK (the developers' bible) which states Important! The records stored in Priority tables are always inserted into those tables via Priority forms. Never insert records directly into Priority tables, as this will bypass the integrity checks and other actions defined in the form triggers (p. 122). This direct insertion explains why my original code - based on the post-insert trigger of TRANSORDERS - was never called.

OK, I thought to myself: if you're not going to play by the rules, then neither am I. If you're going to insert data directly into the TRANSORDERS table, then I am going to update that same data directly. Thus I needed to write common code which would be called from private post-insert and post-update triggers of the parent form, in a similar manner to the standard code. Again, writing the code itself was very simple, but testing was another matter. I could see (by inserting debugging statements) that the code was being called, but the query which was supposed to return the rows in TRANSORDERS was returning empty every time. What could the problem be?

I took the dog out for a walk - always a good debugging technique - and realised that the computer was not lying (it never does): there really were no rows to be returned. In other words, my trigger was executing before the standard trigger were inserted the rows. How could this be? Standard triggers always execute before personal triggers!

It turns out that I had misunderstood that final statement. Looking in the SDK again, I come across this nugget: Standard and custom triggers are sorted alphabetically, so you should name your own triggers accordingly. For example, to run your own trigger after a standard POST-INSERT trigger, use POST-INSERT_AXXX or ZXXX_POST-INSERT (where XXX is part of the prefix you normally use for this customer). In other words, because my trigger was called ATLN_POST-INSERT, it comes before POST-INSERT in alphabetical order and so was executing first. I had confused 'standard triggers' with 'built-in triggers'.

So it was a simple matter to rename my triggers to ZTLN_POST-INSERT and ZTLN_POST-UPDATE, and voila! The code worked first time!

What I have learnt from all those frustrating hours (I reckon I spent about five hours on what should have been a simple exercise; I'll bill an hour and a half)?

1. All triggers execute in alphabetical order. 
2. Always read the source code to see how the program achieves something before trying to extend that functionality

The first lesson will be taken to heart, but I suspect that I will forget the second until I come across it again.

I'll never get those wasted hours back

Several years ago, the Occupational Psychologist and I developed a program in which the user would be presented with a variety of occupations (over 100) and would choose which appeal and which don't. On the basis of the answers, we performed some psychological voodoo and then presented a list of occupations to which the user would be suited. We still use this program in our 'laboratory' setting.

We later thought that this program could be used as an alternative revenue stream: we would convert it from a native Windows program to an interactive Internet program, put it on the OP's site and then invite youngsters to 'take the exam', paying a small charge for the privilege. As this kind of programming is not in my CV, we turned it over to an external programmer, the OP's eldest son (this is not a case of nepotism: he works for Intel, in case anyone is doubting his competence). We arranged the online exam so that one would first have to pay via PayPal, then using the PayPal receipt number as a serial number, the youngster would 'take the exam'; the output file would be sent by email to a certain address. My part in this was to parse the letter that we received from PayPal, notifying us of the paid user, then download the email holding the output file, perform the voodoo then send the results back via email. A non-registered user could still take the exam but the results would not be returned. 

[For any brave souls interested, here is the link to the exam. But beware: it's all in Hebrew]

Unfortunately this did not turn out to be the revenue stream that we had envisaged so the OP made the brave decision to turn the online exam into freeware (but still no one has heard of it...). I had to change my program slightly so that it would ignore anything which was connected with serial numbers etc. This modification was quite simple but then I came across something else which needed my attention.

The program sent its results by email: it was using code which used Outlook as the sending mechanism. Unfortunately, this required that Outlook run continuously on the 'server' which had caused problems in the past. Also, considering the programming work which I had been doing in the past two months, it was time to free the program from the shackles of the proprietary Outlook and move to the freeware of GMail. 

Whilst it didn't take long to perform the conversion, the testing took hours and seemed inconsistent. Sometimes the program would send an email but more often that not, it would stall in the portion of the program which actually sends the email, with no clues being given as to the source of the problem. After knocking my head against a brick wall and coming exceeding close to giving up, the answer suddenly presented itself: when sending mail via GMail, there are two security DLLs which need to be present in the same directory as the program. As soon as I moved those DLLs into the correct directory, the program worked (it had worked before because sometimes I tested the program in a directory which contained those DLLs)!

This is what I have referred to before as the Gumption Trap; once again I fell into its jaws.

But what this really reminds me of is a thread in Nick Hornby's "Juliet, Naked", which I last read a few weeks ago. The book starts off with a couple, Annie and Duncan, making a pilgrimage to America in order to visit places which supposedly had a strong connection to Tucker Crowe, the fictional recluse musician who made the eponymous record. When they come back to their dreary British seaside town, Annie and Duncan separate; Annie makes a calculation that she wasted fifteen years with Duncan and that no one was going to give her those years back.

This is how I feel (on a slightly different level, of course): I wasted a few hours and no one is going to give me them back. I could have been doing something else more productive, like studying for my doctorate....

The simplest ideas are normally the best

The manager of one of the factories in my company asked me several months ago whether it was possible to print the production drawings of parts whilst printing work orders for those same parts. I replied that it would be no problem to do this in principle, but for in order for this to work, the drawings have to be connected somehow to the parts.

Unfortunately, many of the parts produced in this factory are ad hoc parts which are created automatically by a configurator program, which builds (defines) new parts on the basis of existing parts. According to our tests, the configurator does not copy to the new part documents which are linked to the existing part. 

I wrote a program which performs this copying (the documents themselves are not copied, only links to them) but hooking this program up to the output of the configurator (which is an extremely unfathomable black box external program to our ERP program) proved to be problematic. It was very difficult to test the program under controlled circumstances and seemed to work only occasionally.

We were discussing this program's short comings the other day when I suggested taking a different approach to the problem. Every part has what the ERP calls a 'base product': a part which is representative of a class of products. I suggested that the documents be linked to these base products and then at the end of every day, a program would run automatically which would copy the documents from the base product to the parts which had been created that day which belonged to the base product (that's complicated to write but the idea is quite simple).

The following day, following another discussion on the subject, it suddenly struck me that we were still taking a complicated path by focusing on copying the links to the documents. What if the program which prints the work orders printed the documents linked to the base products of the parts in the work orders? This way, there is no need to copy anything anywhere. In order to implement this, I needed to add one clause (about six words) to the SQL statement which controls which documents are to be printed.

As always, the fundamental idea in problem solving applies: the best solution will come once the core problem has been identified. I had been concentrating on copying the links when I should have been concentrating on printing the documents, which is a simpler solution by an order of magnitude. The simplest ideas are normally the best.

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.

The gumption trap

Thomas Edison famously remarked that "invention is 1% inspiration and 99% perspiration". Unfortunately, it's the same regarding debugging.

I bought a wide LED screen for the children's computer and on Sunday connected it up. The screen didn't work. After eventually finding the power switch and turning the screen on, it still didn't work. As it was fairly late and I was very tired, I decided not to continue but to leave the problem for another day.

On Monday evening, I started again with the screen and verified that it wasn't displaying a picture. I then disconnected it from the children's computer and connected it to mine - voila! There is  a picture on screen, so we know that there is not a problem with the screen itself. I moved the screen back to the other computer - no picture. I connected my screen to their computer - no picture. I even swapped the video cable; the new screen's video cable worked fine on my computer whereas my video cable didn't work on their computer.

At this point I did the sensible thing and took the dog for a walk. Getting out of the house - or more importantly, getting away from the problem - helps me see more clearly and theorise what to do. Obviously it's a problem with the computer's video card.

When I got back (and after eating something: food also helps the brain), I opened up the computer and verified that the screen card was sitting properly in its slot. I also managed to cut my thumb on the sharp edges of metal which have been cut to allow a fan to sit on the video card. I connected the video cable to the video card, connected the electricity - and saw a picture on the screen. I then disconnected the electricity and video cables, connected a disconnected wall fan inside the computer, put the side of the computer case back on, connected everything up - and no picture appears on the screen.

The wall fan was making a large amount of noise so I disconnected the computer again, opened it up, disconnected the wall fan - and then I had the epiphany which I should have had much earlier. I had been connecting the video cable to the onboard video connector instead of to the external video card! Once this was noted, I closed the computer up again, reconnected everything properly - including the video cable - and could begin working.

Well, not quite. There was an icon on the screen saying that the network card was disconnected. I checked the connections - wall first, computer second - before the inevitable epiphany occurred again: I had connected the network cable to the internal ethernet socket, which had been disabled, instead of to the external ethernet socket. Easily solved.

Conclusions:

1. Write on the computer case to use the external video and ethernet sockets in the future
2. Take the dog for a walk more frequently

This morning, whilst thinking over this affair, I remember "Zen and the art of motorcycle maintenance" which discusses what I would term 'debugging' and the importance of the 'gumption trap'. When debugging, one often gets into a mental state which leads in one direction whilst ignoring other directions (one of which will be the correct direction). Thus one wastes a great deal of time whilst increasing one's frustration as one goes in the wrong direction.

I am about to order a book which discusses this issue more thoroughly - "How we know what isn't so: fallibility of human reason in everyday life", by Thomas Gilovich. Ignore the price which is going to be displayed by the Amazon link: British Amazon is selling the book for 6.66 GBP but charging something like 8.25 GBP postage, whereas the Book Depository will sell me the book for 10.94 GBP including postage. Why pay 36% more?

Here's the blurb of the book: "When can we trust what we believe - that "teams and players have winning streaks", that "flattery works", or that "the more people who agree, the more likely they are to be right" - and when are such beliefs suspect? Thomas Gilovich offers a guide to the fallacy of the obvious in everyday life. Illustrating his points with examples, and supporting them with the latest research findings, he documents the cognitive, social and motivational processes that distort our thoughts, beliefs, judgements and decisions. In a rapidly changing world, the biases and stereotypes that help us process an overload of complex information inevitably distort what we would like to believe is reality. Awareness of our propensity to make these systematic errors, Gilovich argues, is the first step to more effective analysis and action." 

I look forward to reading this book.