Programming with Tables
Script
statements for table processing
Loading
a table from a resource
Updating
a resource with table data
Copying
table data to another table
4.
Table cell level operations
Table row
processing using row numbers
Building a
list programmatically
See also: Table Basics, Table
Control, Repeater Control, Table Display Features, Tables Tutorial, FPL Command Syntax
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:
There are a number of column events and these vary
according to the type of column control:
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.
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 Javascript form.gotoPage() methods, call form commands or Javascript 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: |
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:
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; } } |
FPL: |
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.
FPL: |
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.
FPL: |
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.
FPL: |
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();
FPL: |
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.
See also processing using row numbers.
FPL: |
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.
FPL: |
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).
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.
Javascript:
To achieve the same effect
with Javascript, 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";
}
}
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.
FPL: |
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 Javascript, a specific row can also be specified.
See also User row selection, User row deletion and Adding rows.
These are operations
affect the entire column.
FPL: |
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 a column removes the column from the displayed table; similarly showing
a column adds the column to the displayed table.
FPL: |
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(); |
These are operations
affect only a single table cell – the specified column on the current row.
FPL: |
Javascript: |
set/unset columnname [in
table tablename] DISPLAYONLY | MANDATORY |
HIDDEN; |
controls.FIELDCOLUMN1.displayOnly
= true; controls.FIELDCOLUMN1.mandatory
= true; controls.FIELDCOLUMN1.hidden
= true; |
Hiding a table cell
displays white space instead of the cell value.
FPL: |
Javascript: |
hide columnname [in table
tablename]; show columnname [in table
tablename]; |
controls.FIELDCOLUMN1.hide(); controls.FIELDCOLUMN1.show(); |
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: |
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++; } |
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.
Javascript:
To achieve the same effect
with Javascript, set control properties such as
background color on the appropriate column on the
current row e.g.
columns.FIELDCONTROL1.backgroundColor = "yellow";
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.
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 Javascript
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 Javascript method Table.updateTable() is executed - $ROW_INSERTED is set to
'N' and Javascript 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 Javascript,
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 Javascript Table.setRowEmpty() method. These statements also
set the $ROW_EMPTY system variable.
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: |
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.
Rows can also be inserted programmatically using
the FPL insertrow command or Javascript 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: |
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: |
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; |
See Nested Tables.
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 |
Javascript method |
fetchtable updatetable resettable copytable insertrow |
Table.fetchTable() Table.updateTable() Table.resetTable() Table.replaceTable() Table.insertRow() |
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: |
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: |
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.
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: |
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: |
Javascript: |
set TABLECONTROL1.visibleRow = rowid; |
controls.TABLECONTROL1.visibleRow = rowid; |
Set a row as the current row of the table e.g.
FPL: |
Javascript: |
set MYTABLE.currentRow = rowid; |
tables.MYTABLE.setCurrentRow(rowid); |
Delete a row e.g.
FPL: |
Javascript: |
deleterow MYTABLE rowid; |
tables.MYTABLE.deleteRow(rowid); |
Highlight a row:
FPL: |
Javascript: |
highlight row MYTABLE rowid BOLD; where BOLD is a CSS class. |
No direct equivalent |
Unighlight a row:
FPL: |
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: |
Javascript: |
set TABLECONTROL1.visibleRow = TABLECONTROL1.firstDisplayRow; |
controls.TABLECONTROL1.visibleRow = controls.TABLECONTROL1.firstDisplayRow; |
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: |
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 Javascript 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.
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.
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:
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: |
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.