Tutorial2: Building a Form with Database Connectivity

Documentation home

 

See also: All Tutorials, Working with Databases, Controls, Form Editor

 

Introduction

 

In this tutorial we will build a form with database integration, and assume that you have already completed the Building your first form tutorial and therefore have a basic familiarity with using the Verj.io Studio.

 

In this tutorial, 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 certificate, and the government will refund the cost of the course on presentation of the certificate 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 EBASE_SAMPLES provided with Verj.io.

 

If you need to create the EBASE_SAMPLES database, scripts can be found in folder Server/databaseSchemas in the server installation.

 

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!

  

Instructions

 

Check Studio Preferences

Open the Studio Preferences Dialog by selecting File > Preferences from the menu and click the Page Editor tab. At the bottom of the page, check Page Navigation Panel Control. This ensures that the Page Navigation Panel Control is added to all new pages. This can be reversed at the end of this exercise, if required.

 

Import the database tables into the Verj.io Studio:

  1. If the Tutorials project doesn’t already exist, create it by right clicking in the tree panel and selecting New > Project
  2. Create a new folder for the tutorial: right click on the Tutorials project and select New > Folder, name the folder Tutorial2
  3. Select Tools > Resource Wizards > Database Resource Wizard from the menu at the top of the studio window to start the database import wizard
  4. Click the … button to the right of Select Database Connection and select EBASE_SAMPLES from the list. (Database connections are created and maintained using the Server Administration Application)
  5. Click the … button to the right of Select the target location for the resource(s) and select the Tutorial2 folder in the Tutorials project. Then click Next
  6. If you are using Apache Derby (the default), Oracle or SQL Server for the database system, you will need to select the EBASE_SAMPLES schema from the next screen and click Next again.  Then select the tables APPLICANTS, COURSES and COURSE_PROVIDERS (hold down the Ctrl key for multiple selects) and click Next again
  7. You will be presented with a dialog box where you can further customize the import; click Finish to accept the defaults. The system will create a new Database Resource in the Tutorial2 folder for each of the three selected tables – the resource name in each case will be the table name, and the system will open the last Database Resource created (COURSE_PROVIDERS)

 

 

Create a Dynamic List of course providers:

  1. Open the COURSE_PROVIDERS Database Resource by double clicking on the name in the tree panel
  2. Click the Build Dynamic List icon  on the toolbar at the top of the panel and click Next to accept the default name COURSE_PROVIDERS_list
  3. On the next window - Create Where Clause, don't select anything, simply click Next
  4. On the final window - Select Fields From Database, select the COURSE_PROVIDER field and click Finish

 

Note: This list will display column COURSE_PROVIDER from table COURSE_PROVIDERS. (i.e. the equivalent SQL is select course_provider from ebase_samples.course_providers).

 

Create a second Dynamic 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_list).

 

  1. Open the COURSES Database Resource by double clicking on the name in the tree panel
  2. Click the Build Dynamic List  icon, accept the default name COURSES_list by clicking Next
  3. On the next window - Create Where Clause , select field COURSE_PROVIDER and click Next
  4. On the final window - Select Fields From Database , click Select All, then Finish

 

Create a new form and import the fields

  1. Right click on the Tutorial2 folder in the tree panel, enter New > Form and name the form Refund
  2. Add the Database Resources to the form: in the form editor select the Resources View (usually in the top right-hand corner), click the Add Resource icon  and select the APPLICANTS, COURSES and COURSE_PROVIDERS Database Resources
  3. Now select the Fields View, click the Import fields from external resource icon  on the toolbar, select APPLICANTS  then click Select All to select all fields then click OK. Repeat the process for resources 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
  4. Importing the fields maps them to the resource fields in the database resources, these automatically created mappings need to be adjusted:
  5. In the Resources View, select the COURSES Database Resource, then click the Show mappings for resource icon  on the toolbar
  6. Set the course_name and course_provider resource fields to be mapped to form field None (i.e. not mapped to any form fields). None is the first name in the dropdown
  7. Click OK

 

Hint: the fields are shown in the Fields View with the most recently added fields at the top. These can be shown in alphabetic order by clicking on the Sort fields icon . 

 

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

 

  1. In the Fields View, click the New field icon  and name the new field PrevApplication then press the Return key
  2. In the Properties View change the Field type to Boolean (General section) and the Display type (Presentation section) to Checkbox

 

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

 

