Cursors in combined form triggers

Over the past few months, I sometimes recieved an error message about a cursor being declared twice in TRANSORDER_E/TEST-POST-INS-UPD. This trigger is activated after entering a record, either inserting it or updating it. I would look at the code and not see any problem. Last week I was working with someone on a private form where a trigger, again combined, wasn't doing what it was supposed to do (it did nothing).

A few days ago I closely read the new SDK 25.1 and came across this statement at the beginning where changes were listed: Added a warning that cursors cannot be used in combined (e.g. POST-UPD-INS) form triggers. This explained concisely why the private from trigger wasn't working, and why I received once again the error message about a cursor being declared twice in a trigger.

The answer is to create separate post-insert and post-update triggers. In the private form, there was a slight difference required between the two triggers, but the triggers for TRANSORDER_E will be the same. I don't know whether extracting the cursor code to a buffer will solve the problem so I'll probably just repeat the code.

Now that I think of it, I do have a way of testing whether I can extract the cursor code to a buffer: we have a test server running Priority 25.1, so I can change the code there and see what happens without disturbing anybody.

Naming private variables in form triggers

Another problem when trying to upgrade to version 23: an error occurred when trying to add a line to a customer order. As this is an extremely common operation, it was of course very important to solve this problem. The error message said something about variable REF in a certain private trigger; had this variable appeared in the trigger then my life would have been easier.

I was reduced to removing all the private triggers from both ORDERS and ORDERITEMS forms: at this stage rebuilding the forms naturally was without problem. I added the triggers back one by one then rebuilt the forms each time until I received the confusing error. I had to do this several times (and of course, remove all the triggers) until I finally found the source of the problem: a trigger included a buffer, and in this buffer I had defined a variable REF. Renaming the variable to TEST_REF solved the problem. Obviously some standard trigger in ORDERITEMS also referenced the REF variable but as a different type.

As always, finding the source of a problem takes a very long time, whereas the fix is normally done in a minute or two.

What can one learn from this? In form triggers, always append the company's four letter prefix to all the variables defined in the trigger, even though these triggers are private. In modern programming terminology, a form and all its triggers constitute one namespace, and so variables must be unique. Employing the company's prefix will ensure that a future predefined variable will not clash with a user-defined variable.

Adding text to a purchase order item

One of my clients needs to add text to a purchase order item (i.e. to the form PORDERITEMSTEXT) when a certain condition is met. This can be an exceedingly frustrating exercise as I have found out (repeatedly, it turns out).

My original trigger code was in the private post-insert and post-update triggers. At first I tried to enter the text by means of an interface (this is the 'proper' way of doing so) but it seemed that I didn't work. Normally, one passes the variable SQL.TMPFILE as the second parameter to the EXECUTE INTERFACE call (i.e. after the interface name), but one can pass the name of a file, such as R:/1.txt, or whatever. In this case, the result of the interface (such as 'three lines have been added successfully') will appear in the file. Thus one can check that the interface is working properly. Despite this, no text was appearing in the text form.

Looking at other triggers that also enter text into this form, I see that they use a direct insert into the table PORDERITEMSTEXT, as opposed to using an interface. The problem with this approach is that one has to include the necessary HTML tags, as well as values for the fields TEXTLINE and TEXORD, so most (if not all) of these triggers copy text from another form as shown below. Of course, if they want to copy text from another form then using an interface would be problematic as it would copy formatting code as well as the desired text.

INSERT INTO PORDERITEMSTEXT (ORDI, TEXT, TEXTLINE, TEXTORD)
SELECT :$.ORDI, TEXT, TEXTLINE, TEXTORD
FROM PARTTEXT
WHERE PART = :$.PART;

So, in order to use this method, I had to add text to a default supplier (www, whose number is -20). But I also wanted to add something to the final line of text, meaning that I couldn't use the above example as is. In WINDBI, I selected the fields, and saw that for one line of text, four lines existed in the table. OK: I would copy three lines (adding to the SQL code the condition WHERE TEXTLINE < 4) and would add directly the fourth line, including the necessary HTML terminating code.

Still no text appeared.

Tearing at what is left of my hair, I looked at all the triggers of the PORDERITEMS form and saw to my horror that standard buffer BUF1 deletes all text from PORDERITEMSTEXT! No wonder my code appeared to work but apparently added nothing. Once I saw this, I realised that my private post-insert trigger should execute after the standard post-insert trigger, meaning that its name should be something like ZTST_POST-INSERT. There already was a trigger by this name ... that was calling a private buffer of mine that also inserted text into PORDERITEMSTEXT! In other words, I had already suffered from this problem in the past but had forgotten the solution.

Having another trigger that could potentially add text means that first I had to establish how many lines of text had already been added (SELECT MAX (TEXTLINE) INTO :TL FROM PORDERITEMSTEXT WHERE ORDI = :$.ORDI), then add this offset to the TEXTLINE and TEXTORD fields.

Finally the trigger worked! I am documenting this here in the hope of saving someone (not only myself) a great deal of time and frustration in the future.

