Logging changes in bypass forms

I am often asked to create 'bypass' forms: this is my term for a form that is based on a standard table but only exposes a few fields. The most common example is a bypass form for customer orders; assume that the order is at a status that does not allow changes, but the user would like to change a few non-essential or private fields. The bypass form exposes those fields but is not bound by the order's status.

There is a relatively simple means of logging changes in this form so that they can be seen in the 'real' orders form.

:DETAILS = '';
:SONEXEC = 0;
SELECT EXEC INTO :EXEC
FROM EXEC
WHERE ENAME = 'ORDERS'
AND TYPE = 'F';
:CHARKEY1 = ITOA (:$.ORD);
:CHARKEY3 = :CHARKEY2 = :CHARKEY4 = :CHARKEY5 = ''
;
GOTO 71 WHERE :$1.DETAILS = :$.DETAILS;
:OLDVALUE = :$1.DETAILS;
:NEWVALUE = :$.DETAILS;
:COLNAME = 'DETAILS';
#include func/UpdateChangesLog
LABEL 71;

The first part of this code (upto the line containing a single semicolon) sets up necessary data so that the changes will be written to the standard orders form changes log. If one wants to use these lines for a different form, then ENAME will have to be the name of the target form. ORD is of course the number of the order being edited.

The second part checks whether a change has been made in the field 'details'; if so, the original value and the new value are saved along with the column name. The column name will appear in the change log the same way in which it is defined in the form.

There is a very large 'gotcha' in that last sentence. One of the fields displayed in the bypass screen is a private field added to the ORDERS form that is based on another table (i.e. like displaying CUSTOMERS.CUSTNAME). The column name for this field in the ORDERS form will be something like TEST_CUSTNAME. But in the bypass form, there's no need to use four letter prefixes as the form is private, so I had defined this column as CUSTNAME.

Changes in this field were not being logged - or rather, they were being added to the CHANGES_LOG table but were not appearing in the change log form. After a bit of head-scratching, I realised that the column name (:COLNAME) should be the name of the column as it appears on the 'real' form and not how it appears on the bypass form. Once this seemingly minor change had been made in the trigger code, changes to the CUSTNAME field now appeared in the change log.

Incidentally, if one wants to log changes in a bypass form for orderitems, the prologue code becomes

:DETAILS = '';
SELECT EXEC INTO :EXEC
FROM EXEC
WHERE ENAME = 'ORDERS'
AND TYPE = 'F';
SELECT EXEC INTO :SONEXEC
FROM EXEC
WHERE ENAME = 'ORDERITEMS'
AND TYPE = 'F';
:CHARKEY1 = ITOA (:$.ORD);
:CHARKEY3 = ITOA (:$.ORDI);  /* This is the order line number */
:CHARKEY2 = :CHARKEY4 = :CHARKEY5 = ''
;