Tutorial3: Introduction to using Tables

Documentation home

Introduction. 1

Instructions 1

See also: All Tutorials, Tables, Controls, Form Editor

Introduction

In this tutorial we will create a form containing a table. We assume that you have some familiarity with using the Verj.io Studio. 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.
• Searching

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:

These three tables already exist in the database EBASE_SAMPLES provided with Verj.io.

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

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 control is added to all new pages. This can be reversed at the end of this exercise, if required.

Import the database tables

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 Tutorial3.
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 Tutorial3 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.
7. Then select the tables CD_CLASSIFICATIONS, CD_SUB_CLASSIFICATIONS and MYCDS (hold down the Ctrl key for multiple selects) and click Next again.
8. In the next panel click the Tables Only checkbox for the MYCDS table and 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.)
9. Click Finish to create the resources. The system will create the three Database Resources and open the last one (MYCDS).
10. Open the CD_CLASSIFICATIONS Database Resource by double clicking on the name in the tree panel.

Build the classification dynamic list

This list will be used to classify a CD.

11. Click the Build Dynamic List icon on the toolbar at the top of the panel and click Next to accept the default name CD_CLASSIFICATIONS_list
12. On the next window - Create Where Clause, don't select anything, simply click Next
13. On the final window - Select Fields From Database, select the CLASSIFICATION field and click Finish

Build the sub-classification dynamic list

This list will be used to further classify a CD.

14.  Open the CD_SUB_CLASSIFICATIONS Database Resource by double clicking on the name in the tree panel
15. Click the Build dynamic list icon, click 'OK' when the warning message appears, and accept the default name CD_SUB_CLASSIFICATIONS_list by clicking Next
16. On the next window - Create Where Clause , select field CLASSIFICATION and click Next
17. On the final window - Select Fields From Database , select the SUB_CLASSIFICATION field, then Finish

Create the form and set up table display

18. Right click on the Tutorial3 folder in the tree panel, enter New > Form and name the form Cd_Exercise
19. Click the form properties icon on the form toolbar at the top of the page, select the Presentation tab, then click the … button to the right of the Presentation Template property and select the xiDefault template from the ebaseSamples project
20. Add the MYCDS Database Resource 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 MYCDS Database Resource
21. In the Tables View, click the New Table icon, select MYCDS from the resource name dropdown list, enter table name cds (over-typing the generated name of MYCDS), then click the Import columns from resource button.
22. Click Select All in the pop-up window. Then click OK twice.
23. Drag the cds table from the Tables View onto the page (drag the cds name next to the table icon ), adding it to the Page.

In the Outline View, you can see that this has created a Table Control, a Table Page Control and a number of Table Column Controls. The Table Control represents a visible table, a Table Page Control represents a horizontal scroll page within this table (in most cases there is only one of these), and the Table Column Controls represent columns.

24. Delete the cds-REVIEW and cds-DESCRIPTION column controls – by selecting them in the Outline View and press the Delete key
25. Re-order the columns in the Outline View by dragging them to the order shown below (or any other order you prefer).
26. Select the Table Control - click on the <no table header set> text then change the following properties in the Properties View:
27. Select Show Add Row button
28. Select Show delete column
29. set No. visible rows to 20 (this change is not visible in the studio – it always shows 3 rows)

Hint: initially, these properties are taken from default values configured in the Presentation Template associated with the form.

30. Click on the Toggle studio view icon on the WYSIWYG View toolbar – this shows the page as it will actually appear in the browser. Before this, the page display was operating in assisted mode where the system makes some small changes to make the design process easier. We can see that the table is too wide and overflows the page – this is because the lengths for the individual columns are too large and exceed the percentage width specifications we have assigned. Maximise the WYSIWYG View by double clicking on the Title Bar – this contains text Page Page_1 on a light blue background. There still isn’t enough space to display the table. Double click the WYSIWYG Title Bar again to return to normal.
31. To correct this problem, select the table columns in the Tables View one by one, and change the Display Length property in the Properties View (Presentation section) as follows:

