Wednesday, November 22, 2017

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.

No comments: