Priority training

Although I seem to be one of the least connected people in Israel, every now and then I do strike lucky with 'a friend of a friend'. Such a connection led me the other day to an interesting conversation with the manager (and owner) of a small training centre, which most not coincidentally has the license to train people for Priority. What is coincidental is that this centre is about 200 metres from what I once described as 'the other company's showroom, which is in the city of Petach Tikva'. So I knew exactly how to get there.

The conversation ranged over several topics, although I'm going to comment about only those which pertain to my doctorate. First of all, the owner was only too pleased to participate in the research, although he warned me that they have only about ten users, and most of them don't use Excel at all. I had to explain that it's fine that there are Priority users which don't use Excel - I need them as a control group in order to see the differences between them and those that do use Excel. 

But it had occurred to me that the real value to be extracted from this connection was not the centre itself, but rather the access that it provided to its students. When I put this to the owner, at first he demurred. He felt that allowing me to talk for upto half an hour to his classes would be a waste of his students' time. What would be better would be for me to give an entire lesson (I assume around two and a half hours) about Priority and then at the end talk about my research and attempt to sign up the students.

I explained about one of the questions about training in the questionnaire: did the training that you received explain techniques about how to use the program, or did it explain how to achieve goals by means of the program? Almost certainly, most people receive the first kind of training (pressing F6 does this, F11 does that), whereas what most people need is the second kind (which explains for example what a delivery note is, why it is used, and how to manage them). The first kind of training is suited for groups whereas the second kind is suitable for individuals - and thus much harder. Of course, a training centre gives the first kind of training.