Column	Display Length
cds-ARTIST	25
cds-CLASSIFICATION	10
cds-COMPOSER	15
cds-RATING	1
cds-SUB_CLASSIFICATION	10
cds-TITLE	40

32. Set the CD_ID column display only by right-clicking the CD_ID column in the WYSIWYG View and selecting Display Only.
33. Click on the Toggle designer View icon on the WYSIWYG View toolbar again to return to the assisted display.

Add database integration

Add a script to load the table from database:

34. Click the table header text <no table header set> to select the Table Control, then right click and select Events, select the Before Table tab (it should already be selected) then click the Script builder wizard icon .
35. Set Target Folder to the Tutorial3 folder in the Tutorials project (this should be the default)
36. Set Language to Javascript
37. Select load a table from resource, click OK.

This will create a script to load the table. Click on the Cd_Exercise form in the open items toolbar at the top of the window to return to the form editor. 

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

38. Drag a Button Control from the Palette (section General) and add it after the table
39. Double click on the button in the WYSIWYG View and change the text to Update CD database.
40. Right click on the button, select Events, then click the Script builder wizard icon .
41. Set Target Folder to the Tutorial3 folder in the Tutorials project (this should be the default)
42. Set Language to Javascript
43. Select update resource with table contents, click OK.

This will create a script to update the table. Click on the Cd_Exercise form in the open items toolbar at the top of the window to return to the form editor. 

Add a script to assign each new CD a sequence number

44. Select the Table Control, then right click, select Events and click the Add Row tab. Then click the Create new script icon , enter script name addCd, set the Location to the Tutorial3 folder in the Tutorials project and Language to Javascript. This will create a new script named addCd and open the script editor. Cut and paste the code below into the script window after the existing importPackage statements.

tables.cds.CD_ID.value = system.sequenceManager.sequence("CDS");

When this script is executed for the first time, the system will create a sequence and start numbering from 1.

Set up the classification lists

45. Classification – this is a simple list that shows a list of main classifications
46. Sub-classification – this list is more complicated: it shows a list of sub-classifications within the main classification. The list is re-built each time the main classification list is changed.

In the Tables View select the cds-CLASSIFICATION column and change the following properties in the Properties View

• Display type (Presentation section) to Drop Down
• Dynamic List (Lists section) to CD_CLASSIFICATIONS_list

47. Click the cds-CLASSIFICATION column in the WYSIWYG View and check the Immediate Validation property (Table Column Control section). This means that the server is contacted each time this list is changed, and will result in the rebuilding of the sub-classification list.

Test the form

48. Click the run form icon on the form toolbar.
49. Click the Add Row button a few times and add some test data. Observe the behaviour of the classification lists.
50. When you are ready, click the Update CD Database button.
51. Now re-run the form and the entered CDs should be displayed.

Final touches

52.  Set some of the columns as mandatory by holding down the CTRL key then clicking on the column header texts for columns cds-ARTIST, cds-TITLE and cds-CLASSIFICATION. Then right-click and select Set Mandatory.
53. Enable sorting on cds-CD_ID, cds-ARTIST, cds-COMPOSER and cds-CLASSIFICATION. Click the column header for each of these fields and set the Sortable property. 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.
54. Sort the initial display by cd id in the database resource. Open the MYCDS Database Resource by double clicking on the name in the tree panel, then add order by CD_ID to the Additional SQL statements box. (We could also achieve the same result by sorting the table using a script). Enter Ctrl S to save the Database Resource or click the icon on the toolbar.
55. Return to the form editor by clicking the Cd_Exercise form in the open elements toolbar at the top of the page.
56. Add some header texts: double click the table header text - <no table header 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.
57. Add validation to the rating column to ensure that any entered number is between 1 and 5. Select the cds-RATING column in the Tables View, double click opposite the Validators property, enter '1' in Inclusive Min. value and '5' in Inclusive Max. value.
58. Delete the Page Navigation Panel Control at the bottom of the page
59. Re-run the form and check this new functionality performs as expected. Try selecting the cds Table Control and setting the No. visible rows property lower than the number of CDs in the table. You will then see the default scrolling header panel displayed when you run the form.

