Sunday, January 31, 2016

A managerial reason for opposing the use of spreadsheets

Here is a real world example of what can happen when spreadsheets are used,

My company is trying to determine how much money has been spent so far on a very large project. A clerk was given the job of classifying lines in the purchase orders stored in Priority so that we can see how much has been spent per class. Her report totals 1.25 million NIS.

We received a print-out of her results; someone had added by hand another 250 thousand NIS, thus supposedly increasing the amount to 1.5 million NIS. We have yet to receive an explanation for the extra amount. The project's manager sent in a complicated spreadsheet whose total expense appears to be 1.75 million NIS, and a third person's spreadsheet shows 2 million NIS.

Which of these results is to be believed? We have costs ranging from 1.25-2 million, where this latter figure is 60% greater than the first! What constitutes the extra 0.75 million, and why aren't those costs written in Priority? Presumably the suppliers will wish to be paid and they won't get paid without a bill in Priority.

One could say that management should be conservative, in which case we should take the highest estimate of costs. But there is such a difference between the values that it is hard to take them at face value.

Wednesday, January 27, 2016

Suspected poisoning

It was raining heavily when I came home yesterday, so I had to wait for a few hours before I could take our new dog, Cora, for her delayed afternoon walk. As usual, she was very pleased to see me when I came home, and followed me everywhere I went in the house (yes, she tried to enter the toilet with me). Even when I was sitting down and she was lying down, her eyes would follow every movement.

When the rain finally stopped, we went out for a walk. As usual, she investigated the hedgerows and picked up loose paper with her mouth. When we got home, we noticed that her mouth was white; in fact, she was producing volumes of foam. This is exactly what happened with Mocha the evening before she died, although the foam appeared several hours after our final evening walk. When I saw this, I immediately told my wife that we were taking Cora to the vet.

Once we arrived,  the vet gave Cora a quick check: her pupils were fixed and enlarged, and she was still foaming. First, he weighed Cora (22.5 kg), then gave her an injection containing an emetic (apomorphine); as the linked wiki article states, "The emetic properties of apomorphine are exploited in veterinary medicine to induce therapeutic emesis in canines that have recently ingested toxic or foreign substances."

After Cora vomited three times, the vet shaved a patch on her foreleg, inserted a cannula and proceeded to give her an infusion of Ringer's solution, in order to prevent any dehydration which might have been caused by the foaming and vomiting. All this time, Cora was very quiet - as much due to the apomorphine as to the suspected poisoning; she was also shaking a little. After the infusion finished, she sat with me in a corner for an hour, under observation.

By the end of the observation hour, her pupils had contracted and were now reactive; she had stopped shaking and was behaving in a more normal manner. The vet said that we could go home, but if any symptoms returned, then we should get in contact, even at 4am. My wife sat with Cora for another hour for continued observation.

This morning, Cora was as if nothing had happened, although maybe she was a little subdued. I took a completely different route for our morning walk, walking inside the kibbutz instead of outside. My wife took me to work in the car (because of the heavy rain), and at the beginning of the drive, I showed her the route that our walk had taken us the previous evening. In the daylight, one could see that one hedgerow had been sprayed with weed-killer; a strange practice to perform when it's raining heavily (the rain deactivates the weed-killer and washes it off the bushes).

Thus we managed to save Cora from dying; she was the beneficiary of the harsh lesson that we learned from Mocha.



On a completely different note: for some forgotten reason, I again looked at the list of notable Old Bristolians on the Wikipedia, and was surprised to see someone from my year: Sir Andrew CashI think that he was in the parallel class to mine for several years but we weren't in the same house (I suppose that everyone these days knows about Gryfindor and Hufflepuff, so there's not much need to explain) and so we would have known each other by name but would not have had much interaction. 

It turns out that there are two lists of distinguished Old Bristolians at Wikipedia: the one which I linked to and one connected with the entry for the school itself. Sir Andrew does not appear on the second list. Well done, Andy Cash.

Sunday, January 24, 2016

Bitten by the bug

