Tables Tutorial

Introduction

In this exercise we will create a form containing a table. We assume that you have some familiarity with using the Ebase Designer. If not, you are recommended to start with the tutorial Building your First Form.

We will build a small application for maintaining a CD collection. It will support the following functions:

Adding a CD to the collection
Removing a CD from the collection
Sorting by artist, classification etc.

This application will make use of the following database tables which are included in the Ebase Samples Database:

mycds	this is the main CDs table
cd_classifications	main classifications e.g. Popular, Classical, Jazz etc
cd_sub_classifications	sub classifications within the main classification

Note: If you need to build these tables, please see script tables_exercise_schema.sql in the root ufs directory. This schema is for the MySQL database but can easily be adjusted for other DBMS's.

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

Instructions

Import the database tables

Open the EBASE_SAMPLES database element (or UFS database element in systems that have been upgraded from earlier Ebase versions) in the hierarchy tree panel (IT Elements -> Databases).
Click the Import from schema button.
Select the appropriate schema if you are using a database that supports schemas.
Select tables mycds, cd_classifications and cd_sub_classifications holding down the CTRL key and click OK.
In the pop-up window, click the Tables Only checkbox for the mycds table.
For the mycds table, click on CD_ID in the Columns for Where Clause column, then uncheck CD_ID in the pop-up window and click OK. (This removes the CD_ID column from the where clause for the database resource which means we will retrieve all CDs.)
Click OK to perform the import.

Build the classification dynamic list

This will be used to classify a CD.

Open the CD_CLASSIFICATIONS database resource in the hierarchy tree panel (IT Elements -> External Resources -> Database Resources).
Click the Build Dynamic List button.
Click OK to the warning message – where clause has no value.
Click Continue on the next pop-up window to accept the default name.
Click Continue on the next pop-up window (optional where clause field selection).
On the next pop-up window (select fields to be fetched from the database) select the CLASSIFICATION field and click Build list.
This will create a dynamic list called CD_CLASSIFICATIONS in the hierarchy tree panel (IT Elements -> Dynamic Lists).
Click Save.

Build the sub-classification dynamic list

This will be used to further classify a CD.

Open the CD_SUB_CLASSIFICATIONS database resource in the hierarchy tree panel (IT Elements -> External Resources -> Database Resources).
Click the Build Dynamic List button.
Click OK to the warning message – where clause has no value.
Click Continue on the next pop-up window to accept the default name.
On the next pop-up window (Optionally select fields to be added to the where clause) select the CLASSIFICATION field and click Continue.
On the next pop-up window (select fields to be fetched from the database) select the SUB_CLASSIFICATION field and click Build list.
This will create a dynamic list called CD_SUB_CLASSIFICATIONS in the hierarchy tree panel (IT Elements -> Dynamic Lists).
Click Save.

Create a business view

Select file menu (File -> New -> Business View) and name the view TABLES_TUTORIAL.
Add the MYCDS database resource to the view using the Add resource button.
Click Save.

Create the USER project

If the USER project does not already exist, create it by right-clicking on Business projects in the hierarchy tree and selecting Create new -> Business project.

Create the form and set up table display

