Programming with Tables

Documentation home 

 

Events 1

Table Control Events 1

Table Column Events 2

Current row concept 2

Script statements for table processing. 2

1. Table level operations 2

Looping through table rows 2

Loading a table from a resource. 3

Updating a resource with table data. 3

Copying table data to another table. 3

Clearing table data. 4

Sorting a table. 4

2. Row level operations 4

Deleting rows 4

Inserting rows 5

Highlighting rows 5

Unhighlighting rows 6

Setting row attributes 6

3. Column level operations 6

Setting column attributes 6

Hiding/showing a column. 7

4. Table cell level operations 7

Setting cell attributes 7

Hiding/showing a cell 7

Getting/setting cell values 7

Highlighting cells 8

Unhighlighting cells 8

Adding rows 8

Adding rows by the end user 9

Adding rows programmatically. 9

Nested Tables 10

Table row processing using row numbers 10

Obtaining row numbers 10

Working with row numbers 11

Working with empty tables 12

Issuing messages from scripts 13

Lists in tables 13

Building a list programmatically. 13

  

See also: Table Basics, Table Control, Repeater Control, Table Display Features, Tables Tutorial, FPL Command Syntax

 

 

Events

Table Control Events

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:

 

 

 

Table Column Events 

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

 

 

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

 

See also processing using row numbers.

 

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;

 

See also highlight table cell.

 

API based language:

To achieve the same effect with an API based language, set control properties such as background color for all controls on the current row e.g.

 

for each (var col in tables.EMPLOYEES.columns)

{

  if (col.columnControl)

  {

    col.columnControl.backgroundColor = "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.

See also User row selection, User row deletion and Adding rows.

 

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.

See also highlight row.

 

API based language:

To achieve the same effect with an API based language, set control properties such as background color on the appropriate column on the current row e.g.

 

columns.FIELDCONTROL1.backgroundColor = "yellow";

 

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:

 

 

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:

 

 

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:

 

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:

 

 

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.