Creating lookups

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

A Column can derive its values from

This topic covers providing values to a column using a picklist to enter a new value to a column, and it also covers creating a lookup that will display values from another column.


Tutorial: Data entry 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/DataExpress/Picklist subdirectory of your JBuilder installation.

This application is primarily created in the designer.

  1. Create a new application by following "Retrieving data for the tutorials". This step enables you to connect to a database, read data from a table, and view and edit that data in a data-aware component.
  2. Add another QueryDataSet to the application. This will form the query to populate the list of choices. Set the query property of queryDataSet2 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select COUNTRY from COUNTRY

    Click Test Query. When successful, click OK.

  3. Click the expand icon to the left of the queryDataSet1 component in the component tree to expose all of the columns. Select JOB_COUNTRY.

  4. Open the pickList property editor in the Inspector to bring up the pickListDescriptor. Set the pickList properties as follows:
    Property name Value
    Picklist/Lookup DataSet queryDataSet2
    queryDataSet2 COUNTRY
    Data Type STRING
    Display Column? checked
    queryDataSet1 JOB_COUNTRY

    Click OK.

  5. Click the Source tab. Enter the following code after the call to jbInit(). This opens queryDataSet2, which is attached to the EMPLOYEE_PROJECT 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.

         queryDataSet2.open();
    
  6. Run the application by selecting Run|Run Project.

When the application is running, you can insert a row into the table, and, when it you enter a value for the JOB_COUNTRY field, you can select it from the drop-down pick list. The country you select is automatically inserted into the JOB_COUNTRY field in the EMPLOYEE data set.

Removing a picklist field

To remove a picklist,

  1. Select the column that contains the picklist in the component tree.
  2. Open the pickListDescriptor dialog by clicking in the pickList property in the Inspector.
  3. Set the PickList/Lookup DataSet field to <none>.


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.

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

  1. Create a new application by following "Retrieving data for the tutorials". This step enables you to connect to a database, read data from a table, and view and edit that data in a data-aware component.
  2. Add another QueryDataSet to the application. 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.

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

  4. Click the expand icon to the left of the queryDataSet2 in the component tree 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. 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 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.

  5. Select the Events tab of the Inspector (assuming the new column is still selected in the content 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"));
           }
         }
    
  6. 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();
    
  7. Select Run|Run Project to run the application.

The running application will look like this:

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