• Script objects
When a script object, such as a Resultset or cmdSelect object, retreives data from a lookup field, the values retrieved are numeric pointers to other records. To retrieve a lookup field's display field, rather than the numeric pointer, use the following syntax:
LookupFieldName:DisplayFieldName
where the LookupFieldName is the name of the lookup field which holds the numeric pointers, and
the DisplayFieldName is the name of a field in the target table.
For example, suppose the Suppliers table has a RowId field that's
an integer primary key and a SupplierName field that's a text field. Also suppose the Products table has a lookup field named SupplierLookup that stores
the value of the Suppliers table's RowId field, and displays the value of the SupplierName field.
If you want a Resultset on the Products table that returns the display value of
the SupplierLookup field, you can create the Resultset as follows:
var rs = new Resultset("Products",
"ProductName, SupplierLookup:SupplierName", "*");
Note the special naming syntax that we use to refer to the SupplierLookup
field -- "SupplierLookup:SupplierName". The first part is the name of
the lookup field in the Products table, SupplierLookup. The second part is the
name of a field from the Suppliers table, in this case SupplierName.
You can retrieve more than one field from the lookup table as follows:
var rs = new Resultset("Products",
"ProductName, SupplierLookup:SupplierName,ContactName,Address",
"*");
You can use this combination syntax anywhere you refer to a field name. For
example, you can use it to retrieve the field's value from the Resultset:
while(rs.next())
response.write(rs["SupplierLookup:SupplierName"]);
• Views
On a view, if the lookup field is included within the data region, then you
can use the same syntax with the record
object to retrieve the display value of the lookup field. For example, if the
SupplierLookup field is in the data region, you can access the display value as
shown:
<!--#
response.write(record["SupplierLookup:SupplierName"]);
#-->
• Data classes
The record object is also passed as an argument to the data class scripts (e.g., the BeforeInsert and AfterInsert functions, etc.). Within these functions, the record object provides only the
stored value of the lookup field on the table. If you need to retrieve the
display value, you should create another Resultset on the lookup table,
filtering for the record that you need.
