Tutorial4: Display and Update of Table with Foreign Keys

 

Documentation home

 

 

Introduction

 

Example 1 – Fetching data with foreign key

Fetch Users data into a form

Create a Dynamic List for departments

 

Example 2 - Fetching hierarchical/tree data

          Show the table column section ids (hidden)

          Create a Dynamic List for sections

         

 

See also: Tables, Dynamic Lists, Tutorials index

 

The aim of this tutorial is to show you how to fetch and update relational data from a database without having to write complex SQL to join database tables.

 

Introduction

Relational databases use the concept of primary keys to uniquely identify a row of data, these keys can be used as foreign keys in other database tables to create a relationship:

 


 

In the above example we have a list of departments identified by a unique number (id) in the DEPARTMENTS table. The USERS table has a DEPARTMENT_ID column which contains a number that specifies a department using the unique id of a department in the DEPARTMENTS table. The DEPARTMENT_ID column is a foreign key.

 

This way of organising data has many benefits but adds complexity when displaying the data within an application. If you want to display a list of users from the above example with their associated department description, you would have to write a SQL statement that joins the two tables using the foreign key.

 

This tutorial will show you how to do this more simply using Verj.io

 

Example 1 – Fetching data with foreign key

 

This example uses two database tables, USERS and DEPARTMENTS. The USERS table stores information relating to each user, for example their name. The DEPARTMENTS table stores the user’s department details. Each user belongs to a department which is stored in the USERS table as a foreign key. This foreign key’s value is an id that relates to the DEPARTMENTS table.

 

These tables are supplied in the EBASE_SAMPLES database schema provided with Verj.io and some test data has been loaded for this exercise.

 

If you need to create the ebase_samples database yourself scripts can be found in the Server\databaseSchemas folder in the server installation directory.

 

Fetch Users data into a form

 

Step 1 – create a project and add a folder

 

 

Step 2 - create a database resource

 

 

Step 3 - create a form

 

 

Step 4 - add a database resource

 

 

Step 5 - map a database resource to a table

 

·         Click Select All in the pop-up window. Then click OK twice and you will have a table with four columns.

 

Step 6 – hide the table column Section id

 

·         Drag this table onto the page (either in the WYSIWYG view or the Outline view) and hide the section_id column (right click on the Section id column header and select Hidden).

·         Drag the DEPARTMENT_ID column to the right hand side of the name column so the order of table column controls is: Id, Name, Department id, Section id (hidden).

 

Step 7 - add a before page event script

 

·         Right click on Page_1 in the Pages View and select the Events option.

·         Select the Before Page tab and click on New Script. Call this script rdFetch and accept the default file location.

·         There is a choice of programming languages, so select the language you prefer from the dropdown when creating a new script. Scripts are shown below for Javascript and FPL (the built-in Form Processing Language):

·         In the newly displayed script enter the text:

 

Javascript:

FPL:

 

tables.USERS.fetchTable();

 

fetchtable users;

 

·         Navigate back to the RdExample form, then run this form by clicking on the  icon on the Form’s toolbar. This should open the form in a browser and display the table with data:

 

     

     

 

 

 

Now we can see the data in our table, which is great.  However most people will struggle to know what the Department id value actually means…

 

What we want to do is display the Department description rather than the ID, but retain the id value in the table in case we decide to update the data. To do this we create a Dynamic List which has the ability to display one value yet return another (the department description and department id respectively).

 

Create a Dynamic List for departments

 

Step 1 - create a database resource - DEPARTMENTS

 

 

Step 2 - create a dynamic list – DEPARTMENTS_list

 

 

Step 3 - link a dynamic list to a table field

 

 

 

Note that in the Department id column, we now see that the department description instead of the id, and that for each row the Dynamic List is displaying the department description using the corresponding department id.

·                      

Add the ability to update the data to the database

 

Step 1 - add an event script

·                      

·         From the Palette View  drag a Button Control on to the page below the table. Double click the Button Control and add the text “Update” and click OK.