Create three pages P_Applicant, P_Course and P_Bank

 

  1. In the Pages View (usually on the left-hand side), double click on Page_1 and change the page name to P_Applicant
  2. In Palette View, click Group Panel Control and drag and drop into Outline View beneath the Page Control P_Applicant
  3. Click the New Page icon  and add a new page named P_Course
  4. In Palette View, select Group Panel and drag and drop in Outline View beneath the Page Control P_Course
  5. Repeat the exercise adding a new page named P_Bank
  6. In Palette View, select Group Panel and drag and drop in Outline View beneath the Page Control P_Bank
  7. Now add the fields to this Group Panel control on each page as shown below. You can do this in one of three ways. In each case, select the target page by clicking on the appropriate page in the Pages View

 

Ø       By dragging the individual fields from the Fields View to the Outline View or WYSIWYG View (this is the central panel)

·         When dragging to the WYSIWYG View, drop the first field after the <no group info set> text – a solid black line indicates the drop position

·         When dragging to the Outline View, a solid black line indicates the drop position (under the target control icon means add after, to the right means add to)

 

Ø       By dragging multiple fields from the Fields View to the Outline View or WYSIWYG View:  hold down the Ctrl key to multi-select the fields, then release the Ctrl key and drag the selections. Drag and drop the Field Controls to rearrange them

 

Ø       Right click on the Group Panel Control in either the Outline View or the WYSIWYG View and select Add control to Group Panel then Existing fields. Hold down the Ctrl key, select the fields, then click Add. Drag and drop the Field Controls to rearrange them

 

Fields for page P_Applicant:

 

NI_NUMBER 

PrevApplication

APPLICANT

ADDRESS

POSTCODE

EMAIL

DOB

EMPLOYMENT_STATUS

 

Outline View should look like this:

 

 

Fields for page P_Course:

 

COURSE_PROVIDER

COURSE_NAME

COURSE_NUMBER

COURSE_DURATION

REFUND_VALUE

CERTIFICATE_ID

 

Outline View should look like this:

 

 

Fields for page P_Bank:

 

BANK_NAME

BANK_ACCOUNT_NAME

BANK_ACCOUNT_NO

BANK_SORT_CODE

 

Outline View should look like this:

 

 

  

  1. Ensure the controls on each page are in the order above. They can be re-arranged if necessary by drag and drop.

 

 

Create three scripts which we will use with this form.

 

Create the named scripts below by right clicking on the Tutorial2 folder and selecting New > Script (Javascript)  or New > Script (FPL) depending on your preference. Examples are shown below for Javascript and FPL (the Ebase built-in Form Processing Language). Then cut and paste the script code below; if using Javascript paste the code after the two importPackage statements at the top of the script; if using FPL, check the syntax by clicking the Verify  icon. Then save the script with the Save icon .

 

Script readApplicant:

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.

 

Javascript:

FPL:

if ( fields.PrevApplication.value )

{

  var rows = resources.APPLICANTS.fetch();

  if ( rows == 0 )

  {

    fields.PrevApplication.value = false;

    event.owner.addErrorMessage("Could not find a saved record for NI " +  fields.NI_NUMBER.value );

  }

  else

  {

    resources.COURSES.fetch();

  }

}

if [ PrevApplication = 'Y' ]

  fetch applicants;

  if [ $FOUND = 'N' ]

    PrevApplication = 'N';

    message 'Could not find a saved record for NI ' + ni_number;

  else

    fetch COURSES;

  endif

endif

 

 

 

Script updateApplicant:

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, and if it's a modification to an existing application, we use an update.

 

Javascript:

FPL:

if ( fields.PrevApplication.value )

{

  resources.APPLICANTS.update();

}

else

{

  resources.APPLICANTS.insert();

}

 

if [ PrevApplication = 'Y' ]

  update APPLICANTS;

else

  insert APPLICANTS;

endif

 

 

 

Script clearCourseDetails:

This script is used to clear previously selected course details.

 

Javascript:

FPL:

fields.COURSE_NUMBER.value = null;

fields.COURSE_DURATION.value = null;

fields.REFUND_VALUE.value = null;

course_number = null;

course_duration = null;

refund_value = null;

 

 

 

Customize the P_Applicant page

 

  1. Return to the form editor by clicking on the Refund form in the open elements toolbar at the top of the page
  2. In the Pages View, click the P_Applicant page
  3. Select the Field Controls for the fields below in the WYSIWYG View or Outline View > Group Panel, and set the following properties in the Properties View:

 

