Ability enables you to create enumerated fields through which you can select, from a drop-down list, one of a range of items entered by you into an enumerated list, for example, Blue, Red, Yellow.
However, in many cases you may find that the items you wish to use for an enumerated list have already been created as part of another table in your database, for example, you may have already created a field for the colors of the rainbow: Violet, Indigo, Blue, Green, Yellow, Orange, Red. It would be pointless creating a fresh enumerated list if the items of the list were already stored elsewhere in your database, especially when the list is of some length.
Lookup fields can be used in a table grid to allow you to select items in another table. These items may be the values held in a particular field, the titles themselves of all the fields, or a mixture of existing values and new values typed by you.
Create the lookup field as follows:
First, make sure you are in the Format Table dialog box: If the table already exists, right-click over the table name in Database Manager and select Format (alternatively open the table and select Table from the Format menu). If you are creating the table from scratch you will be in Format Table anyway.
Add a field name and select a Type e.g. Character.
Click on the Format button in the dialog box.
Click on the Lookup tab in the Format Field box.
In the Display Control line select Combo Box from the drop-down menu. This displays further options for you to set.
In the Row Source Type line there are three options:
Value List: Type the values in the Row Source line below rather than link to an existing field (enter the values separated by semicolons).
Table/Query: Access data from a table or query, which you select in the Row Source line below (click on the drop-down arrow to see the available tables/queries).
Field List: Access field titles from a table or query, which you select in the Row Source line below (click on the drop-down arrow to see the available tables/queries).
Next, once you have made your selection in the Row Source line, you need to choose which column will be the source of the data that will be displayed in your table. This is done using the Column Count and Bound Column lines. (The Bound Column is the one that contains the values you want to display in the field; the Column Count tells Ability how many columns to display in the drop-down list for the Lookup Field. In this way, you can see what data the Bound Column is connected with, even though it is only the values in the Bound Column that will be inserted into your table.) For example: on the assumption that you have selected Table/Query in Row Source Type and selected a particular table or query in Row Source, you need to set the column count and bound column values as follows: If the table you are accessing has three columns and the particular column you want to use e.g. a column with the colors of the rainbow, is the second of these three, then you need to set Column Count to at least 2 and the Bound Column value at 2. This ensures that the Lookup Field will display 2 fields in its drop-down list, hence including column 2, the one you want, and that it will be a value from column 2 that will be displayed in the actual table. The Bound Column value should always be lower than or equal to Column Count.
The Column Widths enables you to set a width for all the columns included by Column Count. The widths should be separated by a semi-colon e.g. 3cm;3cm. Note that if you don’t set a width, a default width will be used instead - this is usually enough for most needs.
Limit to List: set at Yes if you want to restrict the Lookup Field to the items in the accessed table column, and at No if you want to be able to type your own entries in addition to the accessed table items. With a No setting, you can type new entries directly into the table under this field, as well as choose values from the Lookup Field list.
List Rows imposes a maximum number of items to be used in the Lookup Field. If this was set at 7 and you wanted to add another color to Violet, Indigo, Blue, Green, Yellow, Orange, Red, then you would have to increase it to at least 8. The default is 8.
List width determines the width of the drop-down list. The default is the values in Column Widths combined.
Once you have made your settings, click on OK to close the Format Field box.
Back in your table, click on the Table Lookup field to select an item from those listed.