One more instrument in the developer's toolkit (using a trigger in a report)

The other day, I was asked to write a report about "Catalogue of parts with serial numbers"; the use of this screen is very important to companies which track where each individual product is found. The report had to include the status of each product. Normally, most tables (e.g. customer orders, purchase orders, customers, etc) have a corresponding status table; the ubiquitous documents and inventory line tables have their own status table which is very complicated. But for some unknown reason, the serial number products table does not have a corresponding table but rather uses the customers table! It would seem that the use of this table is shared with other tables.

The statuses stored in the customers table all have the 'statusflag' set; predefined statuses have their ID (cust) bearing a negative value, but user-added statuses have regular, positive, values for their ID.

In the first version of the report, I had defined the customers.custname field as a parameter and tried to limit the number of values displayed by requiring customers.statusflag to be 'Y'. This did not work; every time I tried to find a status, the report would present the entire list of customers.

There is a little known way around this problem which I intend to explain. One can define a choose-field trigger for a field in a report in the same way that one defines a trigger for a field in a screen. Each field has a sub-form called 'field triggers' which most developers are probably unaware of. Once I remembered the existence of this sub-form, writing the trigger was very easy:

SELECT CUSTDES, CUSTNAME
FROM CUSTOMERS
WHERE CUSTNAME <> ''
AND STATUSFLAG = 'Y'
ORDER BY 2

Once this code was in place, only the status 'customers' would be presented as possible values. It isn't often that one needs this technique, but like all esoteric techniques, it is the perfect solution to a specific problem. One more instrument in the developer's toolkit.

[Edit from much later] The reason why inventory statuses are stored in the customers table is because inventory can also be 'packed'/reserved for a customer, whereupon its status becomes the customer number (this is what happens after using the DOCUMENTS_A screen). In order to maintain a status that can be either a customer number or some general form of status (like 'goods', 'reject', etc), the general inventory statuses are stored in the customers table, but flagged. Thus 'real' customers can be displayed as can be 'real' statuses without the two intersecting.