Two weeks ago, I wrote these prophetic lines:
  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.

It didn't take long for the first lesson to be forgotten. My task was as follows: in the Projects form, if a contact person is added then insert into a son form data based on that person. Simple, no? I wrote some code and it worked perfectly. 

This morning I received two phone calls complaining about this screen; both users had a similar error message but each was referring to a different field. While I was talking to the second user, the penny dropped: I was trying to insert data into the son form before the parent form (ie the project itself) had been created. In other words, my post-insert trigger was executing before the default post-insert trigger. 

So I changed the name of my trigger and now the code works properly.

Thinking about this some more, the explanation doesn't seem right. If I have written a post-insert trigger, then the project has already been inserted (that's why it's called post-insert), so the alphabetical order of triggers isn't relevant. I'm inserting data into a private table so this isn't dependent on anything else.

Referring to the above phrase "it worked perfectly", obviously my testing was at fault. I was testing the post-update trigger but not the post-insert trigger. The real lesson to be learned is to call all private post-insert triggers ZLOB_POST-INSERT (at least, something beginning with Z). This way, the alphabetical order 'bug' will not bite me again. It's not a bug in the program but rather in my understanding.

Saturday, January 23, 2016

Vinyl log 27 - 23 January

DayMonthYearArtistTitle
23January197610ccHow dare you

Nothing in particular comes to mind when I see this date and record. I know that I saw 10cc live somewhere around this time, but I can't find the date and to be honest, I don't remember anything about the concert itself.

I was a big fan of 10cc, even though I didn't like about a third of their songs. This - their last album with the excellent Lol Creme and Kevin Godley - featured some of their best ever material ("Don't hang up", "Mandy", "Rock 'n' roll lullaby") but also had some rather dire songs ("I wanna (sic) rule the world", "Iceberg") and some which didn't have much of an impact ("Head room", "Art for art's sake"). 

I do remember them appearing on 'Top of the Pops',  supposedly playing "Art for art's sake". I watched this in the London commune with several co-habitators; the song had us bewildered - this is what get's released as a single? "I'm Mandy" (later on, one of my housemates was called Mandy) was a much better choice and apparently reached No. 6 in the charts.

Where was I in January 1976? I would have been working at Schweppes in Hendon as a food analyst during a six month engagement as part of my degree.  I wrote a little about my course here, but didn't really go into details. For me, Hendon was quite local: I just had to drive down the Hendon Way for a few miles on my newly purchased motorbike, turn off down a few local streets and then I was at the factory. I worked in a two story building in the grounds and had nothing to do with the factory itself. They were very good to me at Schweppes. 

Just to put things into perspective: my leaving present was an pocket calculator. At the time, it was very expensive (I seem to remember it cost about 15-20 GBP, when my pay was 40 GBP) and would become very useful for the rest of my early academic career.

Tuesday, January 19, 2016

Another misuse of Excel / preconceptions

I thought that I had seen everything, but today someone sent me a screen shot from Priority ... in Excel! Most people simply paste the screenshot into a 'new mail' screen in Outlook and send me the letter, something which annoys me greatly as that screenshot will stay in all the connected correspondence. I save the screenshot into Paint as a png file (which is automatically compressed) then attach the file to a letter.

But saving into Excel then sending the spreadsheet as an attachment? That's something new, counter-intuitive and so wrong. But it shows that this person thinks in terms of how he can do things in Excel and appears to be unaware of more suitable programs. Somewhat surprising as this is an engineer who works with AutoCad and SolidWorks much more than he works with Priority.

He is also what I sometimes refer to as a 'grey Russian': someone very set in his ways, with whom I have great difficulties in communication. It's not worth suggesting that in future he save screenshots in Paint.

Monday, January 18, 2016

MP3 headphones (2) / The scientific method

