Looking Up Information in Reference Tables

Input fields on a detail form editing a database record are namedSET.attr_name. When the record is saved, data from SET fields are copied directly to the underlying record. Thus, an input field for an attribute that references another table should contain the REL_ATTR (foreign key) of that table. This is normally the id, persistent_id, or code of the reference record.
casm1401
Input fields on a detail form editing a database record are namedSET.attr_name. When the record is saved, data from SET fields are copied directly to the underlying record. Thus, an input field for an attribute that references another table should contain the REL_ATTR (foreign key) of that table. This is normally the id, persistent_id, or code of the reference record.
Users do not directly provide REL_ATTR values, and the SET fields for attributes referencing another table are hidden. The visible field on the form is named KEY.
attr_name
, and it contains the common name of the referenced record. A common name must be converted into a REL_ATTR to update the record. There are several times when this might be done:
  • For fields with a drop-down list, the SET value is provided directly by the drop-down.
  • For fields with a lookup when the user clicks the lookup and selects an item, the SET value is copied from the selected item.
  • For fields with a lookup where the user provides a partial key that uniquely identifies the record and then clicks the label, the browser requests the SET value from the server and copies both it and the full key back to the form.
  • If the Autofill configuration file property is provided or defaulted, and the user both provides a partial key that uniquely identifies the record and clicks Notebook to exit the field, the browser requests the SET value from the server and copies both it and the full key back to the form.
Otherwise, when the record is saved with a KEY value and no SET value, the web engine resolves the value during the save. If any KEY values cannot be resolved to a unique SET value, the save is prevented, and the edit form is redisplayed.
If a form has been redisplayed as a result of a save that failed due to a lookup resolution failure, the following variables are available in the HTMPL for each attribute field for which a lookup was performed:
  • LIST_
    attr
    Contains all the matches found. Typically this is specified as the right-hand side of the SOURCE= field in a <PDM_SELECT> statement.
  • FLAGS_
    attr
    This is set to one of the following values:
    • 0
      Display initial search field.
    • 1
      More than one and fewer than MaxSelectList were found (typically a <PDM_SELECT> list would be displayed in this case).
    • 2
      No matches were found.
    • 3
      Too many matches were found (more than MaxSelectList).
  • SEARCH_STATUS_
    attr
    string
    Contains the TooManyMatches text string from the web.cfg file.
Specifying Lookups on Contacts
When specifying a contact (last name, first name, middle name) in an editable form, you can delimit the contact name with commas (,) or blank spaces, but not both. Commas are preferable because names often have embedded spaces, which cause problems.
Since a combination of commas and blank spaces is not allowed, the presence of commas implies that all parts of the name are comma-separated; if no commas are present, names are delimited by spaces.
Since the information is eventually passed to an SQL query, the percent symbol (%) serves as a wildcard character. For example, 'P%, J%' would match 'Public, John', 'Penxa, Jane', and any other names whose last name begins with P and first name begins with J. (Case-sensitivity depends on the underlying database.) Similarly, 'P% J%' would bring up the same names.
However, 'P%, Jon D' would not bring up all contacts with a first name of Jon, a middle initial of D, and a last name beginning with P, because the presence of one comma means all delimiters are commas. Therefore, the last name would be looked up as 'P%' and the first name would be looked up as 'Jon D'. To avoid this error, specify 'P%, Jon, D' instead.