How to Display a Description from a Lookup Table

Tip: This section doesn’t have anything to do with Picklist Help. However, because the functionality described in this section is in many ways similar to Picklist Help, we decided to put this section at the end of the Picklist Help chapter.

This section describes how to display a description from a “lookup” table, view or CursorAdapter on a form for a code field that is bound to a textbox.

Let’s say you are displaying a field named “Code” from a table named “Master” in a textbox on a form. When the form is displayed, you want to:

·       Use the current value of the Master.Code field to locate a record in a lookup table named “Lookup” that contains a Description field for the code, and

·       Display the contents of the Description field in a Description textbox. The Description textbox should be based on the VPME Textbox_Output class.

The Lookup table has one record for each valid code value. The code values are contained in a field in the Lookup table that’s also named “Code”. The Lookup table has an index tag named “Code” on its Code field. This tag will be used to locate the lookup table record for the current value of the Master.Code field.

Ø To display the Description for the Code field

1.   In the form’s Data Environment, add the Lookup table. There is no need to set a relation into the Lookup table or to set the Lookup table’s Order property.

2.   In the Valid event of the Master.Code textbox, enter the following code:

   <DescriptionTextboxObjectReference>.Refresh( )

   RETURN DODEFAULT( )

   Example:

   ThisForm.txtLookupDescription.Refresh( )

   RETURN DODEFAULT( )

3.   In the Refresh method of the Description textbox, enter the following code:

   This.Value = IIF(SEEK(Master.Code,’Lookup’,’Code’),;    Lookup.Description,’’)

Of course, when you implement this procedure for your own code fields and lookup tables, views and CursorAdapters, replace:

·       “Master.Code” with the table, view or CursorAdapter alias and name of your code field.

·       “Lookup” with the alias of the your lookup table, view or CursorAdapter.

·       “Code” with the name of the lookup table, view or CursorAdapter tag on its code field.

Tip: If you are using a lookup view or CursorAdapter, make sure that the tag is defined in the Data Builder and that the Create in Form check box is checked on the tag’s Edit page.

·       “Lookup.Description” with the lookup table, view or CursorAdapter alias and name of the description field.

Ø To display a description from a lookup table in a grid:

1.   In the form’s Data Environment, add the lookup table, view or CursorAdapter. There is no need to set a relation into the lookup table or to set the lookup table’s Order property.

2.   Add a column to the grid.

3.   In the ControlSource property of the new column, enter the following expression:

IIF(SEEK(Master.code,’Lookup’,’Code’),Lookup.Description,’’)

 

 

More:

Creating Application Objects