As I've been wearing the mp3 headphones (and listening to the music stored within) every time that I take the dog for a walk (that's two or three times a day during the week, four times a day on Fridays and Saturdays), I've had a lot of time to think about the order in which the songs are played. I want to treat this in a scientific manner, so first I will give a non-rigorous version of the scientific method:
  1. One makes observations 
  2. One makes hypotheses based on those observations to explain them
  3. One test those hypotheses to see whether they support all observations
  4. A hypothesis cannot be proved to be true, but it can be proved to be false. One accurate observation which contradicts a hypothesis means that the hypothesis has been falsified.
  5. A good theory (or hypothesis) should predict future observations
So what are my observations? These are at two levels: directory (artist) and song. The artists played in order were Richard Thompson, Robin Frederick, Sandy Denny, Tales of us (Goldfrapp), The Band, The Blue Nile, The Swell Season. Hmmm: seems like they're in alphabetical order. Question: why was Richard Thompson the first artist to be played, and not No'am Newman, considering that the latter comes before the former in alphabetical order? Thus at the moment, one can hypothesize that the directories are played in alphabetical order, but I'm expecting that this hypothesis will be falsified.

The songs in each directory are also played in alphabetical order of their titles. True, the directories of Sandy Denny and The Swell Season played the songs in the order in which I intended, but when I examined the directories, I saw that the song names had been prefixed with a number which indicates the order. This observation strengthens the hypothesis is that the songs are played in alphabetical order.

On Saturday afternoon, I remembered that an old mp3 player of mine (prior to the Sansa clip+) used to drive me mad as the songs were played in the order of their being copied to the player! Could the same thing be happening here? Could it be that the songs are currently in alphabetical order because they were copied in alphabetical order? Cue this web page, which states that "Many modern MP3 players are based on USB flash drives (i.e. Creative's MuVo range of MP3 players). These devices don't allow you to sort the MP3 files in the order you want to listen to them. Instead they play the MP3 files in the order they find them; usually the order you copied them to the flash drive. Unfortunately if you select a bunch of files in Windows' Explorer and copy them to your MP3 player, they don't always copy in alphabetical order (even if you have told Explorer to display the files alphabetically). And of course you may want to add new MP3 files at a later date, which will result in them being added to the end of the directory."

Several people have created a solution: a utility program which sorts the FAT32 directory without the need to recopy the files (the FAT is like a phone book which records where every file is located on the disk/memory. The entries are made in the order of writing to the medium). I tried DriveSort which achieves its aim pretty well. Before updating the directory, however, it is necessary to edit each song's file name to include its play number; this number is included in the ID3V2 tag but DriveSort ignores this.

Going back to the scientific method for a moment: point 3 says that hypotheses can (and should) be tested. Here is the experiment: 

  • Take a directory whose songs are currently being played in alphabetical order (e.g. The Band)
  • Rename the songs in the required order
  • Play the songs 
  • Using DriveSort, update the directory
  • Play the songs again
Stage 3 will check whether the alphabetical hypothesis still holds - if the songs are played in their new order, then the hypothesis has not been falsified. I suspect that they will still be played in their original order, an order which does not match the new order. Stage 5 will check whether the new hypothesis is true: that the songs are played in the order of their copying, or more technically, in the order of their appearance in the FAT32 directory.

I confess that I haven't performed this experiment yet because I got sidetracked with an earlier problem. All the songs have their order number contained in their ID3V2 tag; I would like an automatic method of transferring this tag to the song's file name so that DriveSort can do its job. Whilst obviously I could do this manually, it is a pain in the finger; being a programmer, my natural inclination is to write a program to do the renaming for me. 
 
So I started looking for libraries which enable a Delphi program to read the tag; the first solution which I found did not work at all. The second seemed to work; this came with a test program with a visual interface which allowed me to examine an existing mp3 file, to read its tag and to update it. This program didn't read pre-existing tags, but it did write tags which it could read back; also WinAmp could recognise these tags.

I took the dog for a walk, during which time I realised that this program was of no use to me as it doesn't read the pre-existing tags and so could not update the file names. I had wasted at least half an hour playing with it. I resolved to find another library which could read pre-existing tags.

