Building a form with database connectivity

Building a Form with Database Connectivity

Introduction

In this exercise we will build a more advanced form and assume that you have already built the form described in Building your first form and that you therefore have some familiarity with using the Ebase Designer. In this form, we are assuming that the government has introduced an education program for mature students. A certain number of approved courses are offered by approved course providers. At the end of each course, the student must sit a final test. If successful, they are issued with a certification id, and the government will refund the cost of the course on presentation of the certification id. This form is an online application allowing successful students to apply for the refund. The form will consist of three pages: applicant details, course details, applicant bank details (for the payment).

We will save all applications in the database (table applicants) and we will allow changing of existing applications as well as insertion of new applications. The key for the applicants table is the national insurance number. We will also be using two additional tables for dropdown lists: course_providers and courses. These three tables already exist in the database schema provided with Ebase and some test data has been loaded for this exercise. If you would like to perform this exercise connecting to another database, please see Appendix A: Using your own database 13.

We are going to import the fields used in this form from the database schema. This is the simplest way to create a form with database integration as it ensures that the correct field type and length specifications are created, creates all the mappings between the database resources and the form fields, and it involves the least typing!

These instructions contain a lot of detail so you may find it easier to print this document.

Instructions

Import the database tables into the Ebase Designer:

Open the EBASE_SAMPLES database editor (or UFS database definition in some systems that have been upgraded from earlier Ebase versions) by using the hierarchy tree (IT Elements -> Databases) and double clicking on entry EBASE_SAMPLES.
Click the Import from schema button. If you are using Derby, Oracle or SQL Server for the database system, you will need to select the appropriate schema from the next screen (this is 'UFS' in the supplied system). Then select tables applicants, course_providers and courses (hold down the CTRL key for multiple selects).
Click OK. You will be presented with a dialog box where you can further customize the import; click OK to accept the defaults. The system will create a new database resource for each table with the name of the table. If you want to, you can look at these using the hierarchy tree (IT Elements -> External Resources -> Database Resources)

Create a new business view named REFUND:

Create the business view with (File -> New -> Business View), and name it REFUND
In the business view editor, click the Add Resource button, and select the three database resources (hold down the CTRL key): APPLICANTS, COURSE_PROVIDERS and COURSES. Click OK.
Click the Save button

Create a dynamic list of course providers:

· Open the COURSE_PROVIDERS database resource by selecting (IT Elements -> External Resources -> Database Resources) from the hierarchy tree then double clicking on the name

