Meta-blogging

As we approach the end of the civilian year, many people publish their summation of the year past, be it about music, books, life or everything. I've only bought three or four discs this year and only one book (excluding a few DBA textbooks), so I don't have much to write about in those departments. All the interesting things have been noted in my blog.

Whilst it is easy to see how many blog entries I have written in a given month or a year, and whilst it is easy to see all the entries which I have classified with a given tag, it's not possible (at least, as far as I know) to get any form of detail about the popular subjects this year or over the past 100 blog entries. I wrote about this in April when I was 'reduced' to counting by hand entries per tag over a given range. I was thinking about this the other day and realised that the only solution would be to write a small database program (I too suffer from Maslow's hammer: every problem looks like a database to me) which would store the id, date and name of every entry along with its related tags, thus allowing me to write SQL statements which would query this database and reveal patterns.

Actually, there's no real way of knowing an entry's id number: I can see how many blogs I have written but none are identified with a number. So I started off with the most recent entry (whose number I know) then worked backwards. It was gratifying to note that entry 700 did indeed celebrate 700 blogs. So far, I have recorded just under 200 entries, going back to entry 600 in the summer of 2013. I'm not sure whether I will include the first 600 entries as they won't help me in queries such as 'the most popular tags in 2014' or 'tags for entries 701-800'. I probably will add them at some stage, because that's the sort of person that I am, but there's no real value in doing so.

The program is very basic (for example, there's no way to edit a mis-spelled tag) but also includes all of the features which I have developed over the past year or so, including a parameters screen and adding a record without retrieving the entire database. One nice bit of functionality is showing the number of articles per tag: this is done in HTML so that I can copy the data directly to here. Using a web browser is slower than a grid but it's a neat hack.

So ... assuming that I will not write another blog entry in the coming days, here are some statistical data about my blog in 2014: 125 entries,  51 tags. 

22 of those tags were used only once which tends to imply that they have been used more in previous years. At the moment, I don't have a way of actually showing this (I would need to write a query which sums tag usage according to years, but then I would also have to input more entries), but it seems like only one tag is new - Goldfrapp.

Here is the table of entries per tag (minimum three entries/tag):

Tag	Count
DBA	38
Holiday	16
Personal	15
Health	12
ERP	10
Programming	9
Sorrento	9
Sicily	7
Cooking	6
Films	6
Excel	5
Israel	5
Psychology	5
TV series	5
Kindle	4
Carole Bayer Sager	3
DCI Banks	3
Delphi	3
Mobile phone	3
Obituary	3

I would have to say that there are no real surprises about the leading tags, although I would have thought that there would have been more entries tagged ERP. The above shows that I have spent less time programming this year - or at least, in developing new programming techniques that I have decided to blog about. Let us not forget that the table reflects only activities about which I have written: I work with ERP every day, I program and cook every week, but this is not reflected in the above. I only wrote about the mobile phone because I received a new one this year and I only wrote about Carole Bayer Sager because I purchased her double disk.

Sebaceous cyst and other health issues

At least a year ago, my wife noticed a growth on my back. I showed it to my skin doctor during my annual check-up; she noted its size in her notes but nothing more was done. In August, I had this year's check-up and again the doctor measured the size of this growth. As it had grown by at least 25%, she gave me a referral to the plastic surgeon who removes these growths.

When eventually I saw the surgeon (appointments tend to be two months in advance), he wasn't too sure exactly at what he was looking so sent me off for an ultrasound scan. I had this a few days later but again I had to wait five weeks to see the surgeon again. Fortunately, removing the growth is not urgent. The ultrasound report said that the growth was a sebaceous cyst; such growths can be ignored as long as they don't become infected. Although this growth appeared not to be infected, it was still growing and so the surgeon indicated that I should have it removed as soon as possible. It seems that the surgeon can't be bothered with benign growths (although he does remove BCCs) and sent me off to someone else to have it removed. Fortunately, I was able to make an appointment for excision within a few days.

I had the growth removed yesterday. As I always say after such minor procedures, it's easier than going to the dentist. Normally there are no side effects of these procedures (save having stitches removed after ten days), but yesterday I was told to lie down for at least two hours in order to help the hole in my back to heal. After those two hours, I felt very weak and went to bed for a few more hours' sleep (I hadn't slept well overnight due to a stomach ache).

I was allowed to remove the bandage that same evening; sleeping wasn't too comfortable and I am aware of a pain in my back if I stretch or something knocks into my back. Here's a picture of my back for all horror aficionados (fortunately, I can't see this myself).

I've been having problems in walking the past week and a half: pains appear in my shins after a kilometre. My wife told me of a shop which sells 'healthy shoes and accessories', so we went there to see whether the problem was my shoes (now two years old), my shins or both. Unfortunately, because of the Chanuka holiday, the shop closed early at 4pm, so I had to wait until Wednesday before I could get there before closing time. The salesman looked at my shoes and how I walked: he suggested that first I buy a new pair of walking shoes (450 NIS as opposed to the 600 which I paid two years ago) and see how I go. The previous shoes were good and gave more support as opposed to absorbing shocks; the new shoes are lighter, offer less support but more shock absorbtion. As a result, I walked 5.1 km that evening in 46:39 minutes, burning 405 KCal (good).

I wasn't able to walk either on Thursday night (came home very late from work and didn't feel well) nor yesterday (operation in the morning; went out for dinner to celebrate wife's birthday) although I hope to be able to walk tonight. As a result of this lack of physical exercise, my weight increased slightly from 78.1 to 78.4 kg. I very much want to get it below 78kg but haven't been able to.

Interfacing with Priority from an external program

In or around August this year, my company commenced contact with an independent contractor who is the representative of the Imos program in Israel. This program is intended for woodwork shops, but like others of its ilk, it considers itself to contain all the functionality necessary for such a shop and so has difficulty in interfacing with external programs, especially ERP.

After several months of waiting, I finally received a file yesterday which contains enough information to build an order but also to create bills of materials for the parts contained within the order. My job was to read the file and import the data into Priority.

My first problem was that the file was in xlsx format whereas Priority likes tab delimited files. I had done some preliminary work on this a few months ago, writing a tool which supposedly would save the xlsx as tab, but this program encountered problems. Trying to save an excel file as text brings up messages such as 'do you want to save the changes you made to x.txt' (what changes? I just saved the file). When I answer yes, then I'm asked whether I want to replace the existing x.txt file. What is this? How is one supposed to answer?? It was clear to me that I wanted to save these awkward questions from the user.

When I saved the file manually as tab delimited and imported it with the temporary interface structure, I discovered that I had another problem. For some reason, there were lines which had fields separated by two tabs and as a result, the line wasn't read correctly. I also discovered (at a later stage) that some fields had quotation marks appended before and after the data, and that some fields even had a stray carriage return at their end.

So I was forced to write a program in Delphi which would read the raw xlsx file (by means of automation), correct the above problems then output a tab delimited file with the name of my choice. It will be interesting to see whether these faults occur in future files. I have written the program such that nothing will happen if these faults do not exist.

Once I had got past all of these hurdles, I was able to import the data and store it in a temporary table within Priority. As opposed to previous procedures which I have written to import data, I decided to use a different technique of first importing the data then building files within Priority which would be used to create the final data. This was easy as I have done this before, albeit with a slight twist.

I didn't bother with building the bills of material as this will require a fair amount of fiddling and imagination and my stores had run dry yesterday. Still, I was very pleased with my output. A meeting will be held this afternoon with the contractor, the wood factory manager and assorted others. It will be nice to show some progress after months in which it seems that nothing has happened.

Writing a paper on spreadsheet research

Following my earlier blog entry about the spreadsheet mindset, I decided to write to Dr Felienne Hermans (she of the Enron spreadsheets), asking for suggestions. Although she didn't help directly with search terms, she suggested that we have a conversation ... and so we did on Friday morning.

The more important part of our conversation was that she is hosting a workshop which will take place in Florence, Italy, in May; the workshop will last for one day but is part of a week-long conference. Who can turn down the idea of a long weekend in Florence which will culminate in a workshop (under the banner of the "2nd Workshop on Software Engineering Methods in Spreadsheets")? We agreed that I should write a two page position paper and present it at this workshop.

I have already written a short precis of my research which is intended for the ICESAL conference which will take place in Greece at the end of June. Felienne wants something different, which is apparent from the name: a position paper as opposed to a research in progress paper. An advantage of a position paper is that it doesn't have to be based much on the literature; instead, I can present my own ideas, which as I know don't seem to appear anywhere in the literature. A second advantage, which only became clear to me this morning, is concerned with length. The paper is limited to two pages; references take up valuable space in those two pages and thus the fewer the references, the more space for my ideas.

On Friday evening, I cannibalised the paper intended for ICESAL; whilst the result was ok, it wasn't particularly what I wanted. Yesterday I went over the paper and edited it: some sentences were removed, some added and the order of paragraphs changed, making the whole thing tighter. I also managed to lose two references, so the length shrank from two and a half pages to two. I have sent this version to Felienne who is prepared to edit the paper and make it suitable for publication in the proceedings of the workshop.

This morning I located the proceedings of last year's workshop; whilst the contents are not necessarily interesting, the style of the two page articles is. I realised that I need to write a completely new opening paragraph; my problem (as always) is that I am too polite and I need to be controversial, or at least provocative.

The opening sentences need to be something like: The majority of spreadsheet research is concerned with the data contained within the spreadsheet. Little attention is paid to the source of those data. I'm sure that I can continue with this new orientation, utilising the material which I have already written whilst changing its focus.

This brings to mind my music. It has happened more than once over the past few years that I have worked hard on creating a track only to realise after finishing it that it is too solid and that something more inventive is required. I am also used to working on my own and applying my own editorial choices and tastes whereas here I am working with some in the Netherlands who is about to go on a holiday for at least a week (she mentioned something about skiing in France, apart from the public holidays in Europe).

Seasonal greetings

Translated into English:
DON'T FORGET
a doughnut ...
a minute in the mouth
an hour in the stomach
a year in the bottom

It is the time of year for eating doughnuts. Once, I was only too happy to eat every doughnut which crossed my path during Chanuka, but those days are long ago and now I limit myself to only one during the entire eight days.

My wife bought some doughnuts the other day from the supermarket; she offered me one but I declined, accepting only a small piece. I did not like the dough and decided not to repeat the experience.

At work, I have just been 'delivered' a holiday plate with a few sweets and a doughnut. The smell is enticing even though I imagine that I won't actually enjoy eating the doughnut. 

What is the point of this badly written blog entry? One part of our brain (the mid-brain) is automatically attracted to the sweetness and the fresh baking smells emanating from the doughnut, whereas another part (the cortex) knows that I don't enjoy the taste and that I don't need the empty calories.

Happy holidays, everyone!

A feral system under my nose

A great deal of my working time over the past week and a half has been spent with the overseas sales department of my company. I was called in to help with the posting of an invoice from an overseas vendor (goods were supplied for this department) and once I started work on this, I discovered that I was opening something akin to a can of worms. After several preliminary meetings, I wrote a new protocol for this department, then spent another day answering questions which the protocol had raised.

It occurred to me over the weekend that this department was using a feral system, which is why I am writing about it here. The first stage in the process is the receipt of a purchase order from the customer; if the customer is ordering products which we manufacture, then a 'proper' customer order is entered into Priority and everything works correctly. The problems start when the customer orders products which we don't manufacture but rather purchase from another vendor who is also abroad. These goods are sent directly from country A to country B without 'setting foot' here, and because of this, the overseas sales department didn't invest in a proper process. It may be that there have only been one or two cases of orders of this type but their frequency seems to be increasing.

In the above case, the customer's "purchase order" would frequently be a file created by a design program which we - and the vendors - also use. Instead of running a program to import this file into Priority and in so creating a detailed customer order, a one line customer order was created, so that the department could show that it is selling goods. In a similar manner, this file would then be sent to the vendor, with no purchase order being created in Priority.

The problems started with the receipt of an invoice from the vendor: there is no corresponding purchase order thus there is no way of knowing (at least, to the accounts department) whether the invoice is charging us for what we ordered. Apparently some form of invoice is issued to the customer (so that they can pay), but again this is murky and at one stage I was told that the customer was supplied with "an invoice created in Excel".

One aspect of ERP programs is frequently forgotten: the use of such a program improves communications between different departments, especially if those departments are geographically dispersed. The overseas sales department tended to see these orders as a private affair and so reduced the orders' visibility to a minimum; not because they didn't want anyone to see but because they didn't see any need to make the process visible and transparent (isn't that a contradiction?).

In walks Dr ERP (i.e. me) and says that from now on, the file which the customer supplied has to be imported into Priority as a detailed customer order. As the resulting order frequently contains general parts (because the program which does the importing doesn't recognise the parts), I wrote a program which would create parts and update the order with them. I also went two stage further and both connected those parts to a vendor and then added the parts to the vendor's price list. These latter two stages may have to be separated from the first for various reasons. After running this program, we now have a customer order with distinct items; from this order we can create automatically a purchase order which is then sent to the vendor, along with the original design file.

It's hard for me to understand completely the thinking which exists in the overseas sales department; it starts with a basic lack of knowledge of Priority and continues with a lack of awareness of other people/functions within the company. Hopefully these problems have been addressed.

As it happens, yesterday I was also told about another feral system. One department works with external teams who perform the actual work at customer sites and the person in charge was maintaining a list of teams and jobs to be performed in a spreadsheet. The CEO wanted to receive a list of orders which would be installed in the coming week along with their value. As long as half of the data is being maintained within Priority and half outside, the CEO's request can only be attained by no small amount of manual labour. My suggestion is to maintain the list of teams and jobs within Priority. As a result, we can easily know the value of the upcoming jobs; we can easily print a list of jobs for each team in the coming week and we can easily produce a 'sheet', a two dimensional table of teams and dates, showing what each team is doing on each date. It took me about fifteen minutes to develop a prototype for this system, including data entry and two of the three required reports.

The key word here is 'easily': once the data has been entered into Priority, the rest follows automatically. Using Excel means that whilst it might be slightly easier to enter data (and possibly the results can be displayed more clearly), it is much harder to extract data. The major problem as far as I am concerned is the mind-set of the manager who only knows Excel. 

The moral of these stories should be clear: when the only tool that one knows is Excel, then every problem looks as if it should be solved with a spreadsheet. There exist other and better tools for solving these problems.

---

Here is someone else's take on the topic of the spreadsheet mindset. This is the sort of concept that I want to introduce into the doctorate, but I can't find the correct terms for searching in Google Scholar. Hopefully I will write more on this shortly.

Television detectives

Nearly two years ago, I wrote about actress Ruth Gemmell and her appearances in television shows as a policewoman, every time at a different rank. Yesterday we were watching 'Casualty' (series 29, episode 10, 'Deadfall') when up pops a detective sergeant who looks extremely familiar! The credits demoted her to detective constable.

Looking at Ruth's credits at IMDB, I see that she has appeared in several other police related television programmes. Maybe this is typecasting?

[SO: 3711; 3,15,36

MPP: 574; 1,2,6]

What's happening in the kitchen

I bought a new frying pan on Friday. I have noticed that whenever I have tried to duplicate some of Jamie Oliver's recipes which include frying, the results are somewhat different. One of the differences would seem to be the pan, so I bought a 28cm non-stick pan which is very heavy. As it happens, I bought it in a shop where my wife is in their membership program; as her birthday falls at the end of this month, I received a 30% discount on the pan, which made the price very attractive.

When I arrived home, I began to prepare lunch, and as it was Friday, I was to prepare grilled fish. I turned on the grill in the oven - and nothing happened; the little red light did not come on. To be honest, I was not totally surprised as the oven has been making noises for at least  three months and this time it made no noise; we had called a technician a few months earlier and he said that there was nothing to do - as long as the oven works, it works, and when it stops working, we should buy a new one. How was I to cook the fish? In the new frying pan, of course! I covered the fish with flour, salt, pepper and parsley, put a tablespoon of olive oil in the pan and began frying. In ten minutes (as opposed to fifteen in the grill), I had two lovely pieces of fish. I think that normally I use more than a tablespoon of oil when grilling (to prevent the fish from sticking to the aluminium paper) so in frying, I actually used less oil than normal.

My wife was looking forward (once again) to roast chicken and roast potatoes for dinner. Fortunately, it would be just the two of us eating, as none of the children are home this weekend. There would be no problem in cooking the chicken - put it in the slow cooker for five hours, along with the potatoes. Although the food was not roasted, it was still cooked to perfection.

On Saturday, I wanted to try a recipe of Jamie's which involved frying deboned chicken thighs. He covered the meat with some mixture of spices which don't seem to be available here, so I used 'mixed cajun spices'. Towards the end of the cooking process, Jamie poured sweet chili sauce on the meat - so that it would caramelise - along with sesame seeds. I did this too but the sauce didn't caramelise. The chicken was very tasty but the cajun spices were a big mistake. I will have to consider which spices to use when I try this again - probably salt, pepper, cumin and cinnamon. I may marinade the meat in the chili sauce.

The next task is to replace the oven.

My army service - part four

By the end of basic training, five months in the army, I had barely ever seen an officer. For various reasons, my platoon commanders until now had been staff sergeants (although the company commander was a first lieutenant), so the prospect of having an interview with a colonel was daunting to say the least. I found the correct office, knocked on the door, entered then saluted. The colonel said that they don't bother with saluting.

This interview was in retrospect hilarious. I was telling the colonel - who was in charge of one of the departments of the Medical Corps - what I had done in university whereas the colonel was trying to think of places where he could use me. Eventually we hit upon working the laboratory which checks the quality of medicines, and I think that we agreed on a starting date. 

I don't know how this interview actually came about. I don't know whether it was the result of my signing the opt-out form six weeks previously or whether it was due to all the interviews and attempts to find something to do prior to be inducted into the army. To be honest, I didn't really care.

I had at least one week of holiday from the army; I remember that the fighting soldiers had to report to their new post a week after basic training but that some of us had two weeks' holiday. I seem to recall that I fell into the cracks. As it happens, it didn't make any difference as a few days after this tumultuous interview, I fell ill again with tonsillitis and had to be hospitalised in an army hospital. I was asked what my unit was and found it hard to answer - I had left one unit and was about to join another (the Medical Corps HQ),  although this posting was still unofficial.

When I left the hospital after about a week, I then spent another week trying to get out of the Nahal. I went one day to their HQ in Yaffo and was ignored, so I came home. The next day, I went again (each time carrying my army pack) and maybe made some headway. Possibly on the third day I was told that my medical file was with the unit where I should have been, who were stationed somewhere between Jerusalem and Hebron. I went there the following day and eventually obtained my documents. Probably I went back to Yaffo on yet another day with these in order to receive my final blessing and absolution; I then took yet another bus to the outskirts of Tel Aviv where the Medical Corps had its HQ. Let us not forget that this was in the middle of summer and I was carrying around a heavy kitbag. This period of limbo might thus have lasted five days.

Once in the MCHQ, I was shown to the Chief Petty Officer who set me up with bus tickets, told me when I had to appear for parade and other petty, army, details (there is a reason why his rank was Chief Petty Officer) and sent me to the laboratory where I was to work. This actually was outside of the army base and situated in an old hut in the grounds of Tel HaShomer hospital - more walking and shlepping my kit bag.

When I entered the laboratory, I took the people there by surprise as they had been expecting me a few weeks previously and had concluded that the delay meant that I wasn't coming. Once in, I was treated from now on as a human being. The fact that the laboratory was outside of the camp meant that there was a distinctly non-military bearing to our work, and as an academic, I was afforded a little respect.

I spent the rest of my compulsory army service - a year - in this laboratory; for several years, I also did my reserve duty here. It was a home away from home, and aside from the occasional military demands, was quite pleasant. There was a time when I was considering to stay on in the professional army - but going back to the problem with which I started my army career, the job called for a pharmacist and I had a degree in food science. So no professional career in the Medical Corps for me.

About two months before the end of my service, I spent a night on base as duty sergeant. When I was awoken for my shift at midnight, I felt rather strange, and this feeling accompanied me throughout the night. In the morning I asked to go on 'medical parade'; I sat in the doctor's office and shivered. I was diagnosed once again with tonsillitis and taken - by ambulance - to the same hospital where I had been hospitalised a year previously. As this was in the days before mobile telephones - and even normal telephones were in short supply - no one on my kibbutz actually knew where I was. I slept for at least a day as I was very feverish, but when I awoke I was lucid. Somehow I managed to find a payphone and inform whomever needed to know where I was.

I spent a few days in the military hospital until my condition improved; at this stage, it was discovered that I had blood in my urine - or to use the medical term, microhaematuria - and was transferred to a nearby public hospital. Again, no one at home knew about this. I spent another few days in this hospital, doing nothing and not being treated. On Friday morning, I asked whether I could go home for the weekend and was given permission to do so. On Sunday morning, I returned and was discharged; I was also given a week's medical 'holiday'. When this week finished, I reported to my base only to discover that I had been posted as AWOL! Apparently I should have gone there straight after leaving hospital in order to show them my authorisation to spend a week at home.

This microhaematuria problem continued for several years; I was registered at an outpatients clinic at my local hospital and used to visit once a year. One year I was about to undergo a biopsy from my kidneys, to see whether there was any damage; I was injected with iodine which would serve as a tracer so that the doctors could see where to probe. Unfortunately, I had a reaction to the iodine and the procedure was cancelled. About a year later, the consultant 'gave me an ultimatum': one more attack of tonsillitis and they would be removed. The tonsils must have listened for I haven't suffered from tonsillitis since (although I still occasionally suffer from pharyngitis and sometimes have a haematuria if my temperature is too high).

My army service - part three

The next stage in my army service was a 'professional' course. Some members of my platoon were sent to a radio technician's course, some were sent to a medical technician's course and some were sent elsewhere altogether (for example, one person in my tent went to the military police and another was sent to the education corps). I was fortunate enough to be sent to the medical technician's course. I don't remember now whether this was down to good luck (something which is in extremely short supply in the army) or whether we were asked our preferences and someone actually listened. However this came about, I was to spend the next month of my illustrious army career in a huge base not too far from where I lived.

Here we lived in an old building - the entire base had been a British army camp during the second world war - and had only a small amount of army bureaucracy with which to deal. The first two weeks were spent learning first aid, how to pack bandages and how to pack a medical kit to be taken into the battle field. This was, at least, fairly interesting and the lack of discipline (at least, compared to basic training) helped me feel more normal.

A friend of mine, who had emigrated a year earlier than me and so had started his army service the year before, had also taken this course. Following army regulations, he had to spend the next six months with his army group, who were manning an army base somewhere in the far north; he was working in the dining room of this base. Once those six months were finished, he would spend his final six months being a medical technician (the entire Nachal service was based on six month sections).

The moral of this story was not lost on me and after two weeks of the medical course, I had an epiphany: I could sign a certain form which would get me out of the Nachal and into the general army. As I was training to be a medical technician, I would spend the remainder of my army service (over a year, but never mind) as a technician, probably in this base which was not too far from where I lived. I signed the form, but nothing seemed to happen. This epiphany very much changed my general attitude and I became much more happy as opposed to the resigned and dulled person that I had become.

When the course finished after a month, we were sent back to our previous training camp in order to join our fitter comrades for a final month of basic training. There were three weeks which had to be filled in somehow, and I managed to wangle myself a job in the camp infirmary. As I wasn't a field medic, I wasn't allowed to treat anybody, but I found enough jobs to keep myself busy and out of sight. I slept in a room (!) along with a few other people, went home on the weekend, ate in the cadre dining room (fried eggs for breakfast) and even received a few mid-week passes for an evening/night off base.

When this regrettably short period finished, I rejoined the trainees and started sleeping in a tent again. We spent most of the next week learning how to march around the parade ground. The weather was infernally hot and my feet hurt a great deal, so I used my contacts to obtain some tubular bandaging which I used to wear as an extra sock; this cut down on the pain.

The final parade came and went and then we were awaiting orders for the next stage in our army careers. I can remember the place clearly and I can remember the time (we had just cleaned everything up) but I can't remember whether this incident took place after the final parade or before I went to the medical technician training - one time seems too early and one time seems too late. Anyway, whenever it was (and logic seems to dictate that it was at the end of the complete basic training, around June 1980), I was told that I was to report the next morning to the office of a high ranking officer in the chief medical officer's headquarters near Tel Aviv for an interview.

My army service - part two

After two weeks in pre-basic training, we set off for our first forced march. Although this seemed to go on forever, we probably only ran five kilometres. I had already discovered that I wasn't the strongest runner in the platoon (far from it!) and the only way that I could keep up would be to start at the front and stay there for as long as possible; this way I might not be the last to finish. 

In those days (and possibly still), there was something called 'water discipline': in practical terms, this means that we had to run around with two (silent) full water bottles tucked into our battle dress but never drink from them. There's a scene in the first episode of 'Band of Brothers' which demonstrates this: the soldiers go on a run, then are told to empty a water bottle on the ground. Someone's bottle finishes way before anyone else's and he gets puts on a charge - because he had drunk from his bottle even though he was ordered not to. When we arrived at the half-way spot, we were told to drink one complete water bottle; I was nearly sick from this.

Running back to the base, I was falling further and further behind. At one stage, a few fellow soldiers were ordered to support me and help me run - which hurt as much as the running itself. At some stage, I fell in a furrow and my rifle became covered in mud (I think someone else picked it up in order to reduce the amount of weight which I had to carry). When we finally arrived back at base, I was in no small amount of pain and commenced some exercises which were designed to relax my muscles, not that they did. Eventually one of our NCOs saw that I was in distress, and after a quick visit to the camp's medical unit, I was trucked to a local hospital. The thinking was that I had suffered an attack of my stomach ulcer, although I strongly suspected that I was merely suffering from too much exercise.

I was prescribed several days rest in bed (in the camp infirmary, in a proper bed and not in a cold, leaky tent) and was fed four kinds of cheese every four hours. They would wake me in the middle of the night to eat more cheese, which was intended to calm my stomach. As this run took place on a Thursday night, I didn't go home that weekend. As a result of this incident, my medical profile was further reduced from 72 to an impressive 45, which meant that I didn't have to run again for the next six months.

The rest of the pre-basic training period went without hitch, although two weeks later, I contracted a throat infection and had to stay yet another weekend in the infirmary. Whilst I wasn't against sleeping all the time in a proper bed, I would have preferred to do it at home. 

Eventually this period finished and we went on to proper basic training. As all of my friends also had low to medium medical profiles, we all continued to a platoon intended for the less capable. Here we were joined by young Israelis. The first day, we were told by our new platoon commander that he wanted mixed tents, i.e. Israelis and immigrants together. We told him that our tent was mixed: we had an Australian, some British, some American and one Dutch soldier!

This period lasted two months and again was 50% laughter and 50% crying. I remember that we spent a week in bivouac tents camped on a beach where it rained almost all the time. I recall trying to shoot my gun at a target (I was quite good at this) and being told to hurry up - the wind and the rain were moving the barrel around so much that I couldn't keep the gun still. I remember cleaning my rifle after this and being praised as I had managed to get all the sand out the barrel. About the only thing which I cared about during this time was what was going to happen to me after this two month period.

My army service - part one

(we take time off from DBA studying to dive 35 years into the past ....)

It's a well known fact that there is compulsory military service for Israeli youth: 3 years for males and 2 years for females. What is less well known is that there is also compulsory military service for immigrants such as myself, and that the length of this service is dependent upon the age upon which one immigrated (and not the age of commencing the service). An 18 year old male immigrant will serve 3 years, a 21 year old will serve 2 years and a 22 year old will serve a year and a half; being married 'saves' half a year. When I was preparing to immigrate in 1978, these distinctions were important: one non-academic friend (i.e. he had no external timetable) waited until he was 22 then emigrated the day after - this saved him six months of service. 

I emigrated at the age of 22 and so knew that I was facing a year and a half of military service. To someone coming from my background, this was quite a frightening scenario. Someone had put into my mind the idea that the army would find use for my studies and that I could serve in a professional (non-military capacity). In the time between my immigration and my induction, I tried to follow this idea up as much as possible. At one stage, it seemed that I might be able to serve as a microbiologist in Eilat, although this would require me to 'sign on' for two extra years. Unfortunately, this fell through as a result of staffing levels - there wasn't a place available for me.

About a month before my induction - which would be about today, 35 years ago, I realised that I was going to be cannon fodder like everyone else. I reacted to this news with the maturity that I possessed in those days: I locked the door, stayed in bed and cried for a day. Due to prior medical problems with my stomach, I didn't have a clean medical profile and so was not eligible for some of the more gung-ho elements of the Israeli army. With little option, I signed on to the Nachal branch of the army; whilst the length (or lack of length) of my army service prevented me from spending time on a kibbutz, at least this branch understood people like me. I joined a notional group of immigrants, along with a few others from my kibbutz who were in exactly the same position.

One of the advantages of this army service was that the first five weeks of it were spent in a special course for new immigrants: in the morning, we would learn Hebrew, and in the afternoon and evening, we would learn about the army and get some basic training. Fortunately, I was in the top Hebrew class, where we spent most of our time falling asleep (I don't know what it was like in the other classes as I was too tired to ask). Otherwise, we spent a lot of time running around and not sleeping. 

If I were to sum up my basic training, I would say that it was composed of equal amounts of laughter and tears. For example, my first weekend: we looked upon our married colleagues with envy as they left the base on the first Friday. We looked forward to our first shower in several days. I think that there were about 40 'soldiers' in my platoon; we were led to a shower room which had six shower heads, and told that we had ten minutes in which to shower. This was accomplished by having three people under a shower head: one person would get wet, then shuffle out of the water stream to allow the second person to get wet. After the first person was wet, he would soap himself, then wait for his turn to get under the shower head so that the water could wash the soap off. We barely had enough time after this to get dressed; we were so occupied to finish in time that we didn't have enough time to laugh. Then it was back to our tents to wait for dinner (held late on a Friday), during which we fell asleep without meaning to.

[SO: 3703; 3, 15, 36

MPP: 574; 1, 2, 6]

Meeting with a colleague

It seems that I didn't mention this during October, when I was working heavily on my literature review, but one of the papers on user satisfaction didn't seem to have a journal reference (it turns out that I was looking at a pre-print which has yet to be published). I looked at the list of authors and realised that most (if not all) of them are affiliated with Ben Gurion University in Israel. I contacted one of the authors only to be told that she was abroad and to try again in a few weeks.

Once contact was made, we discussed a few things and made a tentative date to meet. That date got postponed a few times but was kept yesterday. Ben Gurion University is in north Be'er Sheva and has a railway station within easy walking distance. Although there is no direct train from Bet Shemesh to Be'er Sheva, it's very easy to make the journey, which lasts only 80 minutes (including a 15 minute wait between trains). Thus yesterday I worked until lunchtime, drove to the railway station, caught the train and arrived at BGU an hour and a half later. I met the author (who is a part-time lecturer at BGU) and had a riveting one and a half hour conversation. Then it was back to the railway station and home by 5:30pm.

One of the conclusions from the discussion (which was by no means centered around my research) was that almost all research into user satisfaction and/or user resistance has taken place in companies moving from non-ERP to ERP systems. I want to look at more mature implementations, a subject which seems not to have been examined. An interesting point arose when I mentioned that earlier that day I had spoken to a new employee about training: although the ERP implementation in the company may be mature, most new employees normally start with no ERP experience. So this issue of mature implementations is not the only factor at play, here: the user's experience with ERP seems to be more important.

Another interesting issue came up when I mentioned the possibility of researching some British companies and comparing the results to Israeli companies. She thought that this was a very good idea (not necessarily for the doctorate but for a later paper) but suggested that this be carried out by means of a qualitative case study which could require researching only two or three companies in each country. This idea has 'game changing' potential but I think that it should be left aside for the time being.

We were talking about English comprehension and the difficulty that she has in preparing a paper in English, even though she knows exactly what she wants to write (maybe I can help here). Anyway, she mentioned a nice joke (which is funny to the cognoscenti): in her course on ERP implementation, she mentions that one of the important factors is change management (knowing how to manage change). One of her less capable students wrote in an exercise that an important factor in ERP implementation is changing the management - he understood 'change' to be the imperative form of the verb 'to change' and not the noun 'change'. It would seem that the lecture in which this topic arose was held in Hebrew but that the concept appeared in English, for otherwise it's difficult to understand how the student misunderstood. OK: it's not a funny joke.

[SO: 3693; 3, 15, 36
MPP: 574; 1, 1, 6]

Enron's spreadsheets

Remember Enron, the American oil company which cooked its books for several years, showing profits which didn't exist in reality, etc? Well, it transpires that as part of the court case against them, all the company's emails were sub poena'd. This material has formed the basis of a study of their spreadsheets.

I wrote seven months ago (it seems like a lifetime ago) about discovering the European Spreadsheets Risks Interest Group and being in contact with one of their number (Dr Felienne Hermans). Yesterday I was looking at her blog and read her account of discovering the 'Enron Corpus'. Apart from the blog itself, there is also a link to an academic paper which I found fascinating.

Following is data extracted from Dr Hermans' analysis with regard to formulas:

Description	number
Number of spreadsheets analyzed	15,770
Number of worksheets	79,983
Number of non-empty cells	97,636,511
Average number of non-empty cells per spreadsheet	6,191
Number of formulas	20,277,835
Average of formulas per spreadsheet with formulas	2,223
Number of unique formulas	913,472
Number of unique formulas per spreadsheet with formulas	100

and with regard to functions:

Rank	Function	# Spreadsheets	Percentage
1	SUM	6493	72.0
2	+	5571	61.8
3	-	4866	54.0
4	*	3527	39.1
5	/	3112	34.5
6	IF	1827	20.3
7	NOW	1501	16.7
8	AVERAGE	879	9.8
9	VLOOKUP	763	8.5
10	ROUND	606	6.7
11	TODAY	537	6.0
12	SUBTOTAL	385	4.3
13	MONTH	325	3.6
14	CELL	321	3.6
12	YEAR	287	3.2

In case this table isn't clear, it means that 72.0% of the spreadsheets which used functions contained the 'SUM' function. The insight that I derive from this information is that the part of my research questionnaire which deals with spreadsheet competence needs to ask about the 'SUM', 'IF', 'AVERAGE' and 'VLOOKUP' functions in order to cover the most highly used functions. I looked at the questionnaire over the weekend and there are questions about the first three; I should add a question about VLOOKUP to cover myself.

The second insight from the analysis is that employees of Enron were sending spreadsheets back and forth, or as Dr Hermans puts it, about 100 emails with spreadsheets attached were sent each day, so we can safely conclude that emailing spreadsheets was common practice at Enron. This practice is known to result in serious problems in terms of accountability and errors, as people do not have access to the latest version of a spreadsheet, but need to be updated of changes via email. Presumably I will have to ask a question such as "How frequently do you work on spreadsheets which are passed back and forth between your colleagues?" in order to quantify this behaviour.

I also found yesterday an interesting paper on measuring cognitive style in students of accountancy; the authors note that cognitive style influences how information is acquired and utilised during problem solving whereas cognitive ability determines how well an individual performs. The authors determine on a sample of 138 accounting students that students’ cognitive ability had a higher impact on their performance than cognitive style. There is a significant interaction between students’ cognitive styles and the cognitive strategy demanded by the accounting task: students with both global and sequential styles performed better when the cognitive demands of the task matched their cognitive style. It is not clear how these findings can be extrapolated to ERP users.

[SO: 3683; 2,15,36

MPP: 574; 1,1,6] 

Matching a computer language to the problems it needs to solve

For the last few days, I've been looking for material about the Sapir-Whorf hypothesis (aka linguistic relativity) which states that people's thoughts are shaped by the language they speak. This is why, supposedly, Eskimos have fifty words for snow, the British have twenty words for rain and the Israelis have thirty words for taxes. I haven't found anything promising yet, but one hint did lead me to Paul Graham's book "Hackers and Painters".

The actual quote is "Our ideas about what's possible tend to be so limited by whatever language we think in that easier formulations of programs seem very surprising". This isn't exactly what I'm looking for. Elsewhere, Graham writes that the Prolog language "has fabulously powerful abstractions for solving about 2% of problems, and the rest of the time you're bending over backwards to misuse these abstractions to write de facto Pascal programs".

I feel the same about the programming language contained in Priority. I showed only a little of it in my previous blog, but I have known for a long time that certain operations - getting required data from a table or updating tables - are much easier to program in SQL than they would be in any other language, whereas supposedly basic operations such as looping and conditionals are on the level of assembly language.

Another Priority interface - multiple import files

I had to write another interface/importing program for Priority. In this case, an external optimising program creates several output files, each containing the id and quantity of parts (wood sheets) which have to be cut. There are three problems to be overcome (stated in order of their occurrence, not in order of importance):

1. There could be many files per day
2. The files are in Excel whereas Priority reads tab delimited files
3. An interface has to be created within Priority

Problem 3 is near enough the same problem as the one which I solved a few weeks ago. Whilst writing such interfaces are new for me, they're quite straight-forward once understood, and the new interface didn't really provide any challenges which I hadn't overcome the previous time (there are one or two innovations but very minor).

The first two problems occupied me the most. As it happens, the other day I found tucked away at the back of the Priority Programming Manual (yes, there is such a thing) a routine for finding all the files in a given directory (I remembered reading about this); what I didn't remember was that the routine shown also loads the files into an interface, which was exactly what I needed.

But before I could deal with the Priority problem, I had to deal with converting an Excel file into a tab delimited file. After looking up similar questions on the Internet, I cobbled together some code which (via automation) started Excel, loaded the required file, determined how many rows and columns the file had, then created a text file in the required format. After playing around with this for about an hour, each time receiving an obscure error message which resulted in Excel staying in memory, I realised that I had been over-engineering. I could simply tell Excel to output a tab delimited file! Then I had problems with the name of the file, but those were easily overcome.

Here is the Priority code as it was yesterday (derived almost without change from the example)

    /* load a list of files from directory DIR into table ST6 */
    EXECUTE FILELIST :DIR, :ST6, :MSG;  
    LINK STACK6 TO :ST6;
    DECLARE FILES CURSOR FOR
    SELECT NAME FROM STACK6 
    WHERE NUM <> 0;
    OPEN FILES;
    GOTO 300 WHERE :RETVAL <= 0;       
    LABEL 100;
    FETCH NAME INTO :NAME;
    GOTO 200 WHERE :RETVAL <= 0;       /* no more files */
    :MYFILE = STRCAT (:DIR, '/', :NAME);
    /* call my Delphi program to convert XLS to TAB */
    EXECUTE WINAPP 'X:\SYSTEM', '-w', 'XLS2TAB.EXE', :MYFILE;  
    /* do something with the tab delimited file */
    LOOP 100;
    LABEL 200;
    CLOSE FILES;
    LABEL 300; 

Ignoring the low level language which is required for programming in Priority, what the above code is doing is getting a list of files in a given directory, then the filter program is called for every file in that directory. I would then have to manipulate the filenames, so that instead of 'file.xls', I would have to input 'file.txt' into the interface. One way of doing this would be to delete each xls file in turn, then call the FILELIST program again to build a new list of filenames.

It occurred to me yesterday evening that there was a better way to skin a cat. This version involved writing a filter program which converts all the xls files in a directory to txt. This approach is going to be faster for two reasons:

1. the external filter program will be called only once instead of once per file
2. Excel will be invoked only once instead of once per file.

The filter program is exceedingly simple:

$APPTYPE CONSOLE}

uses SysUtils, comobj, ActiveX, variants;

const
 xlText = -4158;

var
 i: integer;
 xlApp, wb: variant;
 rec: tsearchrec;
 fn, s, dir: string;

begin
 if paramcount = 1 then
  begin
   CoInitialize (nil);
   xlApp:= CreateOleObject ('Excel.Application');
   xlApp.visible:= false;
   xlApp.displayalerts:= false;

   dir:= IncludeTrailingPathDelimiter (paramstr (1));
   if findfirst (dir + '*.xls', faAnyfile - faDirectory, rec) = 0 then
    repeat
     fn:= dir + rec.name;
     i:= pos ('.', fn);
     s:= copy (fn, 1, i) + 'txt';
     wb:= xlApp.workbooks.open (fn);
     wb.saveas (filename:= s, FileFormat:= xlText, CreateBackup:=False);
     wb:= unassigned;
     deletefile (fn);
    until FindNext (rec) <> 0;
   FindClose (rec);

   xlApp.quit;
   xlApp:= unassigned;
   CoUninitialize;
  end
end.

I still had to use the FILELIST command in Priority, but only once; the conversion from xls to txt had been moved out of the loop.

    /* call my Delphi program to convert XLS to TAB */
    EXECUTE WINAPP 'X:\SYSTEM', '-w', 'MXLS2TAB.EXE', :DIR; 
    EXECUTE FILELIST :DIR, :ST6, :MSG;  
    LINK STACK6 TO :ST6;
    DECLARE FILES CURSOR FOR
    SELECT NAME FROM STACK6 
    WHERE NUM <> 0;
    OPEN FILES;
    GOTO 300 WHERE :RETVAL <= 0;   
    LABEL 100;
    FETCH NAME INTO :NAME;
    GOTO 200 WHERE :RETVAL <= 0;       
    /* do something with the tab delimited file */
    LOOP 100;
    LABEL 200;
    CLOSE FILES;
    LABEL 300; 

This new version works like a charm - poetry in motion.

---

Overnight, I had some more ideas on how I can improve the execution speed of this program, moving operations from Priority to the Delphi program. It would be interesting to know whether it takes the same amount of time to delete a file via Priority as it does via Delphi. Unfortunately, the clock resolution in Priority is only minutes - not seconds and certainly not milliseconds. Also, one can only measure 'wall time' as opposed to CPU time, so any measurement is going to be inaccurate.

My basic idea is building one big tab delimited file with the Delphi program then passing this to the interface, as opposed to passing several small files. Technically this should reduce overhead but I don't know whether the saving will be worthwhile. Also, it forces the Delphi program to be less general than I had originally intended. 

I've just checked how long it takes to process eight small files - less than a minute. It's not worth my time making this faster. Unfortunately, I won't get the two hours of lost sleep back (I woke up at about 11:30pm with the ideas but couldn't get back to sleep till about 2am).

Navigating by machine

As part of my extra-curriculum doctoral activities, yesterday I participated in a morning seminar aimed at SMEs. This was held in Ra'anana, 60km away, in a location with which I am unfamiliar. In previous times, I would have gone to Google Maps (or similar) and printed out a map or route which would lead me to the location (I know how to get within 5km of the location, but no closer). But now that I have a smart phone, I thought I should be like every other driver in Israel and use the 'Waze' application. This app is more than an a  GPS/map; it 'knows' (by means of other users) which is the best/fastest/shortest route to the location and 'knows' where there are problems (such as traffic jams, etc).

I downloaded the app and after a few minutes incomprehension, discovered how I could enter my location and my destination. The app gave me three options for getting to my destination; I chose the fastest. When I got into the company car, I discovered that this was fitted with a GPS, so out of curiosity, I entered my destination into this GPS. At first, I was getting directions from both devices, but as I knew at least the first half of my journey, I turned Waze off.

I turned Waze back on when I was on the eastern side of Kfar Sava. At first, the two apps agreed on how I should travel, but then suddenly Waze told me to turn off the major road and on to a side road, presumably to slide around a local jam. The car's GPS hummed for a minute then recalibrated. Waze let me a jinking dance around the back streets of Kfar Sava which might have saved me some time; the poor GPS of the car was having a nervous fit at what appeared to be my blatant disregard of its orders. The best bit came towards the end when Waze said to turn left after 200m whereas the GPS said to turn right after 800m!

In the end, I arrived at my destination - after an hour and a half, when I had been promised 65 minutes. 

The seminar had some interesting sessions - one on negotiation and one on modern marketing (ironically, two MBA subjects) - but the one on ERP for SMEs (which is probably why I went in the first place) was disappointing and the others were irrelevant for me.

Driving back was very easy as the location was right next to Route 4; I had no need of either Waze nor GPS.

What I didn't know until the evening was that Waze almost completely drained the mobile phone's battery (it was down to about 25%) and that the phone as a phone wasn't working: I couldn't make or receive calls. I will know for next time.

[SO: 3673; 2,15,36

MPP: 574; 1, 1, 6]

Literature review: second draft completed

I wrote three weeks ago that I had completed the first draft of my literature review. Although I mentioned that it ran to 93 pages, I neglected to mention that it contained about 41 thousand words (including references). I sent it to my supervisor; I received the printed copy back last Sunday (2 November). My supervisor says that the draft is far too long: the review should be about 10K words in length.

It took a few days to figure out how I could quarter the size of the draft. The section on previous literature reviews was maybe eight pages long; by cutting out all the details along with my critical comments, the new version was a touch under two pages long. It was more difficult to understand the section discussing case studies on ERP implementations, but one day the penny clicked. Instead of discussing paper by paper, I should take the six research questions about companies from my research proposal and discuss the studies in the context of those questions. Of course, I had to prune the text drastically.

Once I had finished that, I could devote myself to editing the very long section of psychological issues. I had found seven different constructs (such as cognitive style, perceived management support and self-efficacy), then for each construct I presented a definition and whatever papers that I could find on the subject and ERP. After a few evenings of editing, I was able to reduce this section in size.

The second draft contains just over 22K words, including the bibliography. There's been a drastic reduction in size (I wish I could lose weight in the same way that I have lost words) but it probably needs to be reduced further in size. I could ignore the bibliography (2.8K) and say that the first section is a history of ERP and not really part of the literature review; this would reduce the word count to a mere 14K words.

Hopefully I won't have to wait two weeks to receive the supervisor's response. Apart from the fact that I am left bereft of doctoral work, these gaps eat into my proposed timetable.

Undoing textual changes

I frequently get asked, normally in a programming context, whether I can do such and such a thing. If the solution is clear, then I will answer yes, otherwise I will answer no. It has been my experience that a 'no' can be turned into a 'yes', but a 'yes' can never be turned into a 'no'. I had a case of this yesterday, when the Occupational Psychologist asked whether it would be possible to undo changes in a text field in a certain screen in the management program. 

My immediate answer was 'no' - once a change has been written to the database, it can't be undone. "But Word has an undo feature", she said. "Yes", I replied, "but it only works as long as the file has not been saved. After it's saved and you exit Word, the changes get lost". Today I saw a way of turning that 'no' into a 'yes'.

The 'comments' table in the management program has several fields, but only three interest us: one is the primary key, an autoincrement; one is the foreign key to its owner (normally a docket), and one is the actual text. I saw that if I created an 'oldcomments' table with four fields (autoincrement primary key, foreign key to the original comment's primary key, date and text), I could create a backup of the comments and allow restoration.

Here is the explanation as to how it works. The first time that a comment is created, it is written to the 'comments' table. Obviously, there is no backup. The second time that the comment form is accessed, the original text is saved; if the comment is edited and saved, then the new text gets saved to the 'comments' table whereas the old text, along with the comment's id, get saved to the 'oldcomments' table. The third time that the comment is accessed, a new 'restore' button is visible. Pressing it would display a dialog with all the saved texts for this comment - at the moment, only the original text would be displayed. The user has several options: she can choose to restore this prior text, she can edit the current text (and in so doing, save another version in the 'oldcomments' table) or she can reject any changes and exit silently.

The actual program code is not particularly fancy so I am not displaying it here.

I have added a little code to delete all 'oldcomments' entries which are more than 30 days old. This code may be removed in the future. Whilst it is intended to save space, it also seems pointless to me to save text for such a long period: normally an 'undo' will be performed the same day or the day after the text was saved.

Another good idea

Just by chance, I had two very good ideas regarding Priority on Monday. It doesn't normally work like that so don't read too much into the coincidence of dates. Although on the other hand, my brain must have been highly activated that day which allowed me to see solutions which I had previously missed.

We have a business unit which is ... problematic, to say the least. Their method of working is unique and their workers aren't exactly the sort of people who take naturally to an ERP program. I know that I've mentioned this business unit before, but at the moment, I can't find any of those references (The second half of this post is about them).

This business unit derives most of its income from creating industrial floors. A building will have some kind of floor, but for industry (and also for basketball courts), a special floor covering is required. These coverings are created by mixing together various plastics, lacquers and similar products then layering them on the existing surface. I don't pretend to understand this and I don't really need to understand it. As far as I am concerned, they are taking raw materials and turning them into a finished product. The problems are that this process takes place at the customer's site and that there are no fixed quantities for a square metre of floor.

Several months ago, I overhauled their business processes as executed within Priority in an attempt to make things as simple as possible for them whilst at the same time allowing all the data to be recorded as if they were working in a more standard manner. As mentioned in the previous paragraph, 'production' takes place at the customer's site. This means that raw materials have to leave our warehouse and be transported; this fact has to be represented in the inventory table. The amount of raw material sent may not correspond to the amount needed for the current month; in other words, if a project is going to straddle months, there may not be a correspondence between the month in which the inventory is sent and the month in which the work is performed. The work can also be performed over several months.

Priority allows inventory to be marked with a status; the most normal status is 'goods', but there exists the possibility of marking the inventory with a status which is equivalent to the number of the customer. This way, we can tell how much inventory has been delivered to each customer's site. This seemed to be sufficient, but a few days ago, a request was made to differentiate between projects: apparently one customer has two separate but concurrent projects and it's not possible to differentiate what inventory belongs to which project.

This had me stumped for a while, but then I remembered that Priority allows one to define 'locations' or 'sub warehouses' within a warehouse. We don't use this capability which is probably why it didn't spring to mind. But once I considered 'locations', I realised that this was the answer. We have defined one (virtual) warehouse which holds all the inventory which has been delivered to customers' sites; the inventory is differentiated within this warehouse by its status. I added a little twist to this: each inventory movement will now be against not only this warehouse but also against a specific location (which naturally is the project's number). Thus we can always know how much inventory has been delivered for a specific project.

Actually implementing this was slightly more complicated than I expected, although a great deal of the implementation time was actually spent of fixing a bug which I had unwittingly introduced about a month ago.

[SO: 3663; 2, 15, 35
MPP: 574; 0, 1, 6]

Importing purchase orders

For the past few days, I've been wondering what I was doing a year ago. I have read my blog entries for that period but they don't really provide a good understanding. In order to remedy this for the coming years, I have decided to record some of my daily activities, in a more detailed way.

On Sunday (a workday here in Israel), I had a discussion with someone at work. She is charged with entering into Priority a customer's purchase orders; this customer can have more than ten orders a day, so this work takes up a fair amount of time. She wanted to know whether I could create an automatic interface for these orders. As this customer resides in the same database space as we do (a story in itself) and uses the same part numbers as we do (for obvious reasons), I can develop a program which will export their purchase orders to a file, then develop another program to import the data from that file. Due to permissions, this interface has to be separated into two (in other words, I do not want that this customer has access to our database and they don't want that we have access to their database).

I started work on this idea on Sunday afternoon and completed a first version on Monday morning. The export part was not problematic although it did include a new twist: a prior interface which I wrote like this exported comma delimited files and here I wanted a tab delimited file, but this was easily overcome.

I have been aware for some time that Priority has the capability to read tab delimited files into what is termed a 'load table', but I had never done this myself. I looked at an example and discovered what I needed to do. Actually this example led me slightly astray: the file contains two different types of line (headers and details) and I got the impression from the example that I could define two separate 'maps' for reading the input file, depending on the line discriminator. I tried this once and immediately saw the problem as strange data was placed into the wrong fields in the load table. I then redefined the 'map' (and redefined the output format) and this time the data was read from the external file into the load table correctly.

I then wrote a program which would take the data from the load table and import it into the customer orders table; I've done this sort of thing several times in the past so I had no problems. As a result, by Monday lunchtime I had a complete 'suite': a program which exports purchase orders into a file and a program which reads that file and creates customer orders. Each file consisted of one purchase order only.

Yesterday during my evening walk, I had many useful thoughts about this 'suite': primarily, I wanted to extend it so that the file could contain multiple purchase orders. This would mean that the person running the export program would do this once a day and the person running the import program would also do this once a day: this would reduce the overhead to a minimum. I also considered how the first person would receive feedback from the second person, specifically showing what the customer order number was for each purchase order.

This morning I started work on those ideas and discovered that whilst the ideas themselves were good, almost all the implementation details were wrong! Adding multiple purchase orders to the file and reading them were not problematic, but I discovered that the conversion of the data from the load table into the customer orders table was over-complicated. After simplifying this stage, I was able to load all the purchase orders for one day in a minute!

The program includes sending a report by email, showing the results of the interface. I have done similar things in the past, but the report produced has generally depended on one datum (for example, an order number) which had been stored in a special location, whereas here I wanted to send a report containing multiple data (order numbers). The solution had come during yesterday evening's walk - link the report to the load table (the interface which loads the data into the customer orders table updates the table with certain critical data). This idea worked perfectly.

But I also discovered that I had thrown a little part of the baby out with the bath-water: I had no means of checking whether a purchase order had already been converted into a customer order (something which existed in the original one order version). It took a while to figure out a solution for this, but of course I did. At the moment, I'm not convinced that I have chosen the best (or the recommended) method of doing this, but at least it works. This reminds me again of Alan Turing not performing a literature search. 

Hopefully this new system will go into operation on Sunday.

---

On a different subject, I received late last night the first response from my supervisor regarding my literature survey; I only saw the letter this morning. The letter contains only highlights; detailed comments will be in the draft which he is sending my by courier. This afternoon I will write a non-specific response. At least this activity is moving again, after having been on hold for the past ten days.