Cascading Custom Field Lookups

Where the value of one field depends on the value of another you can use a Cascading Custom field. You should always start by defining the highest level parent table, for example if you wish to cascade Country to State to Zip Code you should start by defining the Country table. When you create the child level definitions, you will be prompted to select the parent table. It follows that the highest level from which field cascade cannot be a 'cascading' dynamic lookup

You will be prompted for the following information

  • Cascading Parent Field: field on the screen that links to the table one level up e.g. 'State' if defining 'Zip Codes' from the example above
  • Cascading Lookup Field Name: field in the table that will be used for the lookup on the parent
  • Lookup Table Name: name of the table that contains the valid values
  • Lookup Key Field Name: name of the field that is the key to the lookup table
  • Lookup Description Field Name: field that contains the description to show
  • Lookup Sort Field Name: field to order by when returning data
  • Create Table in SQL: if the table does not exist it will be created for you on Save
  • Filter by Project: if you add a column to your lookup table called “projectid” (case insensitive) then values are returned per project
  • Show with Attributes: if selected then the field value(s) will be shown on the left hand side in the List and View Item screens

Worked Example:

  • Tariff (created as SQL table 'Tariffs')
    • TariffId
    • Description
  • Device (created as SQL table 'Devices')
    • DeviceiId
    • TariffId
    • Description

In the above example, Tariff is Custom Field of type Dropdown List and is defined as NOT cascading. Tariff is the Parent Field for Device.

Device is a Custom Field of type Dropdown List and is defined as CASCADING from Tariff and the Cascading Lookup field is therefore TarrifId.

Data Entry of Tariff values will look as below:

Date Entry of Device values will look as below, with Gemini requesting the Tariff qualifier with a dropdown of Tariff descriptions.

NoteWhen placing the fields on the data entry screen, be sure to prompt for the parent before the child.

To get the following result

NoteThe Custom Field screen (and Gemini's logic) relates only to simple Field Id/Description matching - simple tables like 'Tariffs' and 'Devices'. More complex tables must be created using a SQL tool such as SQL Management Studio.

Related