Tip of the Month (December 2007)

Grid Controls and Lookups

A grid control is a handy way to display mutiple records from another table that are related to the record currently being displayed. For example, you may have a form built over the ORDER table and use a grid to display records in the ORDER_ITEM table that represent the individual parts of that order.

But grid controls have a limitation: a grid can only display data that is actually stored in that "other table". In particular, grids cannot do "lookups". Suppose your ORDER_ITEM table only had a PART_NUMBER column, but no PART_NAME column (and there is separate PART table that has PART_NUMBER and PART_NAME columns). In this case, the grid (showing records from the ORDER_ITEM table) would only be able to display the PART_NUMBER but not the PART_NAME.

However, you still can have your form display the PART_NAME. When the user clicks on an entry in the grid, you can use a lookup control on the form display the related PART_NAME.

One of the properties of the grid controls is the "Set ... to current ..." which allows you to specify a variable and a column. When the user selects an entry in the grid, the variable is set to the value for that column of the selected entry. Use this to set @var(34) to the items's PART_NUMBER.

Then, on your form, create a lookup control, retriveing values from the PART table, keyed on @var(34), searching PART_NUMBER, and retrieving PART_NAME. You probably should also turn on the FORM | AUTORECALC.

When theform is run on the handheld, as the user selects the different items in the order, the name of the part will appear in the lookup control.