Programmer's Guide:
Using Database Pages

A database is a new principal item in eRoom Release 5.0. Like other eRoom items, an eRoom database has its own properties and its own page. In the user interface, as well as with SAAPI, you can create, delete, edit, move, or set notification on a database, just as you can with other items.

A database page, called its summary page in the user interface, is like the front page of a report. You open the summary page of a database as you would open any eRoom item. The summary page shows all the entries in the database, according to the display options set for the database. A database entry is also an eRoom item with its own page, called the entry page. The entry page shows all the fields in an entry. Entry pages can have comments and attachments, just like other item pages.

A Database Page manages an eRoom database on its own page. You can also pick from a list of styles for the list. Each style contains a set of predefined columns. For example, a change request that contains three columns: Item (plain text, one line), Owner (drop-down list, multiple choices allowed), Priority (list with choices High/Medium/Low), Due Date (date) and Done (yes/no).

SAAPI gives you full access to the capabilities of Database Pages through the IERUDBPage and IERUDatabase interfaces. IERUDBPage is used to set the page display information, while IERUDatabase is used to manage the database contents. Database Rows, Database Columns, and Database Cells are objects that represent parts of a Database. The Database Rows contain the Database Cells. Database Columns are used to define the data type for the column and to give the column a name.

Creating a Database Page

To create a Database Page, use the CreateDatabasePage method in the IERUContainer interface of any container object. The sixth parameter of this method, Style, is of particular interest to SAAPI programmers. The Style parameter specifies a color scheme for the database, which will be used when members see the database in the eRoom user interface. The Style parameter's values are taken from the ERUDBStyle enum.

Creating Columns and Defining the Column Schema

Each Database Column has a name and a data type that constrains the values that can be entered in the Database Cells. To create a Database Column, use the CreateColumn method of IERUDatabase, supplying the column name and data type. The new column is appended to the right of any existing columns.

The following example adds a new column to an existing database called MyDB:

Dim NewCol as IERUDBColumn
Set NewCol = MyDB.CreateColumn("Due Date", erColumnTypeDate)

Depending on the data type specified for a column, you may need to provide some additional information. For example, for columns whose type is erColumnTypePlainText, the LineCount property limits the number of lines of text in a cell, and the Wrap property indicates whether long lines should wrap to the next line. If the column type is erColumnTypeDropDown, the DropDownOptions property returns the drop-down choices in the form of a collection, while the AllDropDownOptions lets you get or set all the choices at once, in a single, newline-delimited string. If the column type is erColumnDataTypeKeyword, the Choices property controls whether rows that have a cell in this column with a value of TRUE appear in the list.

Using Sort Columns

The sort column for a database is identified by the SortColumn property of IERUDatabase. This property specifies the index of the column to use for sorting purposes. The left-most column in a database has an index value of 1. The sort order is set by the SortOrder property, which is a value from the enum ERUSortOrder. The default sort order is to sort "by hand" - this mode is indicated by a SortColumn with the value 0.

The following example sets the left-most column as the sort column and sorts the database rows in ascending order.

MyDB.SortColumn = 1
MyDB.SortOrder = erSortAscending

Modifying Column Order

Columns can be reordered using the SetColumnPosition method of IERUDatabase. You specify the new index that the column should have; the column already at that position and the columns to its right are shifted to the right by one index.

MyDB.SetColumnPosition(1, NewCol)

Creating New Rows in a Database

Database Rows are created by calling the CreateRow method in IERUDatabase. This method simply creates a blank row with cell values set to the defaults that correspond to the column type.

Dim NewRow as IERUItem
Set NewRow = MyDB.CreateRow

Getting and Setting Values in Database Cells

You manipulate Database Cells through the Database Rows that contain them. To get the collection of Database Cells for a Database Row, access the Cells property of IERUDBRow. Besides the cells contained in the Cells collection, each Database Row has a special, hidden cell containing plain text, which is stored in the Notes property.

When setting values for Database Cells, you first assign the value for one or more cells, then commit the pending changes with the Update method in IERUDBRow. No changes are applied until Update is called; you can abandon the pending changes and start over by calling the Revert method.