...

A Servoy Form is typically bound to a single database table and the form will always contain a single Foundset object foundset 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.

...

One of the primary jobs of a Foundset foundset is to load records from the table to which it is bound. A Foundset object foundset is always based on an underlying SQL query, which may change often during the lifetime of the Foundsetfoundset. 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 an ORDER BY clause, which in its simplest form will sort the results based on the Primary Key column(s).

...

After retrieving the results for Primary Key data, the Foundset 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 Foundsetfoundset's scrollable interface.

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

...

Loading Records Programmatically

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

...

Scrolling Result Set

The Foundset foundset maintains a scrollable interface for traversing record data. This interface includes a numeric index for every record that is returned by the Foundsetfoundset's query.

Foundset Size

The Foundset foundset also has a Size a size property, which indicates the number of records that are indexed by the Foundset foundset at any given time. Because the Foundsetfoundset's SQL query may eventually return thousands or millions of results, the initial size of the Foundset foundset has a maximum of 200. This value can grow dynamically, in blocks of 200, as the Foundset foundset is traversed.

Selected Index

The Foundset foundset maintains a Selected Indexselected index, a cursor with which to step through the records. If the selected index equals or exceeds the size of the Foundsetfoundset, the Foundset foundset will automatically issue another query to load the next batch of primary key data. Thus the Foundset foundset loads record data and grows dynamically with the changing Selected Index property. There are two methods used to get/set the foundsets selected index. They are getSelectedIndex and setSelectedIndex respectively.

...

// Foundset size grows dynamically as the Foundsetfoundset is traversed
foundset.getSize(); // returns 200
foundset.setSelectedIndex(200);
foundset.getSize(); // returns 400 because the foundset loaded the next 200 record pks

...

There are several approaches to iterating, each having their particular usage: using the foundset iterator, : using the foundset iterator, changing the selected index of the foundset, accessing a record object, accessing data provider values as an array.

Using the founset iterator is mostly While the last three iterating options are more intuitive, and also vary with regards to performance and usage, the foundset iterator is the most recommended to be used since

Changing the Selected Index

Perhaps the most intuitive approach is to programmatically change the foundset's selected index property. 

Example: The example below iterates over the entire foundset using a for loop.

for(var i = 1; i <= foundset.getSize(); i++){
	foundset.setSelectedIndex(i);
	// operate on the selected record
}

See also the JSFoundset's setSelectedIndex method.

Accessing a Record Object

While setting the selected index of the foundset is sometimes necessary, it also contains some overhead and therefore is not always the most efficient way to iterate over a foundset. However, one can iterate in a similar manner, access a record object without changing the selected index of a foundset by using the getRecord method of the foundset.

Example This example iterates over the foundset, but does not affect the selected index. The performance will be better than the previous example, and will not have any side effects in the UI if the foundset is bound to a form.

for(var i = 1; i <= foundset.getSize(); i++){
	var rec = foundset.getRecord(i);	// does not affect the selected index
}

See also the JSFoundset's getRecord method.

Accessing Data Provider Values As an Array

Sometimes the purpose of iterating over a foundset is to access all the values for a particular data provider. The most efficient way to do this is to obtain an array of values for the foundset's data provider using the getFoundSetDataProviderAsArray method of the databaseManager API.

Example This example shows how to access all the values in a foundset for a single data provider. Iterating over a simple array offers better performance over normal foundset iteration.

...

it is the only option that ensures iterating over all the records of the foundset, without missing any of them due to the multiple clients performing changes on the same foundset at the same time.

It is also possible to use JSFoundsetUpdater API to iterate over and update a foundset, though iterating is not its main goal.

Using the Foundset Iterator

Sometimes there is more than one user working on the same foundset, possibly inserting or deleting records. When iterating on a foundset, it needs to be ensured that the loop neither skips nor processes twice any record due to the foundset modifications occurred from other clients. Thus, in such cases, a secure iterator is needed to perform the iteration on the foundset.

The forEach method does exactly that, iterating over all the records of a foundset and calling the callback method given as parameter for each one of them.

Example This is an example of how to use the forEach method for iterating over a foundset.

foundset.loadAllRecords();
foundset.forEach(
			/**
			 * @param {JSRecord} record
			 * @param recordIndex
			 * @param {JSFoundset} fs 
			 */
			function(record, recordIndex, fs) {
				application.output("record processed: " + record + ", record index: " + recordIndex);
			}
		);

See also the JSFoundset's forEach method.

Changing the Selected Index

Perhaps the most intuitive approach is to programmatically change the foundset's selected index property. 

Example: The example below iterates over the entire foundset using a for loop.

for(var i = 1; i <= foundset.getSize(); i++){
	foundset.setSelectedIndex(i);
	// operate on the selected record
}

See also the JSFoundset's getFoundSetDataProviderAsArray setSelectedIndex method.

Using the Foundset Iterator

Sometimes there is more than one user working on the same foundset, possibly inserting or deleting records. When iterating on a foundset, it needs to be ensured that the loop neither skips nor processes twice any record due to the foundset modifications occurred from other clients. Thus, in such cases, a secure iterator is needed to perform the iteration on the foundset.

The forEach method does exactly that, iterating over all the records of a foundset and calling the callback method given as parameter for each one of them.

Example This is an example of how to use the forEach method for iterating over a foundset.

...

Accessing a Record Object

While setting the selected index of the foundset is sometimes necessary, it also contains some overhead and therefore is not always the most efficient way to iterate over a foundset. However, one can iterate in a similar manner, access a record object without changing the selected index of a foundset by using the getRecord method of the foundset.

