The IBM data access tool provides a way to build and share database applications easily in IBM Workplace Collaboration Services. IBM data access comes with data access designer for creating new applications and editing existing ones and data access viewer for viewing and modifying the data in a database application.

A database application can consist of forms and grids that allow data to be viewed and edited. Reports can also be created to view application data and summaries calculated from the data. Each application has an associated data definition that defines the database tables used by the application. IBM data access tool includes a set of sample applications that you can use as a starting point to create your own applications.

In this article, we describe the development of a simple call tracking database application. It explains at a high level how we created this application and points you to the correct location in the data access designer help for detailed step-by-step instructions on how to perform certain operations in data access designer.

For a basic understanding of the fundamentals of the IBM data access tool, read the developerWorks article, "What is the IBM data access tool?"

The database application

The sample application that comes with this article is a simple customer registration and call tracking application. This is a simplified version of the sample application that comes with the product. Follow these steps to run the sample application:

1. Download the sample application JAR file.
2. Import the sample application into the IBM Workplace Client Technology, rich edition. Click the Applications icon in the Workplace rich client to open the Applications catalog. Click the Import button to import the database application.
3. Open the application. Refer to the Application catalog help for more information on opening applications.

The sample application Main Menu form opens by default.

This form contains a number of links for easy navigation to other parts of the application. This form is bound to a dummy table because it is never used for entering data, but each design element must be bound to a table in the data definition.

NOTE: Some of the options in the toolbar -- New, Save, Delete, and so on -- are disabled because this form contains no controls that allow for the editing of data.

You can open the Edit Customer form by clicking the Customers link from the Main Menu or using the element navigator (the Show drop-down box in the top action bar of the screen).

This form is used to create new customer records. The user must enter a unique customer ID, a contact name, a company name, an address, and a phone number. You can view all the customers at once using the All Customer grid.

You can open the Edit Customer Call form by clicking the Log Customer Call link from the Main Menu or using the element navigator.

Use this form to create new customer call records; a new blank record is created when the form is opened. The user must enter a unique call ID and specify a customer ID from the list of available options. If the user does not specify a unique ID, he receives the following error message: "Unable to save data because it would have caused a duplicate key value in a unique or primary key." New customer call records are open by default.

There is one report provided as part of the sample application, Open Calls by Customer.

Creating the application

The following steps are performed to create the Call Tracking sample application:

1. Create a new application in the IBM data access tool.
2. Create the data definition.
3. Create the Edit Customer, Edit Customer Call, and Main Menu forms.
4. Create the All Customers grid.
5. Create the Open Calls by Customer report.
6. Add simple actions.

Create a new application
You can create this application from scratch or import the sample application provided with this article. If you have not imported the sample application and want to create an application, in the Applications catalog, click the New button. For help creating a new application, refer to the "Creating a database application" topic in the data access designer help.

Create the data definition
The data definition for this application consists of three tables with the following columns:

For help on creating a new data definition, refer to the "Creating a data definition" topic in the data access designer help.

Create the Edit Customer and Edit Customer Call forms
The Edit Customer form is used to create new customer records. The CUSTOMER table is selected when creating this form. By default, all the fields are created as edit controls because that is what is specified in the data definition. We want the address to contain multiple lines of text, so we want to change that field control to a multiline edit control. Use the following steps to do this:

1. Delete the existing address edit control.
2. Drag a Multiline Edit control from the palette into the form.
3. Select the data binding property for this new control and select the ADDRESS:CUSTOMER binding.

Now we have a form with all the fields we need, and we can use the Design editor to resize and position the controls as desired and also to change the default labels for each control to more user friendly text. Depending upon where you position the controls, the default tab order may not be what the user would expect. The tab order determines which control has focus next when the user presses the Tab key. For example, if when you inserted the new multiline edit control for the ADDRESS column, you positioned it above the PHONE field, the default tab order takes you to that edit control before the multiline edit control. To correct this issue, you can use the Attributes editor to specify the tab order for each control. To change the tab order for a control, use the following steps:

1. Select the control in the Design editor.
2. Select the Basics tab in the Attributes editor if not already there.
3. Enter a value in the Tab order field. The control with the lowest tab order is the first tab.

NOTE: On Windows systems, if you press the Tab key in a multiline edit control, a tab is inserted into the text. To tab out of that control, press CTRL+Tab.

The Edit Customer Call form is used to create new customer call records. The CUSTCALL table is selected when creating this form. The columns from the CUSTOMER table that is joined using the relationship are not selected by default. Do not select any of these columns because this will prevent you from using this form to create new customer call records. The default field for the PROBLEM_DESC is changed to a multiline edit control as described previously. This form also contains two combination boxes. After you create this form and preview it, you receive the following error message:

Form contains illegal binding expression.

This happens because the options for the PROBLEM_STATUS combination box have not been configured. To configure the options, follow these steps:

1. Select the PROBLEM_STATUS combination box.
2. Select the Options tab in the Attributes editor.
3. Click the Add button to add the following options:
   • Open
   • Closed

