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