And find I did. This library also came with a test program which allowed me to view the tags contained within a file; it passed this test with flying colours. By now it was getting late so I decided to leave the subject for another day. Despite this decision, some of my dreams were about mp3 tags (this is why I can't program for too long in the evening).

I am aware that in the time that I have devoted to finding a programmatic solution to the file name problem I could have renamed all of the files. But I like a programmatic solution which supposedly will save me time in the future. Actually, in this case, that's not necessarily true: in the future, I might decide to edit the file name directly instead of the tag, though this means that the order will be wrong should I decide to return to the Sansa.

I see that I did not address the issue of why Richard Thompson was the first artist to be played. My supposition is that this was the first directory to be copied then the other directories were copied in alphabetical order by Windows. This supposition is strengthened by the fact that the computer sees the Sansa as two separate disks: its built-in memory and an external memory card. If I remember correctly, first I copied the directories on the memory card (where Richard Thompson et al. are to be found) then the other directories. I should be able to solve this by running DriveSort at the directory level.

Saturday, January 16, 2016

Vinyl log 26 - 16 January

DayMonthYearArtistTitle
16January1972Randy NewmanLive

Although I don't remember purchasing this record, I vaguely remember the events which led to its purchase. Sometime before - I think in November 1971 - the BBC broadcast an 'In Concert' program with the Rand (I have just discovered this clip from the programme). I watched the programme and remember coming away with a strong sense of 'claustrophobia', as I defined it then. A month or two I later, I suspect that Richard Williams wrote a review of this record in the 'Melody Maker'.

There wasn't much Randy Newman product available at the time - his first album was reputedly the worst selling record that Warner Brothers ever released (an accolade shared with Richard Thompson's first solo record) -  and his second probably wasn't available. As it happens, when I did buy those two albums years later, I never liked them very much.

I didn't know this at the time, but the record was originally intended as a radio promo recording to gain publicity[1]. I couldn't have cared less. What amazed me at the time was the piano playing - I doubt that I had ever heard anyone play like this before. I used to describe it as 'impressionistic': in certain songs, Randy wasn't simply playing chords, he was playing an entire orchestra.

Some of the songs left an indelible mark on me - "Tickle me", "Simon Smith", "Living without you" (I belatedly realised that I already knew this song, albeit played in a generic style by Bristol folkies Ians Hunt and Turner), "Last night I had a dream", "Lover's prayer" (reminiscent of Woody Allen and singled out in that MM article), "Cowboy", "Davy the fat boy", "Lonely at the top" and of course "I think it's going to rain today".

"Davy" especially was not like anything I had ever heard before (or since). This was an incredible piece of writing and playing; to be honest, I didn't really appreciate the lyrics until I read Greil Marcus's book "Mystery train" a few years later.

"I think it's going to rain today" turned my world inside out; it was my theme tune for several years and summed up my adolescent angst. To this day, I don't know whether this is one of Randy's rare 'straight' lyrics or whether it is meant to make fun of people like me who were suffering from adolescent angst. Judging by the large number of covers, I conclude that everyone else has considered this song bereft of irony. And the harmonies of that bridge!

Thursday, January 14, 2016

Alan Rickman, RIP

... another one bites the dust ....

I first became aware of actor Alan Rickman by his portrayal of the Sheriff of Nottingham in 'Robin Hood: Prince of Thieves'. His voice seemed to be the epitome of evil, with the sound of curdled cream. Belatedly, I realised that I had also seen him as German Hans Gruber in 'Die Hard'. But the first time that I fully recognised him was as the Metatron in 'Dogma'; a comedy film which most people dislike.

Not being a Harry Potter person, it was only by chance that I saw him as Professor Snape, a role which probably brought him more money and fame than all his other roles put together.

He also had one of the story lines in "Love Actually": a middle aged man married to Emma Thompson, but tempted by his secretary.

For some reason, I have seen very little of his work, but I very much enjoyed what I saw.

He died at the same age as David Bowie (69) and also of cancer.


MP3 Headphones

