The relationship to a table can be defined in two different places—as part of the table description or as part of the form description. In both places, the relationship is defined in the TableRelation property of the field or control. There is no functional difference between a table relationship defined at the table level and a relationship defined at the form level. But there is a difference when you are designing an application. If the relationship is defined at the table level, all the text boxes in the forms that have a direct relationship to the field will automatically have the lookup functionality. You can suppress this function by setting the Lookup property of the text box to No.

Defining the Table Relationship

To define a table relationship

  1. Open the form in the Form Designer.

  2. Open the Properties window (SHIFT+F4) of the field or control.

  3. In the Value field of the TableRelation property, click the AssistButton.

  4. In the Table Relation window, enter the name of the table that you want to look up in the Table field or choose it from the list that appears when you click the look up button.

  5. In the Field field, enter the name of the field in the table (or choose it from the list).

You can use the Condition and the Table Filter fields to create a more advanced relationship.

By using the Condition field, for example, you can look up different tables, depending upon the value of a field in the current table. Each condition line corresponds to a statement in an if…then...else…if sequence.

In the Table Filter field, you can set a filter on the lookup table.

Validating Entries

Entries can easily be validated against the contents of a field in a related table. If you set the ValidateTableRelation property to Yes—either at the field level or at the control level—only entries that exist in the related table will be accepted.

If you need a more advanced validation, you can write C/AL code in the OnValidate trigger of either the control or the field.

Using the Default Lookup or Writing Your Own

If you want more control over how a lookup functions by just using conditions and filters, you can write C/AL code in the OnLookup trigger. This allows you to bypass the default lookup function and write your own.

The following rules determine how a lookup function is performed:

  • A trigger at the form level takes precedence over a trigger at the table level.

  • Triggers take precedence over the system default action.

Defining a Lookup Form

When you are using the system lookup function, you must define which form to use to display the results of the lookup. You can define the form in two ways:

  • Each table can have a form that is used for accessing the table. You can do this by setting the LookUpFormID property of the table.

  • The form can be defined by setting the LookUpFormID property of the control for which the lookup is provided.

If both properties are set, the form that is defined as a control property is used.

If no lookup form is defined either at the table or the form level (although the text box contains a lookup button), a lookup is not performed when the button is clicked. If you are writing a lookup function in the OnLookup trigger, you have to explicitly run a form by using the RUNMODAL C/AL function.

NoteNote

If you always design a basic tabular form for a table and enter this form as the LookupForm (and DrillDownForm) for the table. If you later decide that this form is not adequate for some lookups, you can add customized forms as control properties.

Permanent Assist

The PermanentAssist property is a control property. If set to Yes, the lookup button is permanently displayed; otherwise, it is displayed only when the control has the focus.

Looking Up in the Current Table

By setting the Lookup property for a text box to Yes, you can access the source table of the form. This makes it easy for you to select records. In effect, the lookup provides a list of all the records in the table and you can select a record from the list, and this record then becomes the current record.

A lookup form must be defined either at the table or form level in the same way as it must be defined when the lookup is in another table. However, you cannot set conditions and filters as you can when the lookup is in another table. The default behavior is to display all the records in the table. If you need to change this, write your own lookup function in the OnLookup trigger.

If a lookup in a related table is defined—regardless of how the relation is defined—setting the Lookup property to Yes is overridden. But if Lookup is explicitly set to No (as opposed to its default value <No>), no lookups, including to related tables, are performed.

You can provide the same functionality by using the LookupTable action (applicable to command buttons and menu items). You can provide both types of lookup on the same form: lookups to related tables from text boxes, and lookups to the source table from command button actions