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.