Defining a dynamic target form name for a form column: ZOOM1

This blog will describe a technique that is documented in the SDK but could be written more clearly, as always.

In previous installments, we have seen how to define a dynamic form target for a field in a report. For example, a report might display data from both purchase demands and purchase orders, where one field shows either the purchase demand number or the purchase order number. Clicking on this field will cause the appropriate form to be opened in order to see in detail the purchase demand or the purchase order.

The same mechanism exists in forms, but the implementation is different. I developed a few years ago a form that can display invoices of different types ('A', 'C' or 'F') and allows editing of certain fields in the invoice. Pressing F6 on the invoice number opens an intermediate form, not the specific form for the specific type (AINVOICES, CINVOICES or FINVOICES). This annoyed me sufficiently to seek the solution, which is ...

One defines a form column named ZOOM1, whose value should be the 'exec' number of the required form (not the name of the target form). In the case of the invoices, the exec number is in IVTYPES.EXEC, and so the column name will be EXEC and the table name IVTYPES. This can be seen in the screenshot on the left. This form column is used in the IVNUM field: in the form column extension sub-form, the target form name is set to ZOOM1.

ZOOM1 is predefined so one does not have to define it somewhere. Using 'ZOOM' without a numerical suffix does not work! Apparently ZOOM2-ZOOM9 are also defined, should one need more than one target form for a given form.

I have another private form that displays data about all the programs that I have developed: their name, date of creation, notes and most importantly, for whom it was developed. This form can display reports, procedures, forms and interfaces, so a dynamic target form name is required should one wish to open the chosen program (and frequently I do). In this case, however, I don't want the EXEC number of the program itself, but rather the EXEC number of the form that displays the program: if the program is a report then the form EREP should be opened, and if the program is a procedure then the form EPROG should be opened. As the table EXEC is already being used in this form, I will need to alias the table and use this in what Pascal would call a 'case' statement:

(form column) EXEC1.ENAME
(expression) = (EXEC.TYPE = 'R' ? 'EREP' :
(EXEC.TYPE = 'P' ? 'EPROG' :
(EXEC.TYPE = 'F' ? 'EFORM' :
(EXEC.TYPE = 'I' ? 'EINTER' : 'NULL'))))

Another form column will be required: its name will be ZOOM1 and its value EXEC1.EXEC. I later discovered that the condition EXEC1.TYPE = 'F' should be added in order to prevent duplicate rows in the form (apparently there are two entries in EXEC with the name EREP: one is a form and the other is a menu).