A Column can derive its values from
This topic covers providing values to a column as a result of a lookup or via a picklist.
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 specify the criteria in the pickList property editor. 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. A tutorial for creating this type of lookup field follows.
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 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 further in this topic 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 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 control, such as a grid, when the application is running.
See also:
Working with columns
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.
For this option | Make 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 database connections in the tutorials.
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select * from EMPLOYEE_PROJECT |
Place SQL text in resource bundle | unchecked |
Test the query to ensure its validity. When successful, click OK.
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select EMP_NO, FIRST_NAME, LAST_NAME from EMPLOYEE |
Place SQL text in resource bundle | unchecked |
Test the query to ensure its validity. When successful, click OK.
Property name | Value |
calcType | lookup |
caption | EMPLOYEE_NAME |
columnName | EMPLOYEE_NAME |
dataType | STRING |
The new column will display in the list of columns and in the grid 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 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 LAST_NAME column which is specified later as the lookup column. Calculated columns are read-only, by default.
Field | Value |
Picklist/Lookup Dataset | queryDataSet2 |
queryDataSet2 | EMP_NO |
queryDataSet1 | EMP_NO |
Lookup column to display | LAST_NAME |
When you click OK, you will see the correct last name displayed in the grid.
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.
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/borland/samples/tutorial/dataset/Picklist directory of your JBuilder installation.
This application is primarily created in the designer.
For this option | Make 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.
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select COUNTRY from COUNTRY |
Place SQL text in resource bundle | unchecked |
Test the query to ensure its validity. When successful, click OK.
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select EMP_NO, FIRST_NAME, JOB_COUNTRY from EMPLOYEE |
Place SQL text in resource bundle | unchecked |
Test the query to ensure its validity. When successful, click OK.
Property name | Value |
Picklist/Lookup Dataset | queryDataSet1 |
queryDataSet1 | COUNTRY |
Data Type | STRING |
Display Column? | checked |
queryDataSet2 | JOB_COUNTRY |
Click OK.
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 EMPLOYEE data set.