A few weeks ago I had the misfortune to return from Haifa on a very noisy train. It's interesting to note that in the mornings, the trains are generally quiet, but in the afternoon, people lose their restraint and make a large amount of noise - generally from smartphones which are not listened to via headphones. Although in the past I had traveled with a small mp3 player, my headphones are fairly small, albeit of high quality. So even if I were listening to my music, I would also hear all the extraneous noise.

After that trip, I decided that something had to be done. I had seen people traveling wearing headphones which enclosed their ears (the type that I like) but these phones weren't connected to anything. I wondered whether they worked via a bluetooth connection to a smartphone. When I got home, I started looking for something like this on the Internet. Whilst I did find bluetooth headphones, I found something (for my purposes) much better: headphones with a built-in mp3 player. What could be simpler?

I quickly found the N65 Sports Wireless 1.5" LCD Digital Headphones MP3 Player, although now I don't know from whom I ordered it - apparently not Deal Extreme (dx). The headphones come with a Lithium Ion battery but without a memory card. As it happens, I had a memory card waiting in my shopping cart (which probably means that I ordered from Amazon), so I could copy my songs to the new card and insert the card into the headphones.

Whilst it was clear where the card fitted into the headphones, it was not at all clear where the battery was supposed to go. It took me about ten minutes to figure out that the back of the left headphone can be removed, revealing a socket for the battery. Then of course I had to charge the battery prior to its use.

I utilised that time to copy songs to the memory card, which is a whopping 16GB. I hate to think how long it will take to listen to all the songs - and it's not even half full. Once the battery was charged, I listened for a few minutes to the music - very good quality. My wife also had a listen and she immediately wanted a pair for herself. Unfortunately, it seems that this product was so successful that at the moment one can't order it from anywhere.

The price was a ridiculously cheap 80 NIS - but when one takes into account that one has to buy a memory card, the price becomes a more reasonable 150 NIS (or thereabouts). I see that someone is selling from eBay for $12.41 with free shipping.

