A Column can derive its values from
This topic covers providing values to a column as a result of a lookup. There are at least two ways to create a lookup:
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 and supply the lookup calculation to the StorageDataSet object's 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. 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 uses a pick list to fill in values based on the value of a field from another data set. An example of looking up a value in a pick list for data entry purposes is filling in a country for a customer or employee. In this example, the lookup function is implemented using a PickList property setting to provide a pick list of choices for data entry as the GridControl is visited.
Currently the PickList property cannot be set at design time, but the code can be added by hand in the jbinit() method as follows:
column1.setPickList(new PickListDescriptor(queryDataSet1, new String[]{"COUNTRY"}, new String[]{"COUNTRY"}, new String[]{"COUNTRY"}, false));
Only the first PickListDescriptor.pickListColumns column is used to fill in, and only the first PickListDescriptor.pickListDisplayColumns column is used for display. The PickListDescriptor.destinationColumns are ignored - it always sets the column that the editor is bound to. In this example, the editor also assumes that the pickListDataSet does not get edited after the editor is initialized. If the column ItemEditor property is not set, a PickListItemEditor will be used when the column pickList property is set. The PickListItemEditor uses the column pickList property in a simplistic fashion.
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 connections in the tutorials.
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select * from EMPLOYEE_PROJECT |
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select EMP_NO, FIRST_NAME, LAST_NAME from EMPLOYEE |
Property name | Value |
calcType | calculated |
caption | EMPLOYEE_NAME |
columnName | EMPLOYEE_NAME |
dataType | STRING |
The new column will display at the top of the list of columns and as the leftmost column in the grid control. If you had more columns, you could 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 FIRST_NAME and LAST_NAME columns which are used in the calculation expression. Calculated columns are read-only, by default.
void queryDataSet1_calcFields(ReadRow readRow, DataRow dataRow, boolean boolean1) throws DataSetException{ // Instantiate the DataRows the first time this code is executed. When // a DataRow is scoped, we have to wait to do this until its dataset's // columns are known. if (lookupRow == null) { queryDataSet2.open(); lookupRow = new DataRow(queryDataSet2, "EMP_NO"); } if (resultRow == null) resultRow = new DataRow(queryDataSet2); // The EMP_NO from the current row of queryDataSet1 is our lookup // criterion. // 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", readRow.getShort("EMP_NO")); if (queryDataSet2.lookup(lookupRow, employeeRow, Locate.FIRST)) dataRow.setString("EMPLOYEE_NAME", employeeRow.getString("FIRST_NAME") + " " + employeeRow.getString("LAST_NAME")); }
This method is called for calcFields whenever a field value is saved and whenever a row is posted. This event passes in the following:
You might not want to recalculate fields on rows that are not posted yet.
Because the value to be placed in a calculated column is often based on other values in the same row, the calcFields event method provides that row’s values in two DataRows: one that you read values from, another that you place your calculated values into. When doing a lookup, you use these DataRows, but you also need two DataRows for the lookup table: one for the lookup criteria, another for the values returned (if the lookup succeeds). Both rows can be scoped. If they are scoped, they can't be instantiated until their columns are known. In this case, queryDataSet2 is not opened automatically when the frame opens because no control is bound to it, so we open it explicitly.
DataRow lookupRow; DataRow employeeRow;
This concludes the tutorial on creating a lookup with a calculated column. 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 PickListItemEditor component that can be used to set the Column.ItemEditor property. The current row of an employee table obtains the data for its COUNTRY field when the user selects a country from the pick list and 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. For a related example, see ListControl: a data-aware example.
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 |
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select * from CUSTOMER |
Property name | Value |
caption | COUNTRY |
columnName | COUNTRY |
dataType | STRING |
countryColumn.setPickList(new PickListDescriptor(countryDataSet, new String[]{"COUNTRY"}, new String[]{"COUNTRY"}, new String[]{"COUNTRY"}, false));
This concludes the tutorial on creating a lookup with a pick list. 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 CUSTOMER data set.