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.