Optional Further Enhancements

Adding a reviews button column

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

1. Select the cds-REVIEW column in the Tables View and change the following properties:

· Display type property to Text Area,

· Display length to 60

· Number of rows to 15.

2. Add the review page

• Add a new page after Page_1 by clicking the New page icon in the Pages View, name the page ReviewPage.
• Double click the previous page button – shown as << - at the bottom of the new page and change the text to ‘Return to main table’.
• Drag the following fields from the Tables View onto the new page: cds-CDID, cds-ARTIST, cds-TITLE and cds-REVIEW. Add more column fields if you like.
• Set all fields in the WYSIWYG View except cds-REVIEW display only: select the Field Controls in the WYSIWYG View holding down the Ctrl key, then right-click and select Set Display Only.

3. Add the review button column to the table

• Select Page_1 in the Pages View.
• Drag a Button Column Control from the Palette (Table section) and add it after the cds-RATING column.
• Click on the new column header and enter ‘edit..’ as the button text in the Properties View.

4. Add a script to the new button to send the user to page ReviewPage:

• Create a new script gotoReviewPage (right-click on the Tutorial3 folder and select New -> Script (Javascript) )
• In the script editor enter:

form.gotoPage(pages.ReviewPage);

· Save the script.

5. Return to the form editor by clicking the Cd_Exercise form in the open elements toolbar at the top of the page.
6. Right-click on the new button column, select Events, then click the Add Scripts button and add the gotoReviewPage script.
7. 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

1. Add the input search field

• Click the New field icon on the Fields View toolbar and create a new field named searchFieldInput. Drag it to the top of Page_1.
• Double click on the field’s label text and change it to ‘Search artists’.
• Drag a Button Control after the new field
• Double click on the button and change its text to ‘Search’.

2. Add the DB Search fields

• Click the New field icon on the Fields View toolbar and create a new field named searchFieldForDb. Change property default value to be ‘%’.
• Click the New field icon on the Fields View toolbar and create a new field named whereClause. Change property default value to be ‘artist like &&searchFieldForDb’.

3. Add the script for triggering the search

• Create a new script applySearchCriteria (right-click on the Tutorial3 folder and select New -> Script (Javascript) )
• In the script editor, enter:

fields.searchFieldForDb.value = "%";

if ( fields.searchFieldInput.value )

{

fields.searchFieldForDb.value += fields.searchFieldInput.value + "%";

}

tables.cds.fetchTable();

· Save the script.

· Return to the form editor by clicking the Cd_Exercise form in the open elements toolbar at the top of the page.

· Right-click on the Search button, select Events, then click the Add Scripts button and add the applySearchCriteria script.

4. Edit Database Resource MYCDS

• Open the MYCDS Database Resource by double clicking on the name in the tree
• Add a new resource field by clicking the icon in the Resource fields panel. Name it searchFieldForDb 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 searchFieldForDb 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 a new resource field by clicking the icon. Name it whereClause and uncheck its Persistent checkbox. Check the Dynamic SQL checkbox.
• Add ‘&&whereClause’ to the where clause section of the resource.
• Enter Ctrl S to save the Database Resource.
• Return to the form editor by clicking the Cd_Exercise form in the open elements toolbar at the top of the page.

5. In the Resources View, select the MYCDS Database Resource, then click the icon on the toolbar to display the mappings.

· Map the resource field searchFieldForDb to form field searchFieldForDb.

· Map the resource field whereClause to form field whereClause.

· Click OK.

6. 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 Search 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.

So far we have introduced searching the artist column. We could search on all columns by extending the process above.

For more information on database resources and Dynamic SQL see the Database Resources documentation.