Creating lookups

This type of lookup retrieves values from a specified table based on criteria you specify and displays it as part of the current table. In order to create a calculated column, you need to create a new Column object in the StorageDataSet, set its calcType appropriately, and code the calcFields event handler. The lookup values are only visible in the running application. Lookup columns can be defined and viewed in JBuilder, but JBuilder-defined lookup columns are not resolved to or provided from its data source, although they can be exported to a text file.

An example of looking up a field in a different table for display purposes is looking up a part number to display a part description for display in an invoice line item or looking up a zip code for a specified city and state.


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/DataExpress/Lookup subdirectory of your JBuilder installation.

  1. Close all open projects (select File|Close Project).

  2. Select File|New Project.

  3. Enter a name and location for the project in the Project Wizard. Click Finish.

  4. Select File|New from the menu. Double-click the Application icon.

  5. Specify the package name and class name in the Application Wizard. Click Finish.

  6. Select the Design tab to activate the UI designer.

  7. Click the Database component on the Data Express tab of the component palette, then click in the Structure pane to add the component to the application.

    Open the connection property editor for the Database component by selecting, then double-clicking the connection property ellipsis in the Inspector. Set the connection properties to the JDataStore sample employee table as follows. The Connection URL points to a specific installation location. If you have installed JBuilder to a different directory, point to the correct location for your installation.
    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL jdbc:borland:dslocal:/usr/local/jbuilder/samples/JDataStore/ datastores/employee.jds

    The employee.jds database is located under the samples directory of your JBuilder installation, which may be different on your system. This sample has been modified to find the file on your system.

    Username Enter your name
    Password not required

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed in the status area. When the connection is successful, click OK.

  8. Add a QueryDataSet component to the designer by clicking on the QueryDataSet component on the Data Express tab and then clicking in the Structure pane.

    Select the query property of the QueryDataSet component in the Inspector, double-click its ellipsis to open the QueryDescriptor dialog, and set the following properties:
    Property name Value
    Database database1
    SQL Statement SELECT * FROM EMPLOYEE

    Click Test Query to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  9. Select a DBDisposeMonitor component from the More dbSwing tab. Click in the Structure pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

  10. Set the DBDisposeMonitor's dataAwareComponentContainer property to this.

To view the data in your application we need to add some UI components and bind them to the data set. To do this,

  1. Select contentPane(BorderLayout) in the Structure pane. In the Inspector, set its layout property to null.

  2. Select a JdbNavToolBar component from the dbSwing tab. Drop the component in the area slightly above the panel in the Design frame. JdbNavToolBar automatically attaches itself to whichever DataSet has focus, so you do not need to set its dataSet property.

    The JdbNavToolBar will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.

  3. Click on the JdbStatusLabel component on the dbSwing tab of the component palette. Drop the component in the area slightly below the panel in the Design pane. JdbStatusLabel automatically attaches itself to whichever DataSet has focus, so you do not need to set its dataSet property.

    Among other information, the status label displays information about the current record or current operation.

  4. Add a TableScrollPane component from the dbSwing tab to the designer.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  5. Drop a JdbTable component from the dbSwing tab into the TableScrollPane component. Set its dataSet property to queryDataSet1.

    You'll notice that the designer displays live data at this point.

To add the lookup functionality,

  1. Add another QueryDataSet component from the Data Express tab of the palette to the Structure pane. 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 queryDataSet2 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select * from EMPLOYEE_PROJECT

    Click Test Query. When successful, click OK.

  2. Select the JdbTable in the Structure pane, and change its dataSet property to queryDataSet2. This will enable you to view data in the designer and in the running application.

  3. In the Structure pane, click the expand glyph 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
    calcType CALC
    caption EMPLOYEE_NAME
    columnName EMPLOYEE_NAME
    dataType STRING

    The new column will display in the list of columns and in the table control. No data will be displayed in the lookup column in the table 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.

  4. Select the Events tab of the Inspector (assuming the new column is still selected in the Structure pane). Select, then double-click the calcFields event. The cursor is positioned in the appropriate location in the Source pane. Enter the following code, which actually performs the lookup and places the looked-up value into the newly-defined column.

    void queryDataSet2_calcFields(ReadRow changedRow, DataRow calcRow,
            boolean isPosted) throws DataSetException{
        // Define a DataRow to hold the employee number to look for in
        // queryDataSet1, and another to hold the row of employee data
            // that we find.
        DataRow lookupRow = new DataRow(queryDataSet1, "EMP_NO");
        DataRow resultRow = new DataRow(queryDataSet1);
    
            // The EMP_NO from the current row of queryDataSet2 is our
            // lookup criteria.
            // 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", changedRow.getShort("EMP_NO"));
        if (queryDataSet1.lookup(lookupRow, resultRow, Locate.FIRST))
                    calcRow.setString("EMPLOYEE_NAME",
                            resultRow.getString("FIRST_NAME") +
                            " " + resultRow.getString("LAST_NAME"));
      }
    }
    

  5. Click the Source tab. Enter the following code after the call to jbInit(). This opens queryDataSet1, which is attached to the EMPLOYEE table. Normally, a visual, data-aware component such as JdbTable would open the data set for you automatically, but in this case, there is no visual component attached to this data set, so it must be opened explicitly.

    queryDataSet1.open();
    

  6. Select Run|Run Project to run the application.

Change a value in the EMP_NO field to a valid employee number. 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.