...
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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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:
...
The The loadRecords
method is used to directly modify the underlying query that loads PK data. There are several uses.
...
Tip |
---|
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. |
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.
...
Code Block |
---|
// 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 |
...
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.
...
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 Clauseits WHERE
clause, the parameters by which to constrain the foundset.
...
A Foundset 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.
Excerpt | ||||||
---|---|---|---|---|---|---|
Performing Batch UpdatesFoundsets 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 FoundsetThis 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.
Updating a Partial Foundset with Different Values for Each RecordThe 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.
|