This section discusses using a lookup field to display values from a column of another data set.
This type of lookup retrieves values from a specified table based on criteria you specify and displays it as part of the current table. 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.
The lookup() method uses specified search criteria to search for the first row matching the criteria. When the row is located, the data is returned from that row, but the cursor is not moved to that row. The locate() method is a method that is similar to lookup(), but actually moves the
C r e a t i n g l o o k u p s cursor to the first row that matches the specified set of criteria. For more information on the locate() method, see “Locating data” on page 11-14.
The lookup() method can use a scoped DataRow (a DataRow with less columns than the DataSet) to hold the values to search for and options defined in the Locate class to control searching. This scoped DataRow will contain only the columns that are being looked up and the data that matches the current search criteria, if any. With lookup, you generally look up values in another table, so you will need to instantiate a connection to that table in your application.
This example 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.jpx, located in the /samples/DataExpress/Lookup directory of your JBuilder installation.
For more information on using the calcFields event to define a calculated column, refer to “Using calculated columns” on page 12-7.
1 Create a new application by following “Retrieving data for the examples” on page 11-2.
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:
3 Click Test Query, and when successful, click OK to close the dialog box.
4 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.
For this option Make this choice
Database database1
SQL Statement select * from EMPLOYEE_PROJECT
C r e a t i n g l o o k u p s
5 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:
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.
6 Select the Events tab of the Inspector (assuming the new column is still selected in the content pane), and select, then double-click the
calcFields event.
The cursor is positioned in the appropriate location in the Source pane.
7 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"));