ViewFoundSet
Overview
A View Foundset Datasource is a virtual table that loads data at runtime, often based on a QBSelect query object. It is useful for combining datasets from multiple tables, improving performance compared to standard relations, valuelists, or aggregates.
Creating View Foundset Datasources
View Foundset Datasources can be created in two ways:
Solution Explorer: Accessed via Datasources -> View foundsets -> Create view foundset in the Solution Explorer context menu. This opens the Table Editor for defining the datasource structure.
Runtime Creation: Created via
databaseManager.getViewFoundSet(String name, QBSelect query)
. This returns aViewFoundSet
object with essential operations likegetSize()
,getRecord()
, andloadAllRecords()
, but it doesn’t supportfind()
orsearch()
.
Data Broadcast and Monitoring Changes
View Foundsets do not listen for databroadcast changes by default but can be enabled using viewfoundset.enableDatabroadcastFor(QBTableClause queryTable, int flags)
. Available flags include:
MONITOR_COLUMNS: Tracks changes in specific columns.
MONITOR_JOIN_CONDITIONS: Listens for changes in join conditions.
MONITOR_WHERE_CONDITIONS: Tracks updates to WHERE clause columns.
MONITOR_INSERT: Monitors inserts, triggering a full query update.
MONITOR_DELETES: Tracks deletions, updating records as needed.
MONITOR_DELETES_FOR_PRIMARY_TABLE: Monitors primary table deletions more efficiently.
MONITOR_AGGREGATES: Observes changes impacting aggregates in the query.
Editable View Foundsets
A View Foundset can be made editable with save()
if the primary key (PK) is included in the QBSelect
. Data updates pause databroadcast refreshes until the records are committed.
Commands Summary
Create view foundset datasource: Opens the Table Editor.
Edit table/view: Edits the View Foundset structure via the Table Editor.
Delete View Foundset: Deletes the View Foundset.
Rename View Foundset: Renames the View Foundset.
For more details, refer to the View Foundset Datasource documentation in the Data modeling section.
Constants Summarized
Properties Summarized
Methods Summarized
Constants Detailed
MONITOR_AGGREGATES
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen for changes in columns (selected) of the given datasource in the query that can affect aggregates. This means that when there are deletes, inserts or updates on columns selected from that datasource, a full re-query will happen to refresh the aggregates.
IMPORTANT: in general, this flag should be set on (possible multiple) datasources from the query that have group by on their columns, and the columns don't contain the pk, or that have the actual aggregates on their columns (because all those could influence the value of aggregates).
For example (ignoring the fact that in a real-life situation these fields might not change), a view foundset based on this query:
SELECT orders.customerid, orders.orderdate, SUM(order_details.unitprice) FROM orders LEFT OUTER JOIN order_details ON orders.orderid = order_details.orderid GROUP BY orders.customerid, orders.orderdate ORDER BY orders.customerid asc, orders.orderdate desc
will want to enable databroadcast flag MONITOR_AGGREGATES on both "orders" (because if "orderdate" or "customerid" - that are used in GROUP BY - change/are corrected on a row, that row could move from one group to the other, affecting the SUM(order_details.unitprice) for the groups involved) and "order_details" (because if "unitprice" changes/is corrected, the aggregate will be affected).
But if the above query would also select the orders.odersid (and also group by that) then the orders row that you select for that sum will always be unique and only #MONITOR_COLUMNS has to be used for those - if needed.
Type Number
MONITOR_COLUMNS
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen also for column changes of the given table/datasource. This is used by default if you just use enableDatabroadcastFor() without flags. If you use the one with the flags you need to give this one if you just want to listen to column changes that are in the result for a given datasource and pk.
This constants needs to have the pk's selected for the given datasource (should be in the results).
Type Number
MONITOR_DELETES
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen for deletes on the given table/datasource. This will always result in a full query to detect changes whenever an delete on that table happens.
Type Number
MONITOR_DELETES_FOR_PRIMARY_TABLE
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen for deletes on the given table/datasource which should be the primary/main table of this query. If a delete comes in for this table, then we will only remove the records from the ViewFoundSet that do have this primary key in its value. So no need to do a full query. So this will only work if the query shows order_lines for the order_lines table, not for the products table that is joined to get the product_name. Only 1 of the 2 monitors for deletes should be registered for a table/datasource.
This constants needs to have the pk's selected for the given datasource (should be in the results)
Type Number
MONITOR_INSERT
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen for inserts on the given table/datasource. This will always result in a full query to detect changes whenever an insert on that table happens.
Type Number
MONITOR_JOIN_CONDITIONS
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen also for column changes of the given table/datasource in the join statement - like order_lines.productid that has a join to orders and is displaying the productname. If a change in such a join condition (like order_lines.productid in the sample above) is seen then the query will be fired again to detect changes.
Type Number
MONITOR_WHERE_CONDITIONS
Constant for the flags in #enableDatabroadcastFor(QBTableClause,int) to listen also for column changes of the given table/datasource that are used in the where statement - like order_lines.unit_price > 100. If a change is seen on that datasource on such a column used in the where a full query will be fired again to detect changes.
Type Number
VIEW_FOUNDSET
Type String
Properties Detailed
multiSelect
Returns true if this foundset is in multiselect mode and false if it's in single-select mode.
Type Boolean true if this foundset is in multiselect mode and false if it's in single-select mode.
Methods Detailed
dispose()
Returns: Boolean boolean foundset was disposed
Sample
duplicateFoundSet()
Get a duplicate of the viewfoundset. This is a full copy of the view foundset.
Returns: JSFoundSet foundset duplicate.
Sample
enableDatabroadcastFor(queryTable)
Databroadcast can be enabled per select table of a query, the select table can be the main QBSelect or on of it QBJoins By default this monitors only the column values that are in the result of the QBSelect, you can only enable this default monitoring for a table if for that table also the PK is selected in the results.
you can use #enableDatabroadcastFor(QBTableClause,int) to specify what should be monitored more besides pure column values per pk. Those have impact on performance because for the most part if we see a hit then a full query is done to see if there are changes.
Parameters
QBTableClause queryTable The QBSelect or QBJoin of a full query where this foundset should listen for data changes.
Returns: void
Sample
enableDatabroadcastFor(queryTableclause, flags)
Parameters
QBTableClause queryTableclause The QBSelect or QBJoin of a full query where this foundset should listen for data changes.
Number flags One or more of the ViewFoundSet.XXX flags added to each other.
Returns: void
Sample
forEach(callback)
Iterates over the records of a foundset taking into account inserts and deletes that may happen at the same time. It will dynamically load all records in the foundset (using Servoy lazy loading mechanism). If callback function returns a non null value the traversal will be stopped and that value is returned. If no value is returned all records of the foundset will be traversed. Foundset modifications( like sort, omit...) cannot be performed in the callback function. If foundset is modified an exception will be thrown. This exception will also happen if a refresh happens because of a rollback call for records on this datasource when iterating. When an exception is thrown from the callback function, the iteration over the foundset will be stopped.
Parameters
Function callback The callback function to be called for each loaded record in the foundset. Can receive three parameters: the record to be processed, the index of the record in the foundset, and the foundset that is traversed.
Returns: Object Object the return value of the callback
Sample
forEach(callback, thisObject)
Iterates over the records of a foundset taking into account inserts and deletes that may happen at the same time. It will dynamically load all records in the foundset (using Servoy lazy loading mechanism). If callback function returns a non null value the traversal will be stopped and that value is returned. If no value is returned all records of the foundset will be traversed. Foundset modifications( like sort, omit...) cannot be performed in the callback function. If foundset is modified an exception will be thrown. This exception will also happen if a refresh happens because of a rollback call for records on this datasource when iterating. When an exception is thrown from the callback function, the iteration over the foundset will be stopped.
Parameters
Function callback The callback function to be called for each loaded record in the foundset. Can receive three parameters: the record to be processed, the index of the record in the foundset, and the foundset that is traversed.
Object thisObject What the this object should be in the callback function (default it is the foundset)
Returns: Object Object the return value of the callback
Sample
getCurrentSort()
Get the last sort columns that were set using viewfoundset sort api.s
Returns: String String sort columns
Sample
getDataSource()
Returns the datasource (view:name) for this ViewFoundSet.
Returns: String
Sample
getEditedRecords()
Get the edited records of this view foundset.
Returns: Array an array of edited records
Sample
getFailedRecords()
Get the records which could not be saved.
Returns: Array an array of failed records
getName()
Get foundset name. If foundset is not named foundset will return null.
Returns: String name.
Sample
getQuery()
Get the cloned query that created this ViewFoundSset (modifying this QBSelect will not change the foundset). The ViewFoundSets main query can't be altered after creation; you need to make a new ViewFoundSet for that (it can have the same datasource name).
Returns: QBSelect query.
Sample
getRecord(index)
Get the ViewRecord object at the given index. Argument "index" is 1 based (so first record is 1).
Parameters
Number index record index (1 based).
Returns: JSRecord ViewRecord record.
Sample
getRecordByPk(pk)
Get the ViewRecord from the primary key values.
Parameters
Array pk pk values as array
Returns: JSRecord ViewRecord record.
Sample
getRecordIndex(record)
Get the record index. Will return -1 if the record can't be found.
Parameters
JSRecord record Record
Returns: Number int index.
Sample
getSQL()
Returns the internal SQL of the JSFoundset. Optionally, the foundset and table filter params can be excluded in the sql (includeFilters=false).
Returns: String String representing the sql of the JSFoundset.
Sample
getSQL(includeFilters)
Returns the internal SQL of the JSFoundset. Optionally, the foundset and table filter params can be excluded in the sql (includeFilters=false).
Parameters
Boolean includeFilters include the foundset and table filters [default true].
Returns: String String representing the sql of the JSFoundset.
Sample
getSQLParameters()
Returns the parameters for the internal SQL of the QBSelect. Table filters are on by default.
Returns: Array An Array with the sql parameter values.
Sample
getSQLParameters(includeFilters)
Returns the parameters for the internal SQL of the QBSelect. Table filters are on by default.
Parameters
Boolean includeFilters include the foundset and table filters [default true].
Returns: Array An Array with the sql parameter values.
Sample
getSelectedIndex()
Get the current record index of the foundset.
Returns: Number int current index (1-based)
Sample
getSelectedIndexes()
Get the indexes of the selected records. When the foundset is in multiSelect mode (see property multiSelect), a selection can consist of more than one index.
Returns: Array Array current indexes (1-based)
Sample
getSelectedRecord()
Returns: JSRecord
getSelectedRecords()
Get the selected records. When the viewfoundset is in multiSelect mode (see property multiSelect), selection can be a more than 1 record.
Returns: Array Array current records.
Sample
getSize()
Get the number of records in this viewfoundset. This is the number of records loaded, note that when looping over a foundset, size() may increase as more records are loaded.
Returns: Number int current size.
Sample
hasRecordChanges()
Check whether the foundset has record changes.
Returns: Boolean true if the foundset has any edited records, false otherwise
hasRecords()
Returns true if the viewfoundset has records.
Returns: Boolean true if the viewfoundset has records.
loadAllRecords()
This will reload the current set of ViewRecords in this foundset, resetting the chunk size back to the start (default 200). All edited records will be discarded! So this can be seen as a full clean up of this ViewFoundSet.
Returns: void
revertEditedRecords()
Revert changes of all unsaved view records of the view foundset.
Returns: void
revertEditedRecords(rec)
Revert changes of the provided view records.
Parameters
Array rec an array of view records
Returns: void
save()
Saves all records in the view foundset that have changes. You can only save columns from a table if the pks of that table are also selected by the view foundset's query.
Returns: Boolean true if the save was successfull, false if not and then the record will hav the exception set.
save(record)
Saved a specific record of this foundset. You can only save columns from a table if also the pk is selected of that table
Parameters
JSRecord record ;
Returns: Boolean true if the save was successfull, false if not and then the record will hav the exception set.
setSelectedIndex(index)
Set the current record index.
Parameters
Number index index to set (1-based)
Returns: void
Sample
setSelectedIndexes(indexes)
Set the selected records indexes.
Parameters
Array indexes An array with indexes to set.
Returns: void
Sample