Suppose you have a Customer.dbf table that you want to link to an Orders.dbf table, to show each customer’s orders by date. The Customer table has an autoincrement field named Cust_id. The Orders table also has a Cust_id field and an Order_date field. The index on the Cust_id and Order_date field is defined as:

str( CUST_ID ) + dtos( ORDER_DATE )

Because the Cust_id field is converted to a string in the expression index, the Cust_id field in the Customer table must also be converted to string in a calculated field to link the two tables. (If the Cust_id field was a character field in both tables, this extra step would be unnecessary, because you could use the Cust_id field as-is to link to the expression index.)

Use the Customer query’s onOpen event to create the calculated field, arbitrarily named Cust_link:

function customer1_onOpen( )

   c = new Field( ) 

   c.fieldName := "CUST_LINK" 

   this.rowset.fields.add( c ) 

   c.beforeGetValue := {|| str( this.parent[ "CUST_ID" ].value )} 

Note that when working in the Form (or other) designer, creating the onOpen event handler for the Customer query does not immediately execute it. The calculated field will not be present until the query is re-executed. Toggling the query’s active property alone won’t work, because although the event has been assigned, its code has not been compiled and is therefore not available. You can force the designer to recompile all the code and reexecute the query by making a change in the constructor of the form (adding and removing a blank line is sufficient); then the calculated field will be present.

Once the calculated field is present (it will be in the Field palette), specify the Customer rowset as the masterRowset of the Orders rowset, and the Cust_link field in the masterFields property.

After the calculated field is created, its beforeGetValue will be streamed in the form class constructor in the WITH block of the query’s rowset (right after the query itself). This means that the beforeGetValue code is present in two places, both in the constructor and the onOpen event handler. You can leave them both there, but if you change the code in the onOpen, you must either also change or remove the assignment in the WITH block, because it executes after the query’s onOpen event. Or you can remove the code in the onOpen, and assign the beforeGetValue directly to the calculated Field object in the Inspector.

If you use this relation often, you can create and reuse a data module that contains this code.