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.

Misleading error message

I developed a private form several years ago that displays values of private constants. When I open this form, the error message 'Value exceeds permitted quantity' appears; I press on this three times and it disappears whilst the data is being displayed. As this sort-of works and I'm the only person who accesses this form, I hadn't given much thought as to what could be the problem.

The same thing happened at one of my external clients: they want to load an external file then display the contents in a 'load form' for visual checking before converting the data into invoices. The load form displays this 'value exceeds permitted quantity' error message and clicking on it a few times doesn't help. I originally thought that this behaviour was due to some system constant being too low - the example file has 1220 lines. Changing various system constants didn't make any difference.

Last night I decided to solve this problem for once and forever. My first act was to delete the form entirely - maybe something had contaminated it - then redefine it. The data displayed in the form all come from the same table, with no joins, so 'programming' the form was a simple matter. Retrieving all the data caused the same error message; I had to close the form via the task manager. I then retrieved the first record - it displayed correctly. The first ten records display correctly. The first hundred records display correctly. The first seven hundred records display correctly. 

It was only when I retrieved record number 1000 that I found what the problem was and finally understood the misleading error message. The familiar error message appeared, I clicked on it; the error window disappeared and the record appeared. The line number field displayed 1,00 instead of 1000 as I had been expecting - this is the smoking gun. The record number is stored in a field called KLINE that I had defined as an integer of length 4, as I wasn't expecting more than 9999 lines in the file. But the form is displaying this number with a comma separating the thousands, so 1000 was displayed as 1,000 - and this is five characters! Hence the value (i.e. required column width) exceeds the permitted quantity (of characters in the column). All I had to do was lengthen the field from 4 to 6 characters and rebuild the form. Now all the data displays correctly. I suspect that the error message appeared once for every problematic line, meaning that had I pressed on the error message 220 times, the data would have appeared.

The English error message is a faithful translation of the Hebrew message that I was seeing. I don't know which came first, but in both languages the message is extremely misleading and doesn't say what the real problem is. A far better message would be '<column name> is trying to display X characters but is defined of length Y only'.