A table can be displayed on a page using either a Table Control or a Repeater Control. A Repeater Control does not support any events whereas a Table Control supports three events: before table event, after table event and add row event:

The before table event is fired when the page on which the control appears is first prepared for processing. It can be used to prepare the table for display for the first time. For example, data could be loaded from the database, certain columns could be hidden or made visible or totals fields calculated etc. This event is only fired when the user moves forwards to the page containing the table using the next page button or as a result of an FPL goto page command or API form.gotoPage() method. It is not fired when the page is re-displayed or when the previous page button is clicked.

The after table event is fired after information has been received by the user and when all validation events for table cells have completed successfully. This event is not fired if the user clicks on a scroll icon, a sort icon or clicks the add row button.

The add row event is fired when the user clicks the supplied add row button which is only displayed when the Show Add Row Button option has been selected. This event is provided to allow the application to supply values for the new row. (See Adding rows)

Table Column Events

There are a number of column events and these vary according to the type of column control:

Button Column Controls and Image Column Controls support on click events.
Table Column Controls support before control and validation events. On click events are also supported when the table column is a hyperlink or has immediate validation enabled.

Current row concept

The system maintains a current row on the server for each table and all references to column names within a table from script statements are interpreted as referring to the value of the column on the current row. A table’s current row is set automatically when any sort of event occurs on a control inside a Table Control or a Repeater Control – most commonly the user clicks on something within a table display. This applies to all server-side events (i.e. when a server-side script is executed) and also to any $eb.executeFunction() calls issued from client-side jQuery events. Click here for more details.

Script statements for table processing

1. Table level operations

Looping through table rows

The examples below show syntaxes for looping through all rows in a table - changing the current row for each pass through the loop. Note that all rows in the table are presented to the loop, not just the rows that are visible to the user. As each successive iteration begins, the system sets the current row to the appropriate table row. This current row value is maintained for all processing included within the scope of the loop; this includes additional nested loops, goto page commands or API form.gotoPage() methods, call form commands or API form.callForm() methods etc. If the loop is interrupted e.g. by an FPL break command or a script statement that immediately terminates processing, the current row is set to the break point. If the loop completes normally (all rows have been processed) the current row is reset to its original value.

FPL:

API based language (Javascript):

loop at table tablename

...

endloop

Example:

set ORDER_ITEMS-ORDER_TOTAL = 0;

loop at table ORDER_ITEMS

if [ $ROW_DELETED != 'Y' ]

set ORDER_ITEMS-ORDER_TOTAL = ORDER_ITEMS-ORDER_TOTAL + ORDER_ITEMS-ITEM_AMOUNT;

endif

endloop

A TableRowIterator is used to iterate through table rows. A TableRowIterator can be obtained using any of the following methods on Table:

getRows()
fetchTable()
findRows()

TableRowIterator provides methods to process table rows either forwards or backwards e.g.

var orderTotal = 0;

var rows = tables.ORDER_ITEMS.getRows();

while (rows.next())

