Creating lookups

A Column can derive its values from

This topic covers providing values to a column as a result of a lookup or via a picklist.

See also:
Working with columns


Tutorial: Creating a lookup using a calculated column

This tutorial shows how to use a calculated column to search and retrieve an employee name (from EMPLOYEE) for a given employee number in EMPLOYEE_PROJECT. This type of lookup field is for display purposes only. The data this column contains at run time is not retained because it already exists elsewhere in your database. The physical structure of the table and data underlying the data set is not changed in any way. The lookup column will be read-only by default. This project can be viewed as a completed application by running the sample project Lookup.jpr, located in the samples/borland/samples/tutorial/dataset/Lookup directory of your JBuilder installation.

For more information on using the calcFields event to define a calculated column, refer to Using calculated columns.

This application is primarily created in the UI Designer.

  1. Select File|Close All from the menu. Select File|New from the menu. Double-click the Application icon. Accept all defaults.

  2. Select the Frame file from the Navigation pane, then select the Design tab to begin adding components.

  3. Add a Database component by clicking on the Database component from the Data Express tab and then clicking in the Component tree. In the Inspector, set its connection property as follows:
    For this optionMake this choice

    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    Test the connection to ensure its validity. If not successful, see Troubleshooting JDBC database connections in the tutorials.

  4. Add a QueryDataSet component from the Data Express tab of the palette to the Component tree. This will provide data to populate the base table where we later add columns to perform lookups to other tables. Set the query property of queryDataSet1 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select * from EMPLOYEE_PROJECT
    Place SQL text in resource bundle unchecked

    Test the query to ensure its validity. When successful, click OK.

  5. Add another QueryDataSet component. This forms the query that provides the looked up data to the lookup field. Set the query property of queryDataSet2 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select EMP_NO, FIRST_NAME, LAST_NAME from EMPLOYEE
    Place SQL text in resource bundle unchecked

    Test the query to ensure its validity. When successful, click OK.

  6. Add a GridControl and set its dataSet property to queryDataSet1. This will enable you to view data in the designer and when the application is running.

  7. In the Component tree, click the + sign to the left of the queryDataSet1 component to expose all of the columns. Select <new column> and set the following properties in the Inspector for the new column:
    Property name Value
    calcType lookup
    caption EMPLOYEE_NAME
    columnName EMPLOYEE_NAME
    dataType STRING

    The new column will display in the list of columns and in the grid control. You can manually edit the setColumns() method to change the position of this or any column. No data will be displayed in the lookup column in the grid in the designer. The lookups are only visible when the application is running. The data type of STRING is used here because that is the data type of the LAST_NAME column which is specified later as the lookup column. Calculated columns are read-only, by default.

  8. Select the pickList property in the Inspector. The pickList property editor opens. Set the following properties to look up EMP_NO in QueryDataSet2 and display the appropriate LAST_NAME field in the new column.
    Field Value
    Picklist/Lookup Dataset queryDataSet2
    queryDataSet2 EMP_NO
    queryDataSet1 EMP_NO
    Lookup column to display LAST_NAME

    When you click OK, you will see the correct last name displayed in the grid.

  9. Select Run|Run to run the application.

When the application is running, the values in the calculated lookup column will automatically adjust to changes in any columns, in this case the EMP_NO column, referenced in the calculated values. If the EMP_NO field is changed, the lookup will display the value associated with the current value when that value is posted.


Tutorial: Looking up choices with a picklist

This tutorial shows how to create a picklist that can be used to set the value of the JOB_COUNTRY column from the list of countries available in the COUNTRY table. When the user selects a country from the picklist, that selection is automatically written into the current field of the table. This project can be viewed as a completed application by running the sample project Picklist.jpr, located in the samples/borland/samples/tutorial/dataset/Picklist directory of your JBuilder installation.

This application is primarily created in the designer.

  1. Select File|Close All from the menu. Select File|New from the menu. Double-click the Application icon to create a new application. Accept all defaults.

  2. Select the Frame file from the Navigation pane, then select the Design tab to begin adding components.

  3. Add a Database component from the Data Express tab of the palette to the Component tree. Set its connection property as follows:
    For this optionMake this choice

    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    Test the connection to ensure its validity. If not successful, see Troubleshooting JDBC connections in the tutorials.

  4. Add a QueryDataSet component from the Data Express tab of the palette to the Component tree. This will form the query to populate the list of choices. Set the query property of queryDataSet1 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select COUNTRY from COUNTRY
    Place SQL text in resource bundle unchecked

    Test the query to ensure its validity. When successful, click OK.

  5. Add another QueryDataSet component. This will form the query to populate the grid with information from the EMPLOYEE table. Set the query property of queryDataSet2 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select EMP_NO, FIRST_NAME, JOB_COUNTRY from EMPLOYEE
    Place SQL text in resource bundle unchecked

    Test the query to ensure its validity. When successful, click OK.

  6. Add a GridControl from the JBCL tab of the palette to the UI Designer. Set its dataSet property to queryDataSet2.

  7. Add a NavigatorControl from the JBCL tab of the palette to the UI Designer. Set its dataSet property to queryDataSet2.

  8. Click the + sign to the left of the queryDataSet2 component in the Component tree to expose all of the columns. Select JOB_COUNTRY.

  9. Double-click the pickList property in the Inspector to bring up the pickListDescriptor. Set the pickList properties as follows:
    Property name Value
    Picklist/Lookup Dataset queryDataSet1
    queryDataSet1 COUNTRY
    Data Type STRING
    Display Column? checked
    queryDataSet2 JOB_COUNTRY

    Click OK.

  10. Run the application by selecting Run|Run.

When the application is running, you can select a row in the grid and pick a country from the list. The country you select is automatically inserted into the JOB_COUNTRY field in the EMPLOYEE data set.