The FORM_INTERFACE variable

The SDK documents this as: :FORM_INTERFACE (INT type) — when assigned a value of 1, indicates that form records are filled by a form load interface rather than the user.

What does this mean in practice? First of all, the variable has to be placed in a form trigger for it to take action. When data is entered into a form via an interface, the given trigger will not execute if the variable has the value 1. I had a case (that I don't remember now) where an interface would enter data, but some value was missing (deliberately) and this would cause a trigger in the target form to execute, saying that a field required a value. I had to invent some arcane mechanism in order to prevent this error, but it would have been much simpler had I known about (or remembered) this variable. 

Circumventing a standard check-field trigger

One is not supposed to change standard check-field triggers defined in forms. Unfortunately there are cases where such a trigger does not provide the check that a client might want. In these cases, one has to be creative, and this is what I want to document today.

Specifically I am writing about the form ECOUSER that displays the names of users that have to sign off on an engineering change order. The standard check-field trigger checks that the user who signs off (i.e. sets the UFLAG field to 'Y') is the user whose name appears in the line. Fair enough. But my client wanted that any member of a given group could sign.

Before I start with ECOUSER, there is a challenge in that previous sentence: a flag has to be added to the UGROUPS table to mark which group (and thus which users) can sign. But UGROUPS is one of those pesky tables that does not allow developers to add fields; such tables are generally multi-company tables, like USERS and USERSB. So first I had to add a new table TEST_UGROUP that has two fields, UGROUP and ECOFLAG, then add this table to the UGROUPS form so that the flag could be saved. Once I had progressed past that stage, it was time to look at ECOUSER.

It transpires fortunately that this form has a field ALTUSER (and ALTUSERLOGIN) that allows a substitute user to be added to a line in this form; the substitute/alternative user can sign in place of the default user. Both USER and ALTUSER are checked in the standard check-field trigger. In order to achieve what my client wanted, I had to subvert this trigger.

As non-built-in triggers are executed in alphabetical order, I needed a check-field trigger that would execute prior to the standard trigger. This means that I had to define a trigger with a name like AAAA_CHECK-FIELD (A comes before C). Within the trigger, I had to check that the current user is a member of the given group, and if so, place her user number in ALTUSER. Code follows:

:OK = 0;
SELECT 1 INTO :OK
FROM USERGROUP, TEST_UGROUPS
WHERE USERGROUP.UGROUP = TEST_UGROUPS.UGROUP
AND TEST_UGROUPS.ECO = 'Y'
AND USERGROUP.USER = SQL.USER;
GOTO 1 WHERE :OK = 0;
:$.ALTUSER = SQL.USER;
LABEL 1;

I could probably tighten this up by somehow inverting the check, but in this case, it's better to have something simple that works as opposed to something clever that might fail in unpredictable conditions.

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.

Beware of single character variables in form triggers that log changes

I'm going to describe something which isn't a bug in the strict sense of the word, but will cause Priority to act in a way that we don't want and is unexpected.

There is code in almost every screen to record changes; frequently, there are fields in the screen for which changes are not logged and so we have to add them to our own screen triggers, using the same mechanism for storing the changes. There is a table called GEN_CHANGE_LOG which holds data about changes for most screens and there is a standard procedure for entering data into this table (#INCLUDE TRANSORDER_p/BUF2-CH).

Now down to specifics: I added code a few years ago to the PARTPARAM screen to record changes in the default warehouse of the part using the above procedure. The original value of the field being checked is stored in a variable called OLDVALUE and the new value is stored in - you guessed it - NEWVALUE. As the name of the trigger came after the standard POST-UPDATE-CH trigger, my code executed after the standard code. This shouldn't be a problem as the order of checking should make no difference.

But unfortunately it does. I noticed in the changes log for a few parts that the default warehouse had been changed from 'M' to 'M'. How could this be, when the warehouse names are something like M271 or M504? Why was the trigger storing only one character when the field itself has four characters? I looked at the standard trigger and noted that it was saving the value of a field that could be either Y or empty. Priority handles single character strings in a different manner from longer strings; this gave me the clue that somehow the standard trigger was defining the length of OLDVALUE and NEWVALUE to be single character strings, so that when my trigger executed, only the first character of the value would be stored.

There's no way of redefining the length of a string (AFAIK) in Priority, so I had to look at the problem with some lateral thinking. The solution was to change the name of my trigger so that it executed before the standard trigger. I implemented this and checked: lo and behold! Now warehouse names are being stored in their entirety and are not truncated.

---

I wrote the above a few years ago. Today I ran into a similar case: changing the process of a part to Issue in the PART form caused the error log to show that the new process is 'I'. The previous change in the log was changing the type from O to I - that's where the 'single character variable' comes from.

Edit: this bug appears to have been fixed in later versions of Priority; it's certainly fixed in version 21.

More on POST-FORM triggers

Following my previous post on the topic, I've come across a problem with this trigger. Price quotations with a total greater than 50,000 NIS that were created before the trigger was added are obviously not connected to a sales opportunity. Simply entering the price quotation in order to look at it will trigger the error message when exiting the quotation.

The practical solution to this is to press F7 which causes the current record to be removed from the screen. Possibly inelegant but it works.

POST-FORM triggers

I was recently tasked to create an error message when a price quotation's total price is over 50,000 NIS and the quotation is not connected to an opportunity. This is more difficult than it sounds, as the price comes from the price quotation's lines. The standard scenario would be that a user opens the price quotation, adds lines then exits the price quotation, possibly changing the status of the quotation. If the status is changed, then a pre-update trigger would seem to be the place to put the check about the quotation's price. But if the status is not changed, no event occurs at the quotation level and so no code will be triggered. After all, the post-form trigger performs operations when a form is exited, provided there were insertions, updates or deletes in the form [emphasis mine].

One event that is guaranteed to occur is the item form's post-form trigger (this saves having to write both post-insert and post-update triggers) that will be triggered when the amount in the line changes. Here one would have to write code that sums the prices in the lines connected to the price quotation and then checks whether this total is greater than the designated price. I don't like this too much as the same check will be executed several times (once for each line) and also variables that belong to the price quotation form have to be named with a double dollar sign (because they're in the parent form), like :$$.BRANCHNAME. Whilst this isn't a problem, it's one more thing that has to be remembered. 

Whilst idly looking through the SDK for the description of the post-form trigger, the search found a system variable whose acquaintance I had yet to meet: ACTIVATE_POST_FORM. Its description is assign it [the variable] a value of Y in a PRE-FORM trigger to activate the form's POST-FORM trigger upon exiting the form, even if no changes have been made [emphasis again mine]. This is exactly what I need! In the private post-form trigger I included the private pre-update trigger, so one way or another, the error message will be activated as requested.

This technique makes certain checks in forms much easier.

By the way, I couldn't use a business rule as I had to check more than three conditions.

Don't forget to unlink GENERALLOAD in form triggers

I wrote a post-update trigger for a form yesterday; the code seemed correct when I looked at it, but it didn't work when added to the form. The code itself took some data from the form, inserted them into the GENERALLOAD table then called an interface in order to do something in another form. I extracted the code and rearranged it slightly to become a self-standing procedure so that I could debug it; the procedure worked properly. So why didn't the trigger work? 

The answer half came to me when walking the dog at 5:30 am (always a productive time for mental debugging): check other triggers for this form to see whether they linked the GENERALLOAD table, and more importantly, whether they unlinked the table. And of course, I found another post-update trigger that I wrote that linked GENERALLOAD but did not unlink it.

In the context of the form, when the second trigger came to link GENERALLOAD, the link failed because the table was already linked. Had I been clever enough to display a warning message if the link failed, instead of skipping to the end of the trigger, then I would have known immediately what the problem was.

Moral of the story: ALWAYS UNLINK GENERALLOAD at the end of a trigger!!

This isn't as clear cut as one would like to think. Normally one writes code such as

EXECUTE INTERFACE 'STATUSMAILQ', SQL.TMPFILE, '-L', :$.G2;
SELECT MESSAGE INTO :PAR1 FROM ERRMSGS                              
WHERE USER = SQL.USER AND TYPE = 'i';                               
ERRMSG 99 WHERE :RETVAL > 0; 

The question is: does ERRMSG cause the linked table (:$.G2 in this case) to be unlinked? I suspect not, so alternative methods have to be found in a trigger - probably using WRNMSG, then unlinking the table, then jumping to the end of the procedure. Or unlinking directly after the 'execute' line and before the 'select' line; this way, RETVAL will hold the result of the SELECT statement.

Beware of subroutines in form triggers

I wrote a complicated form trigger - post-update - which is supposed to extract lines from a customer order and insert them into another order. Despite the fact that the code looked correct (and of course, there were no syntax errors), it wasn't working. Every query was checked in WINDBI and was found to be correct. Eventually I was reduced to inserting WRNMSG statements at every decision spot in order to find out why the code wasn't working as expected.

Eventually I found what the problem was: the trigger was calling a sub-procedure defined at the end of its code that returns the number of non-closed lines in the order. I used a sub-procedure as the code is called at two separate places in the trigger, when the number of non-closed lines can vary from the first invocation to the second, if lines are extracted to another order and closed in this order.

The sub-procedure simply wasn't being called. Why not, I asked myself? In semi-desperation, I changed the sub-procedure's number from SUB 900 to SUB 1234 - and then the code worked! This means that some other trigger must define a sub-routine 900 and this is what is being called (if anyone is interested, SUB 900 is defined in the trigger ORDERS/UpdateDiary). 

So what can be concluded from this exercise? All the triggers of a form must be considered to be part of the same name space, which means that each sub-routine should have a separate number. No more SUB 900 - use more imaginative numbers! This is a much more insidious problem than the #INCLUDE problem. In the same way that I now use non-obvious names for cursors in triggers (and let us not forget that the name of a cursor in a trigger must either begin or end with @), one must also use non-obvious numbers for sub-routines.