Right-click on the USER project and create form CD_EXERCISE selecting Create new -> Form.
Click the Form properties icon on the form toolbar and set the business view to TABLES_TUTORIAL by selecting from the dropdown list. Click OK.
Click the Add Table icon on the page toolbar. Select MYCDS from the resource name dropdown list, enter table name CDS, then click the Import fields from resource button.
Click select all in the pop-up window, unselect the REVIEW field (we'll use this later) by holding down the CTRL key and clicking REVIEW. Then click OK, then OK again.

You should now see a display like this:

Right-click on the Description column header text, select Move to...,
In the pop-up window, select 2 from the dropdown list, click OK. The Description column has now disappeared and can be accessed by clicking the horizontal scroll icon .
Re-order the columns by dragging the column header text (e.g. 'Artist') to the order shown below (or any other order you fancy).
Change the column widths by dragging the separators between the column header texts to achieve the effect shown below:

Right-click the table header - <no table title set> - select Edit table properties.
Click options Table supports adding rows and Table supports deleting rows, set No. of visible rows to 10. Click OK.

The table should now look like this:

Add database integration

Add an FPL script to load the table from database:

Right-click the table header - <no table title set> -, select Edit actions, select the Before Table tab (it should already be selected) then click the Script builder wizard icon .
Select load a table from resource, click OK, click OK again.

Add an update table button and an FPL script to perform the updates:

Right-click the table header, select (Add -> Button) to add a button after the table.
Double-click the new button and enter text 'Update CD database'.
Right-click on the new button, select Edit actions, select the On Click tab (it should already be selected) then click the Script builder wizard icon .

Select update resource with table contents, click OK, click OK again.

Add a sequence to number the CDs as we enter them:

Open the sequences in the hierarchy tree (IT Elements -> Sequences).
Click the New sequence button, enter CDS under sequence name, click Save.

Add a script to use the sequence

Create a new script named ASSIGN_CD_ID (right-click on the USER project and select Create new -> Script).
In the script editor, enter:

sequence CDS;

set CDS-CD_ID = $NEXT_SEQUENCE_ID;

Click the Verify button, then the Save button.
Return to the form editor by clicking the CD_EXERCISE button in the open elements toolbar at the top of the page.

Right-click the table header - <no table title set> -, select Edit actions, select the Add Row tab then click the Add Scripts button.

Select ASSIGN_CD_ID from the dropdown. Click OK twice.

Now test the form for the first time by clicking on the Run icon in the form toolbar.

Initially the table is empty, so click the Add Row button, and a row appears with CD_ID 1. However, the table is much too wide because the field lengths for the individual columns are too large and exceed the percentage width specifications we have assigned.

To correct this, return to the Ebase Designer, right-click on the column header texts, select Edit column properties, select the Presentation tab and click on use value under Display Length and change the display length value to:

Column	Display length
ARTIST	25
TITLE	40
COMPOSER	15
CLASSIFICATION	10
SUB_CLASSIFICATION	10
RATING	1

Also, set the CD_ID column read only by right-clicking the CD_ID column header text and clicking Display Only.

Now try running the form again. You may need to further adjust these lengths depending on the width of your screen.

You will notice that CD ID 2 is assigned. Don't worry about this as we can reset the sequences once we have the form working.

Set up the classification lists

Right-click the CLASSIFICATION column text header, select Edit column properties.
Click the Lists tab, click Dynamic list, then select CD_CLASSIFICATIONS from the dropdown list.
Click the Presentation tab and select Drop Down under Display type. Click OK.
Right-click the CLASSIFICATION column text header again, select Immediate Validation. This will cause the sub-classification list to display only those sub-classifications within the selected classification.
Right-click the SUB_CLASSIFICATION column text header, select Edit column properties.
Click the Lists tab, click Dynamic list, select CD_SUB_CLASSIFICATIONS from the dropdown list and select option Dynamic list is built: Each time displayed.
Click on the Mappings Button and map list field CLASSIFICATION to form field CDS-CLASSIFICATION by selecting it from the dropdown. Click OK.
Click the Presentation tab and select Drop Down under Display type. Click OK.

Run the form again

Click the Add Row button a few times and add some test data. Observe the behaviour of the classification lists.
When you are ready, click the Update CD Database button.
Now re-run the form and the entered CDs should appear.

Final touches

Set some of the columns as mandatory by holding down the CTRL key then clicking on the column header texts for columns ARTIST, TITLE and CLASSIFICATION. Then right-click and select Set Mandatory.
Enable sorting on CD_ID, ARTIST, COMPOSER and CLASSIFICATION. Right-click the column header for each of these fields and select Set Sortable. Sort icons are displayed on the right-hand side of the column header text. When we run the form, clicking these icons will sort the table by the appropriate column. Repeating the sort then acts as a toggle to sort in ascending or descending order and the sort icon changes correspondingly.
Sort the initial display by cd id in the database resource (IT Elements -> External Resources -> Database Resources à MYCDS), then add 'order by CD_ID' to the Additional SQL statements box. (We could also achieve the same result using the FPL sort command). Click Save.
Return to the form editor by clicking the CD_EXERCISE button in the open elements toolbar at the top of the page.
Right-click the column header for CD_ID and select Fixed. This means that the cd id is displayed as the left-most column as the user scrolls horizontally.
Add some header texts: double click the table title text - <no table title set> - and enter 'My CD collection'. If you like, enter some instructions in the table info text by double clicking on this. You can also change the column header texts by double clicking on these.
Add validation to the rating column to ensure that any entered number is between 1 and 5. Right-click the RATING column text header, select Column Properties and under the Validation section of the General tab, enter '1' in Inclusive Min. value and '5' in Inclusive Max. value.
Re-run the form and check everything performs as expected.
The form is now ready, so delete all the test data, and reset the CD sequence back to 1 using the sequences editor.

Optional Further Enhancements

Adding a reviews button column

We'd like to be able to add a button on each row which, when clicked, takes us to another page which allows the user to enter a review of the CD. This could be done as follows:

Add the CDS-REVIEW column to the table

Right-click on any existing column header text (it doesn’t matter which column), select Add columns, then click Import fields from resource and select the REVIEW column. Click OK and then OK again. It will be added as CDS-REVIEW.
Right-click on the column header and select Edit column properties. On the Presentation tab, change the display type to be Text area, set the Display length to 60 (click the use value radio button) and Number of rows to 15 and click OK.
Right-click on the column header and select Hidden.

Add the review page

Add a new page after PAGE_1 by right-clicking PAGE_1 and selecting Add page. Name the new page REVIEW_PAGE. Right-click on the new page, select Page properties, select the Nav. buttons tab and change the Prev page text to "Return to main table". Click OK to create the page.
Drag the following fields from the fields panel onto the new page: CDS-CDID, CDS-ARTIST, CDS-TITLE and CDS-REVIEW. Add more column fields if you like.
Set all fields except CDS_REVIEW display only: select the fields holding down the CTRL key, right-click and select Control properties -> Display Only.

Add the review button column to the table

Return to PAGE_1.
Right-click on the CDS-RATING column and select Add button column.
Set the column's title text by double clicking on the column header for the button. Enter "Review" in the text edit box and click OK.
Set the button text by right-clicking on the column header and selecting Set button text. Enter "edit..." in the text edit box and click OK.

Add a script to the Review button to send the user to the REVIEW_PAGE:

Create a new script GOTO_REVIEW_PAGE (right-click on the USER project and select Create new -> Script)
In the script editor, enter text:

goto page REVIEW_PAGE;

Click the Verify button, then the Save button.
Return to the form editor by clicking the CD_EXERCISE button in the open elements toolbar at the top of the page.

· Right-click the column header for the Review button, select Edit Actions, select the On Click tab if not already selected then click the Add Scripts button and add the GOTO_REVIEW_PAGE script. Click OK twice.

Disable the "next page" button on PAGE_1. Right-click on PAGE_1, select Page properties and set Next page to None. This means that the only route to the review page will be by clicking the Edit... button in the review column.
Re-run the form and check everything performs as expected. Enter some reviews and verify that they are recorded against the correct CD.

Making the table searchable

Add the input search field

Add a new field called SEARCH_FIELD_INPUT at the top of PAGE_1. Click on the Fields wizard icon on the tool bar and click the Create new field button. Enter SEARCH_FIELD_INPUT as the field name, click OK and then OK again.
Change the new field's label text by double clicking on the label Search field input and entering the text "Search artists" into the text edit dialog and click OK.
Add a button on the same line as the SEARCH_FIELD_INPUT field by right-clicking over the SEARCH_FIELD_INPUT field's entry field and selecting Add... -> Button. Set the button's text to "Go" by double clicking on the button and click OK.

Add the DB Search field

In the fields panel, click the add field icon which adds a new, unnamed field at the top of the fields list. Set the name of the new field to SEARCH_FIELD_FOR_DB. Note: This field will only be used for script processing and will not be placed on the page. Right-click on SEARCH_FIELD_FOR_DB and select Properties. In the properties dialog, set the field's default value to be "%". Click OK.

Add the script for triggering the search

Create a new script named APPLY_SEARCH_CRITERIA by right-clicking on the USER project and select Create new -> Script
In the script editor, enter text:

if [ SEARCH_FIELD_INPUT != '' ]

set SEARCH_FIELD_FOR_DB = '%'+SEARCH_FIELD_INPUT+'%';

else

set SEARCH_FIELD_FOR_DB = '%';

endif

fetchtable CDS;

Click the Verify button, then the Save button.
The purpose of this script is to build a string to be used to search the artist name column in the database.
Return to the form editor by clicking the CD_EXERCISE button in the open elements toolbar at the top of the page.

o Right-click the Go button and select Edit Actions, select the On Click tab if not already selected, then click the Add Scripts button and add the APPLY_SEARCH_CRITERIA script. Click OK twice.

Edit database resource MYCDS

Open the MYCDS database resource by navigating using the hierarchy tree (IT Elements -> External Resources -> Database Resources -> MYCDS).
Add a new resource field by clicking the icon. Name it SEARCH_CRITERIA and uncheck its Persistent checkbox to indicate that the column does not exist in the database and will only be used in the resource's where clause. This field will be mapped to the SEARCH_FIELD_FOR_DB form field (see below) so that we can restrict the rows returned by fetching this resource to just those which match the search criteria entered by the user.
Add "ARTIST LIKE &&SEARCH_CRITERIA" to the where clause.
Click Save.
Return to the form editor by clicking the CD_EXERCISE button in the open elements toolbar at the top of the page.

Open the resource mappings dialog by clicking the icon in the main tool bar. Map the form field SEARCH_FIELD_FOR_DB to the resource field SEARCH_CRITERIA. Click OK.
Re-run the form and check everything performs as expected.

Enter a search string which you know appears as a substring of at least one of your artist's names. Click Go and check that you only get the artist names that contain the string you entered.
Clear the search string field, click Go and check that you get ALL your data.