Previewing the form works correctly now because the CUST_ID combination box already has default options configured because the CUST_ID column was specified as a foreign key column. However, the default option lists the available customer ID values, which is not very user friendly. Instead, we want the company names to display. We do that as follows:

1. Select the CUST_ID combination box.
2. Select the Options tab in the Attributes editor.
3. Select Add Columns and specify the CUST_ID and COMPANY_NAME columns from the CUSTOMER table.
4. Select the Select label checkbox and select COMPANY_NAME from the drop-down list.

For help creating and previewing a new form and working with controls, refer to the data access designer help topics: "Creating a form," "Previewing a design element," and "Working with controls and decorations."

Create the All Customers grid
The All Customers grid is used to view all customer records and can also be used to add new records and to delete and modify existing records. The CUSTOMER table is selected when creating this grid, but the ADDRESS column is omitted because we cannot use a multiline edit control within a grid. The default labels for the grid columns are the names of the table columns from the data definition editor. It is a good idea to change these to more user friendly strings.

For help on creating a new grid, refer to the "Creating a grid" topic in the data access designer help.

Create the Open Calls by Customer report
The Open Calls by Customer report groups the open call records by customer. The CUSTCALL table is selected when creating this report and also the option to have groups and totals. The following columns are included in this report:

• CUST_CALL:CALL_ID
• CUST_CALL:CUST_ID
• CUST_CALL:PROBLEM_STATUS
• CUSTOMER:COMPANY_NAME

To ensure this report contains only customer call records that are in an open state, the following condition is applied:

CUST_CALL:PROBLEM_STATUS LIKE Open

The customer call records in the report are ordered and grouped using the CUST_ID column. A summary is added to count the number of open calls per customer and also the total number of open calls. The PROBLEM_STATUS column is deleted from the report because all the records will have the same status.

For help creating a new report, refer to the data access designer help topics, "Creating a report" and "Creating summaries for a report."

Add simple actions
Simple actions are used in this sample application on the following design elements:

• Edit Customer Call
• Main Menu

The Edit Customer Call form was described earlier in the article. We want to add some logic so that a new blank record is created each time the form is opened. To achieve this goal, we add a simple action to the form as follows:

1. Open the form in the data access designer.
2. Select the Event tab of the Attributes editor for the form (you may need to click the background of the form first).
3. Select the On Load event.
4. Add the Insert New Record simple action.

If you preview this form now, notice that a new blank record is created when the form is opened.

The Main Menu form is the default design element for this application and lets the user navigate to other parts of the application. The dummy table is selected when creating this form. Because the form is not used for editing any records, the option to generate the form automatically from the selected table is not selected. Alternatively, you can delete the automatically generated controls from the form after it is created. Press CTRL+A to select all the controls in the form at once, and then press the Delete key.

NOTE: The reason we use a dummy table is because when you add navigation from one design element to another, the data access viewer tries to open the most appropriate record in the design element being opened. This can lead to unexpected results if you use On Load event handlers, which we do. If the Main Menu is bound to the CUSTCALL table, then we use the Load simple action to open the Edit Customer Call form. When the form is opened, a new blank record is created. However, the data access drill-down functionality forces whichever record the Main Menu is referencing to be opened in the Edit Customer Call form.

To add hyperlinks for navigation, use the following steps:

1. Drag a hyperlink control from the palette onto the form.
2. Edit the label from the hyperlink, so it describes where in the application it will navigate to.
3. Select the Event tab in the Attributes editor.
4. Add a Load simple action to the On Activate event. The resource parameter will specify the design element to load.

In the sample application, the Main Menu form contains hyperlinks to navigate to all the other design elements in the application. It also contains decorations to give the Main Menu a title and a logo. To add the logo, do the following:

1. In data access designer, select Images in the Database Applications navigator.
2. Select Import Image and import the image that you want to use as a logo.
3. Go back to the Design editor and drag an image from the palette onto the form. The logo you import appears in the list of available images. You can also directly import an image from the dialog box that appears when you drag and drop an image from the palette.

For help working with simple actions, refer to the "Using events in design elements and controls" topic in the data access designer help.

Back to top

Conclusion

The sample application presented in this article is intended to get you started using data access designer. It walks you through the tasks involved in creating a functional application that contains all the available design elements and some logic added with simple actions. If you encounter issues with the sample application, please email the author.

Back to top

Download

Information about download methods

Resources

• Visit the developerWorks IBM Workplace resource center for more information about the IBM Workplace Client Technology and other Workplace products.
  
  
• Read the developerWorks article, "What is the IBM data acces tool?," to learn about the IBM data access tool.
  
  
• For more information about the IBM Workplace Client Technology, rich edition, visit the product page.
  
  
• Get involved in the developerWorks community by participating in developerWorks blogs.
  
  
• Browse for books on these and other technical topics.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• The database application
• Conclusion
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Lotus on developerWorks

Calling all developers

Download no charge Lotus Domino Designer

Special offers