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.