Child pages
  • The Servoy Foundset
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

The Servoy Foundset is a developer's window into Servoy's Data Binding layer. A single foundset always maps to a single database table (or view) and is responsible for reading from and writing to that table. From the user interface, a foundset controls which records are loaded and displayed, as well as how records are created, edited and deleted. From the developer's perspective, a foundset is a programmable object with specific behaviors and run-time properties that provide a high-level abstraction to facilitate low-level data operations.

For all programming reference information, see the JSFoundSet API documention in the reference guide.

Forms Bound to a Foundset

A Servoy Form is typically bound to a single database table and the form will always contain a single Foundset object which is bound to the same table. Much of the action in the user interface, such as a user editing data fields, directly affects the form's foundset. Conversely, actions taken on the foundset, such as programmatically editing data, is immediately reflected in the form.

While a form is always bound to a foundset, a foundset may be used by zero or more forms. Foundsets can be created and used by a programmer to accomplish many tasks.

Often, there can be several different forms which are bound to the same table. In most cases the forms will share the same foundset and thus provide a unified view. For example, imagine a form showing a list of customer records, where clicking on one of the records switches to another form showing a detailed view of only the selected record. The forms will automatically stay in sync and there is no need to coerce the forms to show the same record. Exceptions to this behavior include scenarios where forms are shown through different Relations, or have been explicitly marked to use a separate foundset.

See also the namedFoundset property of a form.

Loading Records

One of the primary jobs of a Foundset is to load records from the table to which it is bound. A Foundset object is always based on an underlying SQL query, which may change often during the lifetime of the Foundset. However the query will always take the form of selecting the Primary Key column(s) from the table and will also always include an Order By clause, which in its simplest form will sort the results based on the Primary Key column(s).

Foundset Loading
SELECT customerid FROM customers ORDER BY customerid ASC

After retrieving the results for Primary Key data, the Foundset will issue subsequent SQL queries to load the matching record data in smaller, optimized blocks. This query happens automatically in an on-demand fashion to satisfy the Foundset's scrollable interface.

Example: Record loading query
SELECT * FROM customers WHERE customerid IN (?,?,?,?,?,?,?,?) ORDER BY customerid ASC

A foundset's underlying query can change dramatically throughout the client session. The following events will modify a foundset's underlying query

  • When a form to which it is bound is loaded
  • When the loadRecords method is called programmatically
  • When the sort definition is changed
  • When it exits find mode

See also the Database Manager's getSQL and getSQLParameters methods

Loading Records Programmatically

The loadRecords method is used to directly modify the underlying query that loads PK data. There are several uses.

Load by a single PK

This is the simplest approach, which loads a single recordy by its primary key value.

foundset.loadRecords(123);
Load by PK data set

This approach simply dictates that a foundset will load records based on specified primary key data.

var ids = [1,2,3,6,9]; 				// an array of record PKs
var ds = databaseManager.convertToDataSet(ids); // convert the ids to a JSDataset
foundset.loadRecords(ds);			// load records

Notice the array was converted first to a JSDataset object. This object, which is like a 2-dimensional array, is used to provide support for composite primary keys.

Load by another foundset

This approach is useful to essentially copy the query of another foundset.

foundset.loadRecords(anotherFoundset);
Load by Query

This approach allows a SQL query fragment to be used to set the foundset's underlying query. There are certain restriction on the form that a query can take. For obvious reasons, the query must return the primary key column(s) from the table to which the foundset is bound. For a full description see the reference guide.

var sql = 'select id from my_table where my_table.column1 in ?,?,?;
var args = [1,2,3];
foundset.loadRecords(sql, args);

See also the loadRecords API in the reference guide for complete usage options.

Sorting

All foundsets contain a sorting definition that determines the order in which records are loaded and displayed. Sorting is always expressed in the ORDER BY clause of a foundset's query and thus handled by the database.

A foundset's sorting definition is encapsulated in a String property, which can be programmatically read using the getCurrentSort method, and written using the sort method.

Parameters for this property include an ordered list of one or more data providers, each of which having a sort direction, either ascending or descending. The string takes a form such that each data provider and its sort direction are separated by white space. The direction is abbreviated either asc or desc. Multiple data providers are separated by commas.

Example: Sort String Format

'column1 asc, column2 desc'	// Sort on column1 ascending, then column2 desceding

The order of the data providers determines their relative priority when sorting, such that when two records contain the same value for a higher priority data provider, the sorting will be deferred the next lowest priority data provider.

Example: The following sort string will sort, first on last name, and second on first name.

foundset.sort('last_name asc, first_name asc');

The result is that all records are sorted by last name. But in the case where the last names are the same, then the first name is used.
|| last_name || first_name ||

Sloan

Zachary

Smith

Jane

Smith

Jon

Snead

Aaron

Available Data Provider Types

The following data provider types may be used as sort criteria:

  • Any Column
  • Any Related Column
  • Any Related Aggregate

Example: Sort a customers foundset based on the number of orders each customer has, in this case a related aggregation.

SELECT customers.customerid FROM customers 
INNER JOIN orders ON customers.customerid=orders.customerid 
GROUP BY customers.customerid ORDER BY count(orders.orderid) ASC

Results in the following query:

SELECT customers.customerid FROM customers 
INNER JOIN orders ON customers.customerid=orders.customerid 
GROUP BY customers.customerid ORDER BY count(orders.orderid) ASC

Sorting on related columns and aggregates changes is simple and powerful. However this changes the nature of the foundset's query. One should be advised of this and ensure that the database is tuned accordingly.

Scrolling Result Set

 

  • No labels