· Click the Build Dynamic List button and accept the default name COURSE_PROVIDERS (a warning dialog box will appear - in this case it isn't a problem, so click 'OK')

· On the first pop-up window, don't select anything, simply click Continue

· On the second pop-up window, select the COURSE_PROVIDER field and click Build list

Note: This list will display column course_provider from table course_providers. (i.e. the equivalent SQL is select course_provider from course_providers). In certain databases, the table name will appear as UFS.COURSE_PROVIDERS.

Create a second list of course names:

This will use column course_name from table courses. This list is a bit more complicated than the last one: firstly, we want to load more columns from the database table when the user makes a selection, and secondly, we only want the user to see the selection of courses provided by the particular course provider they have selected using the previous list (course providers).

· Open the COURSES database resource by selecting (IT Elements -> External Resources -> Database Resources) from the hierarchy tree then double clicking on the name

· Click the Build Dynamic List button, click 'OK' when the warning dialog appears, and accept the default name COURSES

· On the first pop-up window, select field COURSE_PROVIDER and click Continue

· On the second pop-up window, click Select All, then Build list

Create a new form REFUND and import the fields

Right click on the USER project in Business Projects in the hierarchy tree and create a new form REFUND
Associate the form with the business view by clicking Form Properties on the form toolbar and selecting REFUND from the business view dropdown. Click OK.
Click on the Import fields from external resource icon on the right-hand side of the fields toolbar (note: if this is not visible, click on the vertical Show Fields text on the far right-hand side of the Designer). From the dropdown at the top of the dialog box, select APPLICANTS, then click Select All to select all fields then click OK. Repeat the process for COURSES and COURSE_PROVIDERS. Click Yes to all to the message about merging fields - this appears because the same column name appears in some of the tables.

Note: We have used the form fields toolbar in this example to import fields from database resources without putting the fields onto a page. Alternatively, we could have used the Fields wizard to do the same thing but that would have placed all fields on the same page, which is not what we want in this case.

You have now successfully created the form fields and we will now start to customize the form display.

Create an additional field to ask the user if they want to modify a previous application:

Click the Add a field icon on the form fields toolbar and name the new field PREV_APPLICATION. Change the display type to Checkbox and the format to Boolean (you can do this either in the fields panel or by editing the field properties).

Create three pages APPLICANT, COURSE and BANK

Change the name of PAGE_1 to APPLICANT by right clicking the PAGE_1 button, selecting Page Properties then changing the page name.
Right click on the APPLICANT page button and add a new page COURSE
Then right click on the COURSE page and add a new page BANK

Now move the fields onto the page as laid out below. You can do this in one of three ways. In each case, select the target page by clicking the appropriate page button.

By individually dragging the fields from the fields (right) panel to the page (left) panel. Drag one field at a time by clicking on the field name in the fields panel and dropping it on the label text of the field in the page panel after which it should appear. The first field on each page should be dropped on the page info text or page header text.
By selecting all fields to be moved from the fields panel by holding down the CTRL key and then clicking the Insert selected fields from form fields panel icon on the page toolbar.
By using the fields wizard on the page toolbar.

Fields for page APPLICANT:

NI_NUMBER

PREV_APPLICATION

APPLICANT

ADDRESS

POSTCODE

DOB

EMPLOYMENT_STATUS

Fields for page COURSE:

COURSE_PROVIDER

COURSE_NAME

COURSE_NUMBER

COURSE_DURATION

REFUND_VALUE

CERTIFICATE_ID

Fields for page BANK:

BANK_NAME

BANK_ACCOUNT_NAME

BANK_ACCOUNT_NO

BANK_SORT_CODE

Ensure the fields on each page are in the order above. They can be re-arranged if necessary by using drag and drop. (Drag a field by clicking its label text and drop it on the label text of the field after which it should appear.)

Create three scripts which we will use with this form.

Create the named scripts below by right clicking on the USER project. Then cut and paste the commands below, check the syntax by clicking the Verify button, and save the script with the Save button.

Script READ_APPLICANT. This script will be used to read in an existing application from the applicants table if requested to by the user. If the record cannot be found, we will issue an error message.

if [ PREV_APPLICATION = 'Y' ]

fetch APPLICANTS;

if [ $FOUND = 'N' ]

set PREV_APPLICATION = 'N';

message E, 1001, NI_NUMBER;

else

unset COURSE_NAME on COURSE displayonly;

fetch COURSES;

endif

Script UPDATE_APPLICANT. This script will be used to save the application to the database at the end of form processing. If it's a new application, we use an insert command, and if it's a modification to an existing application, we use an update command.

if [PREV_APPLICATION = 'Y' ]

update APPLICANTS;

else

insert APPLICANTS;

endif

Script SHOW_COURSE_NAME. This script is used to allow the user to select from the course names list. Because we have initially set the COURSE_NAME field on page COURSE to be display only, we need to make this field enterable once a selection has been made from the COURSE_PROVIDERS list

unset COURSE_NAME displayonly;

set course_number = null;

set course_duration = null;

set refund_value = null;

Customize the APPLICANT page

· Return to the form editor by double clicking the REFUND form in the hierarchy tree panel, or clicking on the REFUND form in the open elements toolbar at the top of the page

· Click the APPLICANT page button

Right click the fields below, select Control Properties to set the following attributes:

NI_NUMBER	Mandatory
PREV_APPLICATION	Immediate Validation
APPLICANT	Mandatory
ADDRESS	Mandatory
POSTCODE	Mandatory
DOB	Mandatory
EMPLOYMENT_STATUS	Mandatory

Hint: you can apply the same attribute to multiple fields by holding down the Ctrl key, then selecting the fields.

Right click on the PREV_APPLICATION field, select Edit actions, and add script READ_APPLICANT to the On Change scripts (click Add Script(s) button and select the READ_APPLICANT script from the resulting dialog box. Click OK twice.)
Right click the NI_NUMBER field, select Edit field properties and on the General tab, set the Patten (XML Regex) to [A-CEGHJ-PR-TW-Z]{1}[A-CEGHJ-NPR-TW-Z]{1}[0-9]{6}[A-DFM]{0,1} This is an XML regular expression which ensures that NI number is entered correctly. Click OK.
Right click on ADDRESS field, select (Display properties -> Display Type, change the display type to Text Area).

Customize the COURSE page

· Click the COURSE page button

Add a list to the COURSE_PROVIDER field:
Right click on COURSE_PROVIDER field, select Edit field properties. On the Presentation tab, change the display type to Drop Down; then on the Lists tab select Dynamic List, choose list COURSE_PROVIDERS and select option Dynamic list is built: Each time displayed. You will notice that there is a Mappings button. We do not need to use this for this list as only one field is involved and the system will create the mapping automatically. Click OK.

Now do the same for the COURSE_NAME field. Set the display type to Drop Down, choose dynamic list COURSES and select option Dynamic list is built: Each time displayed. This list is more complex but the mappings have still been created automatically by the system (press the Mappings button to see this). The system will automatically map all like-named fields in the form to the dynamic list. Click OK.

Set these attributes to the fields on this page:

COURSE_PROVIDER	Mandatory, Immediate Validation
COURSE_NAME	Mandatory, Immediate Validation
COURSE_NUMBER	Display Only
COURSE_DURATION	Display Only
REFUND_VALUE	Display Only
CERTIFICATE_ID	Mandatory

(Setting Immediate Validation returns control to the Ebase server whenever the field is changed. For the COURSE_PROVIDER field, this will then result in the execution of script SHOW_COURSE_NAME (see next action point) and will build the course names list which is dependent on the value of the COURSE_PROVIDER field. For the COURSE_NAME field, setting Immediate Validation means we can immediately see the additional values loaded from the list - course number, course duration and the refund value for this course.)

Right click on the COURSE_PROVIDER field, select Edit actions and add script SHOW_COURSE_NAME to the On Change event.

Customise the BANK page

Click the BANK page button
Set all four fields mandatory

Set the database update script to run at the end of form processing

Click Form properties on the form toolbar, and on the Events tab add script UPDATE_APPLICANT to the After Form event (click on the After Form tab).

Add texts

Click the Maintain texts icon on the form toolbar, and set the texts as shown below

On the Form tab:

On the APPLICANT page tab:

On the COURSE page tab:

On the BANK page tab:

On the Final Page tab:

Add the error message to display when we can't find a previous application

In the tree hierarchy panel, double click on Messages under project USER. The message editor will open.
Click the Add a new message icon on the toolbar.
Double click on the message text and enter Could not find a saved record for NI &&, then click Save. (The && in the message will be substituted by the NI number that the user has entered)
Note down the number of the created message (probably 1001)
Re-open script READ_APPLICANT either via the tree hierarchy panel (project USER -> Scripts) or via the open elements toolbar at the top of the page, and change the error message number in the message command on line 5 to the number just created
Save the script

And finally we can test the form: return to the form editor and click the Run icon

· Try entering an invalid NI Number in the NI Number field and click the Next button (examples of valid NI numbers: AB123456C, JG103759A, WL457123)

· What happens if you enter a new valid NI Number and check the retrieve previous application checkbox ?

· Run the form, entering all the details and submit the application. Then run the form again, entering the same NI Number in the first field and clicking the 'Retrieve previous application?' box. The details you entered initially should be fetched from the database.

· Note how the contents of the course names list changes as you change course provider

· Note how the display only values for course number, course duration and refund value change with the course name selection

· If you have made a mistake following the instructions, you may receive an error message at runtime. Hopefully the message will be specific enough to enable you to resolve the error. If you get error message:Naming exception error retrieving datasource jdbc/xxxxx, check that the dynamic lists have EBASE_SAMPLES specified as the database (or UFS database definition in some systems that have been upgraded from earlier Ebase versions)..

Change the error message to display when we enter an invalid National Insurance number

When we enter an invalid National Insurance number, we get the error message: Field does not match xml regex pattern [A-CEGHJ-PR-TW-Z]{1}[A-CEGHJ-NPR-TW-Z]{1}[0-9]{6}[A-DFM]{0,1} This is clearly not a meaningful message to most people. So let’s be more helpful.
Right click the NI_NUMBER field, select Edit field properties and on the Texts tab, and set the Information Text to:

Format of a National Insurance number is: 

AANNNNNNA - in other words: 2 letters, 6 numbers, one letter 

The 1st letter may not be D, F, I, Q, U or Z

The 2nd letter may not be D, F, I, O, Q, U or Z 

The final letter is optional

In the tree hierarchy panel, double click on Messages under project USER. The message editor will open.
Click the Add a new message icon on the toolbar.
Double click on the message text and enter Invalid National Insurance number. See field level help for full explanation of valid values.
Note down the number of the created message (probably 1002)
Re-open the REFUND form, Right click the NI_NUMBER field, select Edit field properties and on the General tab, highlight Patten (XML Regex), then click edit selected validator icon
Click the more button, then in the Server validation section, click the use message id radio button and enter the message number just created
Run the form again. Note the help icon to the right of the Ni number field. Click on this and see what happens. Enter an invalid Ni number, click the Next button and make sure that you see the message you entered in this section

In this exercise we have set up the basic form for this application. In real life, we would continue to make further improvements, e.g. including a static list for employment status, addition of many more explanatory texts and customization of the look and feel.

Appendix A: Using your own database

First connect the database to Ebase. (See Adding a Database Connection for more information)

· Then create the three tables applicants, course_providers and courses. A sample SQL file can be found in ...\Ufs\db_exercise_schema.sql but you will probably need to make small changes for your database system's syntax.

· Load the sample data. A sample SQL file can be found in ...\Ufs\db_exercise_data.sql or you can create some test data manually

· Then follow the instructions above using your newly created database definition instead of the Ebase database.

Note that we could have used Ebase to create the database tables for us – this functionality is available using Create Database Table from within a database resource. But this would have made this exercise considerably longer.

Introduction

Instructions

The 1st letter may not be D, F, I, Q, U or Z<br>

Appendix A: Using your own database