Creating lookups

A Column can derive its values from

This topic covers providing values to a column as a result of a lookup. There are at least two ways to create a lookup:

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.

  2. Select File|New|Application from the menu. Accept all defaults.

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

  4. Add a Database component and 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.

  5. Add a QueryDataSet component. 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

  6. 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

  7. 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.

  8. 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 calculated
    caption EMPLOYEE_NAME
    columnName EMPLOYEE_NAME
    dataType STRING

    The new column will display at the top of the list of columns and as the leftmost column in the grid control. If you had more columns, you could 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 FIRST_NAME and LAST_NAME columns which are used in the calculation expression. Calculated columns are read-only, by default.

  9. Select the queryDataSet1 object, then select and double-click the calcFields event handler from the Events tab of the Property Inspector. This creates the stub for the event's method in the Source window.

  10. Modify the event to look up the employee name that is associated with the employee number on the same row, as follows:
    void queryDataSet1_calcFields(ReadRow readRow, DataRow dataRow, 
       boolean boolean1) throws DataSetException{
    
        // Instantiate the DataRows the first time this code is executed.  When
        // a DataRow is scoped, we have to wait to do this until its dataset's
        // columns are known.
    
        if (lookupRow == null) {
          queryDataSet2.open();
          lookupRow = new DataRow(queryDataSet2, "EMP_NO");
        }
    
        if (resultRow == null)
          resultRow = new DataRow(queryDataSet2);
       
        // The EMP_NO from the current row of queryDataSet1 is our lookup
        // criterion.
        // We look for the first match, since EMP_NO is unique.  If the lookup
        // succeeds, concatenate the name fields from the employee data, and put
        // the result in dataRow; otherwise, let the column remain blank.
    
        lookupRow.setShort("EMP_NO", readRow.getShort("EMP_NO"));
        if (queryDataSet2.lookup(lookupRow, employeeRow, Locate.FIRST))
          dataRow.setString("EMPLOYEE_NAME", employeeRow.getString("FIRST_NAME") +
            " " + employeeRow.getString("LAST_NAME"));
      }
    

    This method is called for calcFields whenever a field value is saved and whenever a row is posted. This event passes in the following:

    You might not want to recalculate fields on rows that are not posted yet.

    Because the value to be placed in a calculated column is often based on other values in the same row, the calcFields event method provides that row’s values in two DataRows: one that you read values from, another that you place your calculated values into. When doing a lookup, you use these DataRows, but you also need two DataRows for the lookup table: one for the lookup criteria, another for the values returned (if the lookup succeeds). Both rows can be scoped. If they are scoped, they can't be instantiated until their columns are known. In this case, queryDataSet2 is not opened automatically when the frame opens because no control is bound to it, so we open it explicitly.

  11. Near the top of the Frame1.java file, just before the constructor for the class, define the DataRows used in the previous step:
        DataRow lookupRow;
        DataRow employeeRow;
    

  12. Run the application to view the resulting lookup calculation.

This concludes the tutorial on creating a lookup with a calculated column. 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 pick list

This tutorial shows how to create a PickListItemEditor component that can be used to set the Column.ItemEditor property. The current row of an employee table obtains the data for its COUNTRY field when the user selects a country from the pick list and 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. For a related example, see ListControl: a data-aware example.

This application is primarily created in the designer.

  1. Select File|Close All from the menu.

  2. Select File|New|Application from the menu. Accept all defaults.

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

  4. Add a Database component and 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.

  5. Add a QueryDataSet component. This will form the query to populate the lookup table. Set the query property of queryDataSet1 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select COUNTRY from COUNTRY

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

    Database database1
    SQL Statement select * from CUSTOMER

  7. Select Frame1.java from the Structure pane. Select the Design tab of the UI Designer. Click the + sign to the left of the queryDataSet2 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
    caption COUNTRY
    columnName COUNTRY
    dataType STRING

  8. Add a GridControl and set its dataSet property to queryDataSet2.

  9. Select the Source tab of the UI Designer and add the following code snippet. Locate the code that was automatically written when the column was added and add this code below that:

    countryColumn.setPickList(new PickListDescriptor(countryDataSet,
                                                     new String[]{"COUNTRY"},
                                                     new String[]{"COUNTRY"},
                                                     new String[]{"COUNTRY"},
                                                     false));
    

  10. Run the application.

This concludes the tutorial on creating a lookup with a pick list. 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 CUSTOMER data set.