The headphones arrived on Sunday; on Monday I had the chance to test them in the field as I was traveling to Haifa that day. I wore the phones from Tel Aviv to Haifa going north, and all the way coming home. Although I could hear muffled train announcements when music was playing, the only time I could hear other people on the train was during the gaps between songs (I should point out that I don't listen at a very high level).

The only gripe which I have at the moment with the player is that the songs play in what seems to be alphabetical order. For reasons which are not clear, the music started with my Richard Thompson collection, the first song being "Al Bowlly's  in heaven". After that, there were a few songs whose name begins with "B", then "C", etc. By the time the songs had got to the "W"s (eg "Walking on a wire"), I noticed that several songs had been skipped. What happened next is not clear: I think that Al Bowlly started again, then I pressed the button which skips to the next track. I was then treated to all the songs that hadn't been played the first time, again in alphabetical order. Strange.

As I have included so many RT songs, he was still playing when I got off the train. When his run finally finished, the player moved to songs by Robin Frederick (alphabetical order again?), again playing the songs in alphabetical order. Being scientific, I have to state that I haven't collected enough data to make a conclusion about the order. I did try numbering the songs - both in the file's name itself (eg "4123 Walking on a wire") and also in the internal metadata of the mp3 files (the ID3V2 tag). Neither seem to make any impression. 

After Robin, Sandy Denny popped up, thus continuing the alphabetical order of directories. Her songs have followed the ID3V2 tag and have not been played in alphabetical order. Strange. I shall have to wait and see what happens next.

I did look for an instruction manual on the internet - I found something in Chinese English, half of which is incomprehensible. Needless to say, the issue of song ordering was not addressed. Searching for this item reveals about 38,700 results. Obviously I'm not going check all of them; from what I can see, at least the first couple of hundred hits reveal exactly the same text. No one seems to have actually reviewed this item.

I should point out that I am one of those dinosaur types who likes their songs to be played in a definite order and not at random. An order would allow me to appreciate the changes in RT's songwriting, playing and production over a 40 year career.

Wednesday, January 13, 2016

Reviving the research

I last wrote about my research two months ago. Since then, very little has happened as I couldn't see how I could change to a more qualitative orientation. I had an epiphany on Saturday, when suddenly it became clear to me what needed to be done. I emailed my supervisor, and even wrote a revised chapter in my submission which I sent him. He suggested (and every suggestion by a supervisor should be taken almost as an order) that we talk in the evening.

So yesterday we had a discussion via Skype. It seems that one salient fact had passed him by: whilst I had been moaning that only ten companies had signed up, I did note that from those companies I could expect at least one hundred completed questionnaires. When my supervisor realised the importance of this last statement, he said that this would mean that the quantitative approach would definitely be possible. The fact that the respondents would be drawn from only a small number of companies is regrettable and probably means that any conclusions drawn from the 'company data' should be taken with a grain of salt, but this should not be a problem when my submission gets discussed by the research committee.

Greatly relieved, I said that the submission was about 99% ready: I needed to incorporate a few paragraphs which I had written about EyeCo in November (the company mentioned in the linked post, which does not use Excel) and add some new material in the methodology and conclusions sections. I intend to conduct about twenty interviews - spread over ten companies, this means two people per company. In these interviews, I can ask about subjects for which I couldn't find suitable questions.

I awoke in the middle of the night with a question on my mind: might it be possible that feral systems (and/or spreadsheets) are used because the companies think that it is not cost effective to employ a developer (internal or external) to develop the required reports? In other words, might they think that it is cheaper to allow a salaried employee to spend a few hours working on a spreadsheet than employ someone to develop a report? Obviously, I think not! In terms of economics, employing a developer to create the necessary report is a one-off cost whereas having an employer use a spreadsheet is a repeating cost, which over time is going to cost more than the developer.

This morning, I added the required material to the submission, which didn't take long, then sent it to my supervisor. He said that he would look at it as soon as possible, as there is a research committee deadline in a few days; if I miss this deadline, then I will have to wait another seven weeks.

Suddenly the prospect of completing the doctorate before my 60th birthday in August seems possible once again.

Tuesday, January 12, 2016

Sending complex emails via Priority

The genesis of this blog was a request made to me by a programmer of intermediate skill who wanted to send a complex email from a screen trigger in Priority.

There is a simple mechanism (half documented) called MAILMSG - this sends a mail to either a user or to a group, where the contents are contained in three parameters, each of maximum length 64 characters. This mechanism is usable for small amounts of data, such as 'order XC123 has been opened', but is somewhat limited, both in content and in format.

A much more complicated mechanism exists (again, half documented) which allows one to send a report via email. Once this technique is mastered, it's actually very useful, but quite hard to debug.

Neither of these options were what the programmer required, Basically he wanted a stronger version of MAILMSG which could include almost unlimited text. A third programmer suggested using SENDMAIL, but gave no hints as to what this might be or how it might be accomplished.

When I started poking around, I discovered that SENDMAIL is an external program which sends mail (naturally); its payload appears to be a row stored in the MAILBOX table. After turning this over in my mind, I realised how this mechanism works. There is a form in Priority called MAILBOXSEND which is a compromise between a normal Priority form and a 'send email' form of a program like Outlook: there is a header (subject, recipient) and a son form which consists of text. When sending mail, the contents of this screen are passed to the external program which sends the mail.

Once this was clear, writing the program itself was relatively painless. Data can be entered into a new row of the MAILBOX table by the standard means of an interface; I wrote the necessary code and checked that indeed a new email was stored and waiting to be sent.

More problematic was the actual sending of the email. In the MAILBOXSEND screen, this is implemented by a procedure with a few steps, of which one - the actual SENDMAIL procedure - interested me. This step has two parameters: a linked file of emails and a text parameter. By oversight, I neglected to define the order in which the parameters were passed to the procedure, and of course, the parameters were being passed in the wrong order which is why my program didn't work at first. After a while I realised my mistake, corrected it and discovered that my program does indeed send mail.

The program, written to be implemented as a trigger in the PORDERS form, follows. The variables whose name start with :$. refer to screen fields.
/* Build the email */ SELECT SQL.TMPFILE INTO :GEN FROM DUMMY; LINK GENERALLOAD TO :GEN; INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT6) VALUES (1, '1', STRCAT ('*', :$.ORDNAME), 'TAMCHIR'); /* tamchir is the name of an email group */ INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT) VALUES (2, '2', STRCAT ('Supplier = ', :$.SUPDES)); INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT) SELECT PORDERITEMS.LINE + 2, '2', STRCAT (PART.PARTNAME, ', ', PART.PARTDES, ', quantity = ', RTOA (REALQUANT (PORDERITEMS.QUANT), 2)) FROM PORDERITEMS, PART WHERE PORDERITEMS.PART = PART.PART AND PORDERITEMS.ORD = :ORD ; EXECUTE INTERFACE 'GLOB_SENDMAIL', SQL.TMPFILE, '-L', :GEN; /* Did the interface execute with errors? */ :XMSG = ''; SELECT MESSAGE INTO :XMSG FROM ERRMSGS WHERE USER = SQL.USER AND TYPE = 'i'; GOTO 1 WHERE :RETVAL <= 0; :PAR1 = STRIND (:XMSG, 1, 64); :PAR2 = STRIND (:XMSG, 65, 64); :PAR3 = STRIND (:XMSG, 129, 64); ERRMSG 500; LABEL 1; /* Get the id of the email, now that it has been built */ :MB = 0; SELECT ATOI (GENERALLOAD.KEY1) INTO :MB FROM GENERALLOAD WHERE LOADED = 'Y' AND RECORDTYPE = '1'; /* Insert that email into a temporary file of emails */ SELECT SQL.TMPFILE INTO :PAR FROM DUMMY; LINK MAILBOX TO :PAR; INSERT INTO MAILBOX SELECT * FROM MAILBOX ORIG WHERE MAILBOX = :MB; EXECUTE SENDMAIL :PAR, :XMSG; /* send it */ UNLINK AND REMOVE MAILBOX; UNLINK AND REMOVE GENERALLOAD;
This program is an example of what might be called 'incremental improvement'. It's not something which is taught on the first day of Priority programming, but rather builds on knowledge which is incremented as one learns more and more techniques. Almost the entire program - until the comment 'Insert that email' - is fairly standard code for transferring data from one screen/table to another screen/table; whilst the specifics vary from application to application, the general structure and tasks are the same. I should point out that this technique is considered 'advanced', although once learned, it is extremely useful. 