One of my personal advantages (and I have been told to be less modest about this) is that I can give both types of training. How can I use this advantage and inveigle myself into the training centre, at least for one session (I'm not looking for a full time job!)? The solution was to write an article - which will be the basis for a full lecture - which displays how to write a report in a business context. The report writer receives a request - "write a report which shows all the payments made to suppliers within a given time period" - but because of assumptions both on her part and also by the person making the request, several iterations are required until the correct report is produced. 

Of course, during each iteration, I show what the business need is and how to implement it in Priority. In doing so, I also introduce a few pearls which can't be found solely by reading the documentation and which probably aren't taught. I have no idea how much material utilised in the final version of the report is taught - I hope that not too much is new, otherwise I will have to explain the technical side more than the business side, which is not my intention.

I sent the article to the owner, who presumably will pass it on to the person who runs the Priority training (they run several courses about various computer subjects). I haven't received a response yet, but then not everybody works at my pace - and they may have more important things to do with their time.

Zero values in Priority tables - a blessing and a curse

I am writing this for various reasons: to document for myself a quirk in Priority, to prepare an article for the Priority Users Internet Forum, and as a way of getting myself back into the habit of writing. If you don't use Priority, then this post will have little value.

In SQL databases, there is a way of distinguishing when a field has a value (even if that value is zero) and when it is not known whether a field has a value (there may be a value for what the field represents but it hasn't been entered into the database) – NULL. Handling null generally is problematic for programmers and can frequently lead to incomplete results. Two reports which ostensibly work on the same data can give different results as a result of null fields.

The designers of Priority decided to skip this minefield by removing the keyword 'null' from their SQL subset. A null value is represented by zero, which for 99% of the time is what one would naively expect. This solves a great deal of problems but ironically creates a problem, which is what I want to write about here.

SQL tables generally have one or more foreign keys – these are values which point to other tables. A simple table – such as supplier type – will have at a minimum three fields:

1. 'id': in Priority-speak, an 'A' key; not displayed
2. 'code': in Priority-speak, a 'U' key, displayed
3. 'des': the description of the type, generally not indexed

This table has no foreign key, but its 'id' field will be a foreign key in other tables, for example, suppliers: a supplier may have defined a supplier type, in which case its 'suptype' field will contain the appropriate id value from the supplier types table. A supplier for which no supplier type has been defined will have zero in its 'suptype' field; this is allowed as it is not compulsory (or in Priority-speak, mandatory) to define a value for the 'suptype' field.

In a simple report which lists suppliers by their supplier type (and ordered by type), first will appear all the suppliers which have no supplier type, followed by those that do. This is good as such a minimal report would allow us to see which suppliers do not have a defined supplier type (the fact that we can establish this by means of a query within the suppliers' screen is beside the point). We can also easily add the sum of purchase orders for each supplier within a given time period. Should we wish to see the sum of purchase orders for a given supplier type, we simply make the 'supplier type' a parameter. If there were a code '100' and we wanted to know the sum of purchase orders for all suppliers whose supplier type is '100' within a  given period, we would simply enter that value for the parameter; no suppliers without a defined supplier type would appear.

The problems begin when we use a 'step' procedure to calculate data which are later displayed in a report. The procedure receives as a parameter a list of all supplier types that we wish to include in the report; we use this linked table of types in the query which selects the suppliers to be examined. If we want to see data for all the supplier types, then there is no problem, but should we wish to see data for only one supplier type, we will be astounded to discover that the final report contains data not only for the chosen supplier type but also for all the suppliers without a supplier type!

This is because each table – even linked tables passed to procedures – contain a dummy zero tuple. This is needed so that queries which link a supplier without a supplier type to the supplier types table will not fail (the zero in the supplier's 'subtype' field matches the zero in the supplier type table).

The standard way of writing such a query would be

SELECT ...
FROM SUPPLIERS, SUPTYPES
WHERE SUPPLIERS.SUPTYPE = SUPTYPES.SUPTYPE
AND ...

The above is fine if we want all the suptypes, but is not so good when we only want a subset. Trying to correct the 'zero problem' by writing the below query will cause problems when we want all the suppliers – including those without a subtype.

SELECT ...

FROM SUPPLIERS, SUPTYPES
WHERE SUPPLIERS.SUPTYPE = SUPTYPES.SUPTYPE
AND SUPPLIERS.SUPTYPE > 0
...

[Actually, there is a subtle definition problem with the word 'all' in the previous sentence: in certain cases, use of '*' will return only records which have a non-zero value in the given field, whereas passing an empty string would return all records. Sometimes this can be used as a solution to the 'zero problem'.]

So: sometimes we need an extra clause in the query, but of course, we can't predict when such a clause is needed.

The way which I have found to solve this problem is to calculate how many records there are in the suptypes table before it is linked as a procedure, then do the same after the table has been linked. If the results are the same then the user wanted everything, but if they differ, then suppliers without a suptype should not be included. This is fairly easy to do in a cursor based procedure  (the statements required to solve the zero problem are displayed in bold font below) …

:LINKED = :UNLINKED = :SUP = :TYP = 0;
SELECT COUNT (*) INTO :UNLINKED FROM SUPTYPES;
LINK SUPPLIERS TO :$.SUP;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK SUPTYPES TO :$.TYP;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT COUNT (*) INTO :LINKED FROM SUPTYPES;
DECLARE CUR CURSOR FOR
SELECT SUPPLIERS.SUP, SUPPLIERS.SUPTYPE
FROM SUPPLIERS, SUPTYPES
WHERE SUPPLIERS.SUPTYPE = SUPTYPES.SUPTYPE;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH CUR INTO :SUP, :TYP;
GOTO 200 WHERE :RETVAL <= 0;>
LOOP 100 WHERE (:LINKED <> :UNLIKED) AND (:TYP = 0);
...

This is more difficult to perform with the INSERT INTO/SELECT syntax:

:LINKED = :UNLINKED = :SUP = :TYP = 0;
SELECT COUNT (*) INTO :UNLINKED FROM SUPTYPES;
LINK SUPPLIERS TO :$.SUP;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK SUPTYPES TO :$.TYP;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT COUNT (*) INTO :LINKED FROM SUPTYPES;
INSERT INTO STACK4 (KEY, REALDATA)
SELECT SUPPLIERS.SUP, SUM (PORDERS.DISPRICE)
FROM SUPPLIERS, SUPTYPES, PORDERS
WHERE SUPPLIERS.SUPTYPE = SUPTYPES.SUPTYPE
AND SUPPLIERS.SUP = PORDERS.SUP
AND PORDERS.CURDATE BETWEEN :$.FDT AND :$.TDT
AND SUPPLIERS.SUP > 0
AND SUPPLIERS.SUPTYPE = 
(((:LINKED <> :UNLINKED) AND (SUPPLIERS.SUPTYPE = 0)) ?
-1 : SUPPLIERS.SUPTYPE)
...

One advantage of using a cursor is that suppliers who have no purchase orders within the given time period can be included in the record set, although such a left join (as it is known in standard SQL) can be achieved by adding a question mark after the word PORDERS in the FROM clause. Of course, using a cursor enables further calculations which can't be squeezed into the INSERT INTO/SELECT type.

Margin Call and redundancy

As there are business novels, so there are business films. One such example would be Oliver Stone's Wall Street, another would be Up in the air, and even possibly Working Girl.  Dredging one's memory brings up The secret of my suce$s. I am mentioning these films because I have started watching Margin Call - at the moment, I don't have much time on my hands and so I've only watched about ten minutes of this film. Also, I don't want to write about the film but rather compare an event which takes place in the film's opening scene with real life - or maybe compare Israel with America.

The film starts - like 'Up in the air' - with two unnamed women walking into an office, calling someone aside then informing that person that he has been made redundant, he will receive half pay for the next six months, is required to accept the redundancy package within 24 hours (what happens if he refuses?), and then immediately is escorted off the premises, his mobile phone already disconnected.

By chance, I happened to see a redundancy notice given to one of our employees (I only saw it because I have to remove the person from the list of users in Priority). A meeting was held between him, his manager and a vice president, in which the reasons for his redundancy were presented. His final day of work is one calendar month from that meeting.

Whilst the American approach is better from a data safety point of view, it also seems unhuman. Why immediately disconnect the phone? It would seem that Israeli companies allow their workers less supervised use of their computers - and that month of grace would be more than enough time to remove all the personal information and store it elsewhere. It would also give the unscrupulous more than enough time to download sensitive data, such as a customer list.

Ironically, although I am in the place to do maximum damage to our data, I also have the least incentive to do so. I am not a salesman so I am not concerned with data about what we have sold to whom and at what profit. My knowledge is stored in my head - it's what enables me to be a consultant.

Training the dog

We have had Cora the dog for three weeks and a day. At first, she was almost frozen until she began to get accustomed to her new surroundings. She seems like a good dog, but there are some aspects to her behaviour which need to be improved - like barking and snarling at people (and dogs) outside of the home and also inside. We had been told by the kennels that she was well behaved, but that observation didn't appear to be accurate. As a result, we were given the phone number of a dog trainer. The kennels said that they would pay for the training (although that promise has yet to be fulfilled) - and I thought that we might have to pay the kennels for the dog. 

So far we have had two training sessions. The most important point is to show the dog who is in charge: the humans, not the dog. As we have never had such problems with our previous dogs (or we had forgotten), we have to be taught this as well. The first thing to change was the lead - we have a 3m flexi-lead which was very good for Mocha but unsuitable (for the time being) for Cora, as it allows her to wander around whilst being nominally connected to me. The kennels had given us a fixed 1.5m cloth lead when we took the dog home, and this is exactly what we need.

Yesterday we continued with our lessons. The first thing was to add a second collar - this one is 75% cloth and 25% chain; this is very important as a short tug on the lead connected to the collar simulates a nip on the throat by the dog's mother. The trainer taught Cora a sign for 'sit!', along with the vocalisation. When she didn't sit, he gave a short tug and the sign again; this time she sat. Of course, whilst training, he gave her treats to help reinforce the lesson. This went on for several minutes, then it was my turn. Although at first she wasn't too co-operative (obviously I'm not authoritative enough), she soon got the message.

This morning, when we went out for our walk, I tried the 'sit!' manoeuvre, which went surprisingly well. I didn't even use the hand sign; I just gave a short tug on the lead and Cora sat. For a change, she also relieved herself while we were out - toilet training has been a problem. I noticed that both last night and this morning, she was much more compliant, so the lessons are having an effect! I seat her when people come close and this helps.

One problem which we will have to sort is the reaction between Cora and Gutz, my daughter's dog. They came over on Saturday night for a while; this time, there was less barking but both dogs were kept on their respective leads and didn't interact physically. We are going to have the dubious pleasure of Gutz's company for a few days at the end of January, so I sincerely hope that they will begin to tolerate each other's company. 

There are no problems with our cat, whom Cora meets on the stairs every day. The cat (whose name is Brazil, but we rarely call her by name) had become accustomed to greeting Mocha and rubbing up and down against her. She was at a loss when Mocha died, and when Cora arrived, she tried to continue where she left off. Cora tolerates - or ignores - Brazil, which at least means that we don't have fights on the stairs. But we haven't got to the stage of physical contact yet (well, it's only be a few weeks) and the cat seems disappointed.

[SO: 4032; 3, 17, 38

MPP: 632; 1,  3, 6]

Tuning the piano

A few weeks ago, I wrote about adopting a piano. The piano tuner came yesterday and examined the piano. First, he took all the panels off

to discover that due to neglect, there were cobwebs and other detritus which had to be removed by the judicious use of a vacuum cleaner. After looking at the piano, the tuner realised that he recognised it: he had tuned it a few years ago and replaced a few strings. The family who owned the family had no room for it and stored it outside - which is why there are cobwebs inside and a cracked polyurethane finish on the outside. But otherwise, the piano is in good condition.

It transpires that the piano is actually a very good piano; the tuner was prepared to pay 15,000 NIS (about $4,150) for it on the spot, and he said that it was worth upto 20,000 NIS ($5,550). Apparently a new model sells for about 80,000 NIS ($22,200)! 

There are quite a few repairs which need to be done: a few dampeners have been lost (that's why the F above middle C rings for so long), the pedal action needs to be fixed, and the entire piano needs to be tuned. But all of this will cost only 500 NIS, which is what I expected. As far as I could gather from the internet, tuners charge by how much work they have to do, where the price ranges from 250-500 NIS. I expected that we would be at the top range of the scale.

From the way the tuner was talking, I had a feeling that there was a great deal of work to be done, so I was relieved when he said that it would cost 'only' 500 NIS. He lives locally, which is very good, and will come again on Wednesday morning with the replacement parts. Then he'll do all the necessary work.