Even more on POST-FORM triggers

A few months ago, I documented my need to write a POST-FORM trigger and how to execute it even if there were no changes in the displayed record. The code worked fine in most situations, but I found one situation in which it didn't work. 

Let's say that the current price quotation is for 51,000 NIS: the error message should appear. It will appear if I change anything in the quotation, such as its status, and it will appear if I leave the form with ESC. But the error message won't appear if I press PageDown or Ctrl-Enter (which is effectively the same), in order to create a new price quotation.

After some cogitation, I realised that I had been defining a POST-FORM trigger: ESC will cause the form to close and so POST-FORM triggers. But PageDown leaves the form displayed on the screen, albeit with no data. I was confusing the POST-FORM event with a non-existent event, POST-RECORD (there's no such event or trigger by this name, which is a pity).

Once I realised this, the only solution appears to trap the error condition (or calculate the value of the price quotation) before each line is entered, i.e. CPROFITEMS.PRE-INSERT. Actually I need to trap the PRE-UPDATE event as well. I thought that there is a difference between the two, regarding the current line, but writing this out has undermined my confidence. In PRE-INSERT, the current line won't be in the database whereas in PRE-UPDATE, the current line will be in the database albeit with the wrong price. Now I realise that this doesn't matter: in PRE-UPDATE the wrong total will be created, but it will be higher than what it should be as opposed to lower.

First I'll show the new trigger query and then I'll discuss my reservations.

GOTO 57 WHERE :$$.BRANCHNAME <> '200';
GOTO 57 FROM DOCSTATUSES
WHERE ORIGSTATUSID = :$$.CPROFSTAT
AND SORT = 99;  /* cancelled */
:TEST_TOTAL = :TEST_THISLINE = 0.0;
/* Total of lines previously entered */
SELECT SUM (PRICE * (100.0 - PERCENT) * (100.0 - :$$.PERCENT)
* 0.0001) INTO :TEST_TOTAL
FROM CPROFITEMS
WHERE PROF = :$$.PROF;
/* Add this line */
SELECT (:$.PRICE * (100.0 - :$.PERCENT) * (100.0 - :$$.PERCENT)
* 0.0001) INTO :TEST_THISLINE FROM DUMMY;
:TEST_TOTAL = :TEST_TOTAL + :TEST_THISLINE;
ERRMSG 5000 WHERE :$$.CCNUM = '' AND :TEST_TOTAL >= 50000;
LABEL 57;
  

For the sake of argument, let's say that the first line in the price quotation has the price 20,000 NIS. TEST_TOTAL will be 0 and TEST_THISLINE will be 20,000, so of course the error message will not be displayed. The second line also has the price 20,000 NIS: now TEST_TOTAL will be 20,000 and TEST_THISLINE will be 20,000 NIS, so the total is 40,000, again below the limit. But should I then edit the second line and change it to 30,000 NIS, TEST_TOTAL will be 40,000 and TEST_THISLINE will be 30,000, giving a total 70,000 which is well over the limit. The combined total should be only 50,000, but the extra doesn't matter. The way to overcome this would be to enter a negative amount initially then change it to a positive amount; I don't think that anyone is going to go to such lengths.

I've just thought of another possibility. Let's say that at the beginning, before entering any lines, a discount of 10% was entered into the price quotation. One line (to make this simpler) for 54,000 NIS is entered. The total will be 54,000 - 5,400 = 48,600 NIS and so no error message will be displayed. But I'm a devious character and now remove the discount: the price jumps to 54,000 but again no error message will be displayed as no line has been changed. This means that I have to retain the CPROF/PRE- UPDATE trigger that currently exists.

These mental contortions are a good reason why I don't like programming forms.

---

[Addition from 13/07/22] I'm very embarrassed to say that there are two bugs with the code that I published above, one HUGE and one not so important. SUM (PRICE ...) gets the sum of the prices, not the total cost of each line - i.e. there is no multiplication by the quantity! This is why my tests of 1 unit @ 50,000 NIS caused the error message to be displayed, but 50,000 units @ 1 NIS did not. This is really poor programming. The field QPRICE should be used, with no need to take the line's discount into account.

The other bug concerns DOCSTATUSES: there should be another line in this clause, AND TYPE = 'C', to limit the check solely to statuses of price quotations.

Winrun redux

Several months ago, I wrote about using the program WINRUN in order to send data to Priority from a computer that is not running Priority. I wrote a program in Delphi that would accept barcodes (as read from a dumb barcode reader) and then would transmit them to Priority, invoking a procedure that could handle these barcodes. By the time I wrote that blog entry (in November), the Delphi program was working as designed.

That is, working until about a month ago, when we upgraded our version of Priority from 18.2 to 21.1 (a giant step that caused all kinds of problems). At first it seemed that the only change necessary to my program was to enclose the procedure name with quotation marks, but unfortunately this transpired to be not true. The program would work on a computer that was running Priority but would not work on a computer that was connected to the network and could access the Priority directories but was not running Priority.

With hindsight and after rereading the documentation, I could see that the problem was with accessing the tabula.ini file. Documented was a change in the environmental variable required for the program. Originally the variable was called TABULAINI and as I wrote then, this only has to be done once on the computer. In the mean time, the name of this variable has been changed to TABULAINIORIG (one might ask why this was done; I presume that the answer would be 'davka', a wonderful Hebrew word that supposedly means 'precisely', but has the connotation of being contrary).

Even after I changed the value of the environmental variable, my program still would not work. I had noticed before that the date of the file tabula.ini would change every time that it was accessed, but it hasn't changed since the end of May. This no doubt explains why the program doesn't work. But why?

Today I had the insight to run the SET command (without parameters) on the computer: this lists all the environmental variables that had been defined on the computer. TABULAINIORIG was not there. I have been using a batch file to check WINRUN, and indeed the first command was SET TABULAINIORIG = x:\1\tabula.ini as per the book. I added the variable again, and after unsuccessfully running the batch file, I ran SET again - and the variable that I had just added had disappeared! It seems that WINRUN 'eats' this environmental variable which is really strange behaviour.

As by this time I had removed the SET command from the batch file, I typed it in manually. Then I ran the batch file - and it worked! I ran SET again and saw that now there was a variable tabulainiorig as well as TABULAINIORIG; it seems that WINRUN wants its environmental variable in lower case!

I changed the Delphi program so that instead of setting the (wrong) environmental variable only once at program start-up, the two variables are set immediately prior to running WINRUN (I'm not taking a chance with the upper/lower case problem). And now the program works again.