Comparison to a Lookup Table
The keyword in can also be used to compare data entered in a field to a set of stored values in a database table. This is the easiest way to compare against a large number of values or against values that can be changed from time to time.
When your form is initialized in QBF or your own application, the current set of data from the lookup table is read into main memory. The column entries in the lookup table at the time of form initialization represent the data values used for comparison. Values added to, or deleted from, the lookup table column after the form is initialized are not reflected in the set of data used for comparison.
To compare against values in a lookup table, use the following format:
fieldname|tablefield.column in
[schema.]tablename.columnname
If the schema, tablename, or columnname after the in keyword is a delimited identifier, you must enclose it within double quotes:
fieldname|tablefield.column in
["schema name".]"table name"."column name"
where:
fieldname
Identifies the internal name of the field being validated
tablefield.column
Identifies the internal name of the tablefield and column being validated
schema
Identifies the schema to which the table belongs and its implied owner
tablename
Identifies the name of a table in the current database
columnname
Identifies the name of a column in the specified lookup table
The following example requires that the value in the Zipprefix field match the values found in the Prefix column of the Zip table:
zipprefix in zip.prefix
This example requires that the names entered in the Manager column of the Emptable tablefield match names contained in the Name column of the Employee table:
emptable.manager in employee.name
The user can place the cursor in a field that is validated by a lookup table or by a fieldname in list validation check, as described in Comparison to a List of Values. In either case, choosing the Help Field operation at this point displays the acceptable values from the lookup table.