Example This example iterates over the foundset, but does not affect the selected index. The performance will be better than the previous example, and will not have any side effects in the UI if the foundset is bound to a form.

for(var i = 1; i <= foundset.getSize(); i++){
	var rec = foundset.getRecord(i);	// does not affect the selected index
}

See also the JSFoundset's forEach getRecord method.

Related Foundsets

Foundsets are often constrained or filtered by a Relation. In this situation, the foundset is said to be a Related Foundset and its default SQL query will include in its Where Clause, the parameters by which to constrain the foundset.

It is important to make the distinction that a relation and a foundset are not one in the same. Rather, a relation name is used to reference a specific foundset object within a given context. The context for a related foundset is always a specific record object. But for convenience, related foundsets may be referenced within a form's scripting scope and as a property of any foundset. However in these cases, the context is always implied to be the selected record in the context.

For example:

Take a predefined Relation, customers_to_orders, which models a one-to-many relationship between a customers table and an orders table. The following three lines of code, executed within the scripting scope of a form based on the customers table, all produce the same result.

// Returns the number of orders for the selected customer record in this form's foundset
customers_to_orders.getSize();

// ...the same as:
foundset.customers_to_orders.getSize();

// ...also the same as:
foundset.getSelectedRecord().customers_to_orders.getSize();

Related foundsets can be chained together using relation names. Again, the shorthand implies the context of the selected record for each foundset.

Example

...

Accessing Data Provider Values As an Array

Sometimes the purpose of iterating over a foundset is to access all the values for a particular data provider. The most efficient way to do this is to obtain an array of values for the foundset's data provider using the getFoundSetDataProviderAsArray method of the databaseManager API.

Example This example shows how to access all the values in a foundset for a single data provider. Iterating over a simple array offers better performance over normal foundset iteration.

var ids = databaseManager.getFoundSetDataProviderAsArray(foundset,'order_id');
for(i in ids){
    var id = ids[i];
}

See also the JSFoundset's getFoundSetDataProviderAsArray method.

Related Foundsets

Foundsets are often constrained or filtered by a Relation. In this situation, the foundset is said to be a Related Foundset and its default SQL query will include in its WHERE clause, the parameters by which to constrain the foundset.

It is important to make the distinction that a relation and a foundset are not one in the same. Rather, a relation name is used to reference a specific foundset object within a given context. The context for a related foundset is always a specific record object. But for convenience, related foundsets may be referenced within a form's scripting scope and as a property of any foundset. However in these cases, the context is always implied to be the selected record in the context.

For example:

Take a predefined Relation, customers_to_orders, which models a one-to-many relationship between a customers table and an orders table. The following three lines of code, executed within the scripting scope of a form based on the customers table, all produce the same result.

// Returns the number of order detailsorders for the selected order record of the selected customer:
 customer record in this form's foundset
customers_to_orders.getSize();

// ...the same as:
foundset.customers_to_orders.orders_to_order_details.getSize();

// ...isalso the same as:
customers_to_ordersfoundset.getSelectedRecord().orderscustomers_to_order_details.getSelectedRecord().orders.getSize();

Foundsets and Data Broadcasting

A Foundset may be automatically updated when the client receives a Data Broadcast Event . If the data change affected the table to which the foundset is bound, the foundset will be refreshed to reflect the change.

Performing Batch Updates

Foundsets are typically updated on a record-by-record basis, either as the user operates on a foundset-bound GUI component, or through programmatic interactions. However, sometimes it is necessary to perform an update to an entire foundset. For performance reasons, it is not advised that this be done by programmatically iterating over the foundset's records. Rather, it is recommended that batch updates be performed using the JSFoundsetUpdater API.

The Foundset Updater API is ideal to use for the following situations:

Updating an Entire Foundset

This essentially has the effect of issuing a SQL UPDATE statement using the WHERE clause that constrains the foundset. This presents a significant performance advantage over updating records individually. In the example below, a related foundset is updated, meaning all orders belonging to the selected customer will be affected.

var fsUpdater = databaseManager.getFoundSetUpdater(customers_to_orders);
fsUpdater.setColumn('status',101);
fsUpdater.performUpdate();

Updating a Partial Foundset with Different Values for Each Record

The Foundset Updater API can also be used to update part of a foundset. Moreover, unlike the above example, this approach allows for different values for each record. In the example below, the first 4 records (starting from the selected index) are updated by specifying an array of values for each column that is affected.

//	update first four records
var fsUpdater = databaseManager.getFoundSetUpdater(foundset);
fsUpdater.setColumn('customer_type',[1,2,3,4]);
fsUpdater.setColumn('my_flag',new [1,0,1,0]);
fsUpdater.performUpdate();

...

Related foundsets can be chained together using relation names. Again, the shorthand implies the context of the selected record for each foundset.

Example

// Returns the number of order details for the selected order record of the selected customer:
customers_to_orders.orders_to_order_details.getSize();

// ...is the same as:
customers_to_orders.getSelectedRecord().orders_to_order_details.getSelectedRecord().getSize();

Foundsets and Data Broadcasting

A foundset may be automatically updated when the client receives a Data Broadcast Event . If the data change affected the table to which the foundset is bound, the foundset will be refreshed to reflect the change.

var fsUpdater = databaseManager.getFoundSetUpdater(customers_to_orders);
fsUpdater.setColumn('status',101);
fsUpdater.performUpdate();

...

...

...

//	update first four records
var fsUpdater = databaseManager.getFoundSetUpdater(foundset);

fsUpdater.

setColumn('customer_type',[1,2,3,4]);
fsUpdater.setColumn('

my_flag',new [1,0,1,0]);

fsUpdater.performUpdate();