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.
This type of lookup displays a list of choices in a drop-down list. The choices that populate the list come the unique values of a column of another data set. The tutorial gives the steps for looking up a value in a picklist for data entry purposes, in this case for selecting a country for a customer or employee. In this example, the pickList
property of a column allows you to define which column of which data set will used provide values for the picklist. The choices will be available for data entry in a visual component, such as a table, when the application is running.
There are many other things you can do with a picklist. For more ideas, see the pick list sample in the dbSwing samples directory.
"Creating a lookup using a calculated column" 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. 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.
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 cursor to the first row that matches the specified set of criteria. For more information on the locate()
method, see "Locating data".
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 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.
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 option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select COUNTRY from COUNTRY |
Click Test Query. When successful, click OK.
Click the expand icon to the left of the queryDataSet1
component in the component tree to expose all of the columns. Select JOB_COUNTRY
.
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.
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();
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.
To remove a picklist,
pickListDescriptor
dialog by clicking in the pickList
property in the Inspector.
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".
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 option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select * from EMPLOYEE_PROJECT |
Click Test Query. When successful, click OK.
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.
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.
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")); } }
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();
Select Run|Run Project to run the application.
The running application will look like this:
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.