|
| How To: Accessing data in a lookup field from script |
| Article Type | How To |
| Title | Accessing data in a lookup field from script |
| <p><i>In script, when I create a resultset on a table that has a lookup field,
the value that's returned for the lookup field is a number. I need the text that
the user sees on the view. How can I get it?</i></p>
<p>The number that's returned in the resultset in script is the value that's
actually being stored in the lookup field. The text value that the user sees is
stored in the table that the lookup points to. To get the value that the user
sees, you can take advantage of the fact that the lookup field links the two
tables together.</p>
<p>To retrieve a field value from the lookup table, use the following special
naming syntax:</p>
<p><font face="Courier New" size="2">LookupFieldName:DisplayFieldName</font></p>
<p>where the LookupFieldName is the name of the lookup in the current table, and
the DisplayFieldName is the name of a field in a related table.</p>
<p>For example, suppose you have a Suppliers table that has a RowId field that's
an integer primary key and a SupplierName field that's a text field. Suppose you
also have a Products table with a lookup field named SupplierLookup that stores
the value of the 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 could create the resultset as follows: </p>
<font face="Courier New" size="2">var rs = new Resultset("Products",
"ProductName, SupplierLookup:SupplierName", "*");</font>
<p>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.</p>
<p>You can retrieve more than one field from the lookup table as follows:</p>
<font face="Courier New" size="2">var rs = new Resultset("Products",
"ProductName, SupplierLookup:SupplierName,ContactName,Address",
"*");</font>
<p>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:</p>
<font face="Courier New" size="2">while(rs.next())<br>
response.write(rs["SupplierLookup:SupplierName"]);</font>
<p>On a view, if the lookup field is included within the data region, then you
can use the same syntax with the <font face="Courier New" size="2">record</font>
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:</p>
<p><font face="Courier New" size="2"><!--#</font><font face="Courier New" size="2"><br>
response.write(record["SupplierLookup:SupplierName"]);</font><font face="Courier New" size="2"><br>
#--></font></p>
<p>The record object is also passed as an argument to the data event functions
that are available for each module (e.g., the BeforeInsert and AfterInsert data
events, 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 recordset on the lookup table,
filtering for the record that you need. </p> |
| Last Reviewed | September 10, 2001 17:33:04 |
|
|
