Create a Custom Lookup

Use the UD User Defined Lookups form to create custom lookups.

Lookups are windows that display when you press F4 from a field, showing valid data related to that field.

Lookups are created using “clauses” that act as data filters. Before creating a lookup, you must first determine the “from” database table (where the required data is located), as well as the specific database fields/columns to filter information by. Use the following instructions to create a custom lookup.

Custom lookups can also be created by copying a standard lookup and modifying it. For more information, see Copying a Lookup.

Note: This topic provides basic information on creating clauses; however, clauses can be quite complex. For additional help and information, speak to your system administrator.
  1. Identify the table that the lookup will pull data from. To do this, press F3 in any field on the form. Note the name displayed in the View field. This the table that needs to be specified when creating the "from clause".
  2. Determine which fields in the form will display in the lookup. These fields define the lookup columns. You will define these fields in a later step.
  3. Repeat step 1 to identify the calling table (the table/form using the lookup).
  4. Determine which columns on the calling table will be used in the Where Clause to filter data. Press F3 (Form Properties) for each field on the calling form and note the number displayed in the Field Seq # drop-down. These numbers will used when assigning the lookup to the calling field.
  5. In the UD User Defined Lookups form, enter the name of your new lookup in the Lookup field. The name must begin with the letters ud.
  6. In the Title field, enter the title for your lookup. This is the description/title that displays when accessing this lookup.
  7. Create clauses in the From Clause, Where Clause, Join Clause, and GroupBy Clause fields. For more information on creating clauses, see From Clause, Where Clause, Join Clause, and GroupBy Clause.
    Note: These topics provides basic information on creating clauses. For additional help and information, speak to your system administrator.
  8. Select the Sort Descending checkbox if necessary to adjust the order in which the columns will display in the lookup.
  9. Use the Order by Column field if necessary to adjust the sort order of the columns.
  10. On the Details tab, define the lookup columns on the Details tab. For more information, Defining Lookup Columns.
  11. Assign the lookup to the field. For more information, see Assigning a Lookup to a Field. The lookup is now created.