{

if ( !tables.ORDER_ITEMS.isRowDeletedByUser() )

{

orderTotal += tables.ORDER_ITEMS.ITEM_AMOUNT.value;

}

Loading a table from a resource

FPL:

API based language (Javascript):

fetchtable tablename;

tables.tablename.fetchTable();

These statements load the table with data retrieved from the backing external resource associated with the table, and set the current row to point to the first row in the table. Internal row numbers are reset. These statement should not be issued while looping through table rows.

Updating a resource with table data

FPL:

API based language (Javascript):

updatetable tablename;

tables.tablename.updateTable();

These statements update the backing external resource with the table data, deleting, inserting, and updating rows as required. Any empty rows are ignored and are not written to the external resource (see Adding rows).

Internal row numbers are reset by this command and therefore the current row may be changed. Circumstances that change the current row include: issuing updatetable on a table that has previously been sorted, issuing updatetable where one or more rows have been deleted.

These statements should not be issued while looping through table rows.

Copying table data to another table

FPL:

API based language (Javascript):

copytable sourcetablename [to] targettablename;

tables.targettablename.replaceTable(tables.sourcetablename);

These statements copy the contents of the source table to the target table. Data in table columns where the column name is the same in both tables is copied. Internal row numbers are reset. These statements can be used to transfer table data from one resource to another resource. e.g. the requirement might be to read data from a database and write it to part of an XML document.

Clearing table data

FPL:

API based language (Javascript):

resettable tablename;

tables.tablename.resetTable();

These statements clear the entire contents of the table and reset the current row. Internal row numbers are reset. Note that these statements will not result in the deletion of records from a backing resource specified for the table e.g. a Database Resource; any subsequent updatetable command will not remove these rows from the attached resource. To remove all rows from the table and the backing resource use:

FPL:

loop at T1

deleterow;

endloop

updatetable T1;

Javascript:

var t1 = tables.MyTable;

var rows = t1.rows;

while (rows.next())

{

t1.deleteRow();

}

t1.updateTable();

Sorting a table

FPL:

API based language (Javascript):

sort tablename by COL1, COL2 DESCENDING, .....;

tables.tablename.sort(tableColumn);

tables.tablename.sort([]tableColumns);

tables.tablename.sort(tableColumn, direction);

tables.tablename.sort([]tableColumns, []directions);

These statements provide a programmatic means of sorting table data. Any number of sort columns can be specified with any mixture of ascending and descending sort order.

2. Row level operations

Deleting rows

FPL:

API based language (Javascript):

deleterow;

deleterow tablename row;

e.g.

loop at table EMPLOYEES

if [ … ]

deleterow; // delete current row

endif

endloop

deleterow EMPLOYEES rowid; // delete specific row

// delete current row

tables.tablename.deleteRow();

// delete specific row

tables.tablename.deleteRow(row);

These statements delete a single row in a table. Rows deleted using these statements are immediately deleted from the table and are not available for further processing. This is in contrast to the supplied user row deletion feature which simply marks the row for deletion but does not remove it from the table.

The FPL deleterow command with no parameters deletes the current row in the current table and this form of the command can only be used within a loop at table construct or in a before control, validation or on click table cell event.

Inserting rows

FPL:

API based language (Javascript):

insertrow tablename [EMPTY];

tables.tablename.insertRow();

These statements insert a row into the specified table. New columns will be set to their default values or null if no default value has been specified. The current row pointer is moved to the inserted row. These statements should not be issued while looping through table rows.

The optional EMPTY (FPL only) indicates the system should treat the new row as empty until at least one field has been entered by the user (see Adding rows).

Highlighting rows

The highlight command is only available only via FPL.

FPL:

highlight row CSSclass;

highlight row tablename row CSSclass;

This command is used to apply the specified CSS class to all table cells on a row, for example the background color could be changed and/or the text could be displayed as bold etc. When used without any parameters, the command is applied to the current row in the current table and this form of the command can only be used within a loop at table construct or in a before control, validation or on click table cell event.

loop at table EMPLOYEES

if [ … ]

highlight row yellow;

endif

endloop

highlight row EMPLOYEES rowid yellow;

Unhighlighting rows

The unhighlight command is only available only via FPL.

unhighlight row;

unhighlight row tablename row CSSclass;

Removes a highlighting style from a row and is the reverse of the highlight row command. Command unhighlight all can be used to remove all highlighting styles on the current page.

Setting row attributes

FPL:

API based language (Javascript):

setrow/unsetrow SELECTED | DELETED | EMPTY;

tables.tablename.setRowSelected();

tables.tablename.setRowDeletedByUser();

tables.tablename.setRowEmpty();

These statements provide programmatic control over the status of a row. With FPL, they operate only on the current row. When using an API based language, a specific row can also be specified.

3. Column level operations

These are operations affect the entire column.

Setting column attributes

FPL:

API based language (Javascript):

set/unset column columnname [in table tablename] DISPLAYONLY | MANDATORY | HIDDEN;

// use the all attribute for operations on the entire column

controls.FIELDCOLUMN1.all.displayOnly = true;

controls.FIELDCOLUMN1.all.mandatory = true;

controls.FIELDCOLUMN1.all.hidden = true;

Hiding/showing a column

Hiding a column removes the column from the displayed table; similarly showing a column adds the column to the displayed table.

FPL:

API based language (Javascript):

hide column columnname [in table tablename];

show column columnname [in table tablename];

// use the all attribute for operations on the entire column

controls.FIELDCOLUMN1.all.hide();

controls.FIELDCOLUMN1.all.show();

4. Table cell level operations

These are operations affect only a single table cell – the specified column on the current row.

Setting cell attributes

FPL:

API based language (Javascript):

set/unset columnname [in table tablename] DISPLAYONLY | MANDATORY | HIDDEN;

controls.FIELDCOLUMN1.displayOnly = true;

controls.FIELDCOLUMN1.mandatory = true;

controls.FIELDCOLUMN1.hidden = true;

Hiding/showing a cell

Hiding a table cell displays white space instead of the cell value.

FPL:

API based language (Javascript):

hide columnname [in table tablename];

show columnname [in table tablename];

controls.FIELDCOLUMN1.hide();

controls.FIELDCOLUMN1.show();

Getting/setting cell values

References to a table column value are interpreted as referring to the named column on the current row. For example the following script will insert 10 rows into the EXPENSES table with the ITEM column having values 1 to 10.

FPL:

API based language (Javascript):

set EXPENSES-ITEM = 0;

loop [ EXPENSES-ITEM <= 10 ]

insertrow EXPENSES empty;

set EXPENSES-ITEM = EXPENSES-ITEM + 1;

endloop

for (var i = 1; i <= 10;)

{

tables.EXPENSES.insertRow();

tables.EXPENSES.setRowEmpty(true);

tables.EXPENSES.ITEM.value = i;

i++;

}

Highlighting cells

The highlight command is only available only via FPL.

FPL:

highlight tablecell columnname style;

This command is used to apply the specified CSS class to the named table cell on the current row of the current table. For example the background color could be changed and/or the text could be displayed as bold etc.

Unhighlighting cells

The unhighlight command is only available only via FPL.

unhighlight tablecell columnname;

Removes a highlighting CSS class from the named table cell on the current row. Command unhighlight all can be used to remove all highlighting styles on the current page.

Adding rows

Rows can be added to a table in one of two ways:

By the end user: a table can optionally be configured to display the Add Row Button to allow insertion of rows by end users. This is requested by setting the Show Add Row button table control property.
Programmatically by using the FPL insertrow command or the API Table.insertRow() method.

When the add row button is clicked, the add row event is triggered and this can be used to assign values to fields in the new row.

A new row is always inserted at the bottom of the table. Field values are set to null or a default value if one exists. For each row inserted using either the add row button or the insertrow command, system variable $ROW_INSERTED is set to 'Y'. The API method Table.isRowInserted() can also be used to query row status. The insert status is reset when the backing resource is updated i.e. FPL command updateTable or API method Table.updateTable() is executed - $ROW_INSERTED is set to 'N' and API method Table.isRowInserted() will return false.

Additionally, a new row may be configured as empty: rows added by the user with the add row button are always empty, rows added using the FPL insertrow command can optionally be configured as empty by adding the word EMPTY to the insertrow statetment. Using the API, method Table.setRowEmpty() can be used to set empty status. A row loses its empty status when at least one value is entered in the row by the end user - setting values in other ways e.g. via a script, does not affect the empty status. Empty rows in a table differ from other rows in the following respects:

Client side validation of mandatory fields is not done
Column validation events are not run
Buttons and hyperlinks on the same row are honoured
Empty rows are ignored by updatetable processing

The empty status can be set and cleared using the setrow/unsetrow EMPTY FPL commands or API Table.setRowEmpty() method. These statements also set the $ROW_EMPTY system variable.

Adding rows by the end user

When the user clicks the add row button, the add row event is fired and the current row pointer is changed to point to the new row. Here is an example of an add row event script that sets a unique id for the new row:

FPL:

API based language (Javascript):

MAX_ITEM_NO = MAX_ITEM_NO + 1;

ORDER_ITEMS-ITEM_NO = MAX_ITEM_NO;

tables.ORDER_ITEMS.ITEM_NO.value = ++fields.MAX_ITEM_NO.value;

where ITEM_NO is a column in table ORDER_ITEMS and MAX_ITEM_NO is a form field not included in the table.

Adding rows programmatically

Rows can also be inserted programmatically using the FPL insertrow command or API Table.insertRow() method. When these statements are executed, the current row pointer is moved to the new row so that columns can be assigned values. e.g.

FPL:

API based language (Javascript):

insertrow EMPLOYEES;

sequence EMPLOYEES;

EMPLOYEES-EMPLOYEE_ID = $NEXT_SEQUENCE_ID;

EMPLOYEES-XXXXXX = ..

tables.EMPLOYEES.insertRow();

var next = system.sequenceManager.sequence("EMPLOYEES");

tables.EMPLOYEES.EMPLOYEE_ID.value = next;

tables.EMPLOYEES.XXXXXXX.value = ..;

In some circumstances, it may be useful to treat the new row as empty e.g. an expenses form where you would like to prime the table with a few empty rows e.g.

FPL:

API based language (Javascript):

insertrow EXPENSES empty;

EXPENSES-EXPENSE_ID = 1;

insertrow EXPENSES empty;

EXPENSES-EXPENSE_ID = 2;

tables.EXPENSES.insertRow();

tables.EXPENSES.setRowEmpty();

tables.EXPENSES.EXPENSE_ID.value = 1;

tables.EXPENSES.insertRow();

tables.EXPENSES.setRowEmpty();

tables.EXPENSES.EXPENSE_ID.value = 2;

Nested Tables

See Nested Tables.

Table row processing using row numbers

The system maintains an internal row number for each row in a table. These row numbers can be used by an application to perform specific operations on a row e.g. make it visible, delete, highlight etc. Row numbers are set when the table is loaded and updated; specifically the following commands/methods set/reset row numbers:

FPL command

API method

fetchtable

updatetable

resettable

copytable

insertrow

Table.fetchTable()

Table.updateTable()

Table.resetTable()

Table.replaceTable()

Table.insertRow()

Obtaining row numbers

A row number can be obtained for each row when it is the current row:

With FPL, using the table’s currentRow property for each row when it is the current row.
With API based languages, using the Table.getCurrentRow() method

The row number for each row in a table could be obtained in this way when the table is loaded and then stored for subsequent use as shown in the example below (this stores the row number in an additional ROWID table column – this should be of type INTEGER):

FPL:

API based language (Javascript):

loop at table MYTABLE

set MYTABLE-ROWID = MYTABLE.currentRow;

endloop

var rows = tables.MYTABLE.rows;

while (rows.next())

{

tables.MYTABLE.ROWID.value = tables.MYTABLE.getCurrentRow();

}

When a row is added, the new row becomes the current row and its row number can be obtained using the currentRow property e.g.

FPL:

API based language (Javascript):

insertrow MYTABLE;

set MYTABLE-ROWID = MYTABLE.currentRow;

tables.MYTABLE.insertRow();

tables.MYTABLE.ROWID.value = tables.MYTABLE.getCurrentRow();

When a table is empty, the currentRow property has a value of -1.

Working with row numbers

Row numbers can be used for many functions as shown in the examples below. These examples use a variable rowid to contain the row number.

Getting/setting column values:

FPL:

API based language (Javascript):

Not available

var v1 = tables.MYTABLE.getColumnValueOnRow(tables.MYTABLE.COL1, rowid);

tables.MYTABLE.setColumnValueOnRow(tables.MYTABLE.COL1, rowid, "Test");

Make a row visible. This uses the visibleRow property of the Table Control identified by TABLECONTROL1 – setting this property makes the row visible

This property is available for both Table Controls and Repeater Controls.

FPL:

API based language (Javascript):

set TABLECONTROL1.visibleRow = rowid;

controls.TABLECONTROL1.visibleRow = rowid;

Set a row as the current row of the table e.g.

FPL:

API based language (Javascript):

set MYTABLE.currentRow = rowid;

tables.MYTABLE.setCurrentRow(rowid);

Delete a row e.g.

FPL:

API based language (Javascript):

deleterow MYTABLE rowid;

tables.MYTABLE.deleteRow(rowid);

Highlight a row:

FPL:

API based language (Javascript):

highlight row MYTABLE rowid BOLD;

where BOLD is a CSS class.

No direct equivalent

Unighlight a row:

FPL:

API based language (Javascript):

unhighlight row MYTABLE rowid BOLD;

where BOLD is a CSS class.

No direct equivalent

Scroll to the top of a table. This uses the firstDisplayRow property of the Table Control identified by TABLECONTROL1 – this property returns the row number of the first row in a scroll set.

This property is available for both Table Controls and Repeater Controls.

FPL:

API based language (Javascript):

set TABLECONTROL1.visibleRow = TABLECONTROL1.firstDisplayRow;

controls.TABLECONTROL1.visibleRow = controls.TABLECONTROL1.firstDisplayRow;

Working with empty tables

If an empty table is displayed to the user, message 'No records to display' replaces the usual info text 'displaying n to nn of nnn records'. In this circumstance, the only option available to the user is to click the add row button. Alternatively, the table could be primed with a number of empty rows as shown in the example below:

FPL:

API based language (Javascript):

insertrow EXPENSES;

// set initial values for the first row

set EXPENSES-START_DATE = $SYSTEM_DATE;

set EXPENSES-DESCRIPTION = 'First empty row';

insertrow EXPENSES;

// set initial values for the second row

set EXPENSES-START_DATE = $SYSTEM_DATE;

set EXPENSES-DESCRIPTION = 'Second empty row';

tables.EXPENSES.insertRow();

// set initial values for the first row

tables.EXPENSES.START_DATE.value = new Date();

tables.EXPENSES.DESCRIPTION.value = "First empty row";

tables.EXPENSES.insertRow();

// set initial values for the second row

tables.EXPENSES.START_DATE.value = new Date();

tables.EXPENSES.DESCRIPTION.value = "Second empty row";

The FPL insertrow command and API Table.insertRow() method change the current row pointer to the inserted row, so any subsequent statements operate against that row.

Empty tables are visible if either the Supports adding rows button or Display no. records info options on Table Control are selected. If just Display no. records info is selected then info text 'No records to display' is shown.

Issuing messages from scripts

When messages are displayed locally, an error or warning message issued during a table cell level event for a field within a table will be displayed immediately above the row containing the field in question. Messages issued at a before or after table event will be displayed immediately above the table.

Lists in tables

A table column can be configured as either a static or dynamic list allowing the user to select from a dropdown list. If a dynamic list is used, then the following additional features are supported:

The values displayed to the user need not be the same as the retained value for the table column – foreign keys can be used. Click here for tutorial on foreign keys in tables.
The values displayed in the list can be dependent on one or more table column values on the same row, or an external field.
Similarly additional values on the same row can be set automatically by the system when a value is selected from the pulldown list.

Building a list programmatically

We have a table CUSTOMER with columns ID, NAME and CREDIT_RATING where ID is a unique integer and NAME is the name of the customer. We want to display a dropdown list containing all customer names in alphabetical order, but which returns the customer id when a selection is made.

FPL:

API based language (Javascript):

A list can be built by a script using the buildlistfromtable function which takes four parameters:

· name of the field to which the list is attached

· name of the table

· name of the table column to be displayed in the list

· name of the table column to be returned when a value is selected

fetchtable CUSTOMER;

sort CUSTOMER by CUSTOMER-NAME;

set NUM_CUSTOMERS = buildlistfromtable('LIST_FIELD', 'CUSTOMER', 'CUSTOMER-NAME', 'CUSTOMER-ID';

A list can be built by a script using the WebFormFieldOrColumn.createCustomList() which returns a CustomList object. The following example shows constructing a list from a table:

tables.CUSTOMER.fetchTable();

tables.CUSTOMER.sort(tables.CUSTOMER.NAME);

var rows = tables.CUSTOMER.rows;

var list = fields.LIST_FIELD.createCustomList();

while (rows.next())

  list.add(tables.CUSTOMER.NAME.value, tables.CUSTOMER.ID.value);

If a list is added to a table column when the table is empty, the list will act as a default and be applied to all rows as they are added. If a list is added to a table column when the table is not empty, the list will be applied only to the current row.