NI_NUMBER

Mandatory

PrevApplication

Immediate Validation

APPLICANT

Mandatory

ADDRESS

Mandatory

POSTCODE

Mandatory

DOB

Mandatory

EMPLOYMENT_STATUS

Mandatory

 

Hint: you can do this faster by multi-selecting the controls with the Ctrl key, then using the right click menu.

 

  1. Right click on the PrevApplication Field Control in the Outline View or WYSIWYG View, select Events, select the On Change tab, then click the Add Script(s) button and add script readApplicant
  2. Click the NI_NUMBER field in the Fields View, double click on the Validators property, then set the Pattern (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
  3. Click the ADDRESS field in the Fields View, change the Display type to Text Area

 

 

Customize the P_Course page

 

39.   In the Pages View, click the P_Course page

 

  1. Add a list to the COURSE_PROVIDER field:
    In the Fields View, click on the COURSE_PROVIDER field. In the Properties View, change the following properties:

 

  1. Now do the same for the COURSE_NAME field. Set the display type to Drop Down, choose dynamic list COURSES_list and select option When list is built: to Each display. Note that this list is more complex – it displays all course names for the selected course provider and also loads additional fields when the user makes a selection. This is equivalent to the following SQL:

 

select course_name, refund_value, course_number, course_duration from courses where course_provider = xxx

 

You can see the mappings between the form fields and the list by clicking the “…” button opposite the Dynamic list mappings property.

 

 

These mappings have been created automatically by the system - any form fields are mapped to corresponding like-named list fields when the list is first associated with the field.

 

  1. Set these attributes for the Field Controls 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 Verj.io server whenever the user changes the value of the field. For the COURSE_PROVIDER field, this will then result in the execution of script clearCourseDetails (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.)

 

  1. Right click on the COURSE_PROVIDER Field Control in the Outline or WYSIWYG View, select Events and add script clearCourseDetails to the On Change event

 

Customise the P_Bank page

 

  1. In the Pages View, click the P_Bank page
  2. Set all four Field Controls mandatory

 

 

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

 

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

 

Add texts

 

  1. Click Form properties icon  on the form toolbar again and on the Texts tab, set the following texts:

 

 

On the Final Page tab of Form Properties set:

 

 

 

  1. Select the P_Applicant page in the Pages View > Select Group Panel in the Outline View and double click on the following texts to change them:

 

From

To

<no group header set>

Mature student application for refund

<no group info set>

Page 1: Applicant

NI number

NI Number

PrevApplication

Retrieve previous application?

Dob

Date of birth

 

  1. Select the P_Course page in the Pages View, and double click on the following texts to change them:

 

From

To

<no group header set>

Mature student application for refund

<no group info set>

Page 2: The course you have completed

Course number

Course ID

Course duration

Course duration (weeks)

Refund value

Refund for this course (£)

Certificate ID

Your certificate ID

 

  1. Select the P_Bank page in the Pages View, and double click on the following texts to change them:

 

From

To

<no group header set>

Mature student application for refund

<no group info set>

Page 3: Bank details

 

 

That ends the design of the form pages. Click on each page in turn and check that the page looks OK. To see how the pages will appear in the browser, click on the Toggle studio view icon  on the WYSIWYG toolbar. This turns off all enhancements and assistants added by the Form Editor. Click this icon again to return to the assisted view. 

 

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

 

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

52.  What happens if you enter a new valid NI Number and check the Retrieve previous application checkbox ?

53.  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

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

55.  Note how the display only values for course number, course duration and refund value change with the course name selection (these are being populated by the COURSES_list Dynamic List)

56.  Click View > Execution log on the studio menu to see a log of form activity and scripts executed

57.  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

 

Change the error message displayed for 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.

 

  1. Click the Maintain texts and messages icon  on the form toolbar, then click the  icon and add a message text ‘Invalid National Insurance number. See field level help for correct syntax.’ Change the generated id to Msg1

 

  1. In the Outline View, select the NI Number field control, then check the Display help checkbox (Field Control section)

 

  1. In the Fields View click the NI_NUMBER field, then in the Properties View:

 

Format of a National Insurance number is:<br><br>

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

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

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

The final letter is optional

 

 

  1. 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 still have quite a lot to do, e.g. including a list for employment status, addition of many more explanatory texts and customization of the layout and styling etc.