Servoy 2020 Documentation
Child pages
  • The Servoy Foundset

Versions Compared

Old Version 27

changes.mady.by.user Haniela Hardut

Saved on

compared with

New Version Current

changes.mady.by.user Laurian Vostinar

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

One of the primary jobs of a foundset is to load records from the table to which it is bound. A foundset 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).

Code Block
titleFoundset Loading
borderStylesolid
langsql
titleFoundset 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.

Code Block
lang
borderStylesolid
langsql
titleExample: Record loading query
borderStylesolid
sql
SELECT * FROM customers WHERE customerid IN (?,?,?,?,?,?,?,?) ORDER BY customerid 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

...

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 it is the only option that ensures iterating over all the records of the foundset, not 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

...

Gliffy Diagram
nameshared foundsets(copy)
pagePin1

Note

If relation is invalid relation name will return null in scripting. There is a helper method: databaseManager.hasRecords(JSRecord, relationName) to check if a related foundset is valid and has records.

Foundsets and Data Broadcasting

...

Excerpt

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. Please note: This method will not trigger any associated Table Events or modification columns.

Code Block
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.

Code Block
//	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();


Note

When using this approach, it matters what the selected index of the foundset is. The update will start with this record.


...