·         Right click on the new button and select the Events option. Click New Script, call the script rdUpdate, accept the default file location, then click OK.

      In the newly displayed script enter:

 

API based language (Javascript):

FPL:

 

tables.USERS.updateTable();

 

updatetable users;

 

·         Navigate back to the RdExample form, then run the form again by clicking on the  icon on the form’s toolbar, edit some of the data and click the new Update button. Your edited data should be stored in the database.

 

·                         

Example 2 - Fetching hierarchical/tree data

Example 2 will build on Example 1 above. We will add another Dynamic List to display the user’s department section. The difference this time is that the contents of the SECTIONS_list Dynamic List will depend on the DEPARTMENT_ID. This is a common requirement when manipulating hierarchical data.

·                      

The USERS database table that we used previously has a hidden column containing user data, section_id. This id refers to primary key data in the database table SECTIONS which contains section IDs and descriptions (Each department has a number of related sections, so each user has a department and a section).  Here are the three database tables:

·                      

 

 

Show the table column section id (hidden)

 

·         If it is not already open, open the RdExample form created in Example 1 above.

·         Right click on the Section id column header in the WYSIWYG View and deselect the Hidden attribute.

·         Run the form  to confirm you can see this column and that it contains data.

 

·                      

Create a Dynamic List for sections

 

Step 1 - add a database resource

 

·         Select Tools > Resource Wizards > Database Resource Wizard from the menu at the top of the studio window to start the database import wizard.

·         Click the … button to the right of Select Database Connection and select EBASE_SAMPLES from the list.

·         Click the … button to the right of Select the target location for the resource(s) and select the Tutorial4 folder in the Tutorials project. Then click Next.

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

·         Then select the SECTIONS table and click Next.

·         In the next panel click the Tables Only checkbox for the SECTIONS table and then click on ID in the Columns for Where Clause column, and deselect the ID column, click OK.

·         Click Finish - the system will create the SECTIONS Database Resource and open it.

 

Step 2 - add a dynamic list

 

·         From the newly created Database Resource we can create a Dynamic List by clicking on the Build Dynamic List icon  on the toolbar. Accept the default name of SECTIONS_list and click OK.

·         On the next page of the wizard select the DEPARTMENT_ID field for the where clause and click Next.

·         Click the Select All button to highlight the remaining two fields and then click Finish to create the Dynamic List.

 

Step 3 – map the dynamic list to a table field

 

·         Navigate in the studio back to the RdExample form and select the USERS-SECTION_ID column in the Tables View; this will display the column’s properties in the Properties View. 

·         Change the Display Type property from Text to Drop Down.

·         In the same set of properties click the … button on the Dynamic List property and select the newly created SECTIONS_list Dynamic List, doing so will pop up the mappings dialog.

·         Set the List Display Field value to be SECTION_DESCRIPTION and the List Return Field to be ID. Map the remaining list field DEPARTMENT_ID to form field USERS-DEPARTMENT_ID. Click OK.

 

Step 4 – run the form

 

·         Run the form again by clicking on the  icon on the form’s toolbar.

·         Note that the section id drop down is set to a descriptive value and only those values from the appropriate department are shown. This is due to the where clause on the Dynamic List mapped in the List Mappings dialog.  Note: If you changed and saved department data in Example 1 some users may not have a section set for them.

·                      

     

Step 5 – add Immediate Validation to departments column

 

·         Right click on the Department id column header in the WYSIWYG View and select the Immediate Validation option. This will make the page update automatically at runtime when the Department id column changes value.

·         Select the USERS-SECTION_ID column in the Tables View and set the When list is built property in the Lists section to Each display.

 

Step 6 – run the form again

 

·         Run the form and change the value of a user’s department, the form should refresh the page and the corresponding sections column will rebuild the list to have the relevant sections in it for the selected department.

 

Extra

Step 7 – enable display only option cc

 

The above example displays data that can be edited and updated, whereas in many cases you only want to display data.

 

 

Step 8 – run the form again