Only the six lines of code following that comment are new (and to be honest, the first five, which copy one row from the existing MAILBOX table in a new, temporary, table are based on similar code by which a customer order is built from data in a storage area). Calling the SENDMAIL program within my procedure prevents the problem which I had earlier about parameter ordering: here it is clear what the order is. 

There is no way of finding any code within Priority which calls SENDMAIL in this manner (one can't search within the SQL code of a procedure), so one can't learn this technique from previous examples without some head-scratching.

[SO: 4057; 3, 18, 38
MPP: 632; 1, 3. 6]

Monday, January 11, 2016

David Bowie, RIP

As everyone in the world is writing about the death of Bowie, I suppose that I should add a few comments. Let me preface them by saying that Bowie was never an influence on me, neither do I have any music by him.

At summer camp in 1969, I became friendly with a boy called X Banks (I can't remember his first name - I haven't thought of him for years, and of course that summer camp was the only time we met). He played the guitar and taught me my first chords - for some strange reason, only A and G. I wrote some lyrics to which he added the music. At every camp, we had a free day, in which we would be taken to a nearby town and allowed to do whatever we wanted to do for a few hours (eating at a cafe was always a good idea).

If I remember correctly, that year we were near the town of Woodstock (not that Woodstock, although the timing was perfect), slightly north of Oxford. Instead of heading for a cafe, X headed for a record shop: he wanted to buy a copy of a record called 'Space Oddity'. I don't remember whether he found it or whether we listened to it. I think that I bought a copy of 'Brontosaurus' by The Move (edit: wasn't this was in 1970?).

I doubt that I knew this at the time, but 'Space Oddity' was Bowie's tribute to the moon landing, which had taken place a few weeks before the summer camp. We all enjoyed the stylophone solo, probably played by Rolf Harris, who used to display it on his television show.

That was the last I heard of Bowie for a few years, until I started frequenting the youth club in Bristol. There I became acquainted with 'Hunky Dory', but didn't like it very much. A bit later, I saw Bowie 'perform' a song called 'John, I'm only dancing' on 'Top of the Pops': this I very much enjoyed (probably because of the electric guitars) and bought the single; it received heavy play at the youth club.

Again, I didn't hear any more of Bowie for a few years, and what I did hear, I didn't like. Somehow I managed to miss Ziggy Stardust and Aladdin Sane. The 'Berlin trilogy' also passed me by. I love the film clip of 'Ashes to ashes' but don't care too much for the song itself. There was a brief renaissance around the time of "Let's dance" (1984), but again, very little of his music is in a style that I like.

I bought producer Tony Visconte's autobiography several years ago, which devotes a large amount of space to his work with Bowie. This probably caused me to listen once again to Bowie, but once again, the verdict was the same: not my taste.  After all that, I have to say that I enjoy "Life on Mars" - the song, not the television series. The song brings back memories of living in Cardiff for a few months in July-August 1974.

There is no doubt that Bowie was an innovator (or maybe, like the Beatles, an early adopter of a style which he then publicised) and had a huge influence on other musicians ... just not on me.




Saturday, January 09, 2016

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.

Thursday, January 07, 2016

Vinyl log 25 - 3 January

Before I get started on this day's vinyl log, I want to explain why this series of blogs stopped abruptly in October. When I summed up the blogs between 801-900, I saw that 24 of them were vinyl logs; it seemed to me that almost every blog was such a log. There may not be much happening in my life (mainly disappointments), but I didn't want to spend all my time chronicling events which happened mainly 30-45 years ago. The series is being renewed although probably will be pursued with less vigour than before.

DayMonthYearArtistTitle
03January1978Jackson BrowneRunning on empty

I suppose that first I have to start with how I became acquainted with Jackson Browne's records. Sometime in 1976, I purchased a subscription to 'ZigZag' magazine, as there was a student discount. Whilst ZZ devoted most of its space to West Coast acts, most of which I had never heard, there was also a decent amount of British coverage. One of the earliest editions that I purchased had Richard Thompson (and probably Linda, too) on the cover, so ZZ was OK. Unfortunately, about six months after the subscription began, to my displeasure ZZ turned punk, so that was the end of that.

In November 1976, ZZ began singing the praises of a new record by Jackson Browne entitled 'The Pretender'. I decided to check this out, so went down to One Price Records in Golders Green, where I heard this record. The first song, 'The fuse', knocked me out (although after the first chord, I instinctively knew what the second chord would be) and I decided on the spot to buy the record. This was 18 Dec 1976. Whilst the second and third songs on this record found less favour with me, I very much appreciated the record and over the next few months bought the other three records which JB had released at the time.

The story with 'The fuse' is very similar to that of 'The songs of Leonard Cohen' : at summer camp of 1971, a friend had strongly recommended his first album, even though by this time he had released three. I went into a shop to hear the record and much appreciated the first track that I heard, 'So Long Marianne'. On the basis of this, I bought the album, only to discover that SLM was the opening track of the second side and that there were several songs which I didn't care for (too long and boring).

Back to Jackson Browne. At the end of 1977, I remember being somewhere in central London at night with a girl that I was seeing at the time (she was in the first year student of my course whilst I was a lofty fourth year student); I saw this album - gate-fold sleeve - in some shop display. Of course, a few days later I would have gone to some record shop to buy this album,

The concept seemed interesting: an album consisting of songs about 'being on the road' actually recorded on the road. Some of the songs were recorded in concert whereas some were recorded in hotel rooms and even on the tour bus! Unfortunately, the concept didn't match the execution: the songs, for the most part, were somewhat long and boring (this seems to be a common complaint for me). Whilst I can still recall a song or two, I have never bothered to replace this record with a cd, or even download it.