# JSBaseSQLFoundset ## Overview `JSBaseSQLFoundSet` is the foundational class for SQL-based foundsets, including `JSFoundSet` and `ViewFoundSet`. It provides functionality to handle SQL-driven data operations within Servoy environments. This class extends `JSFoundSet`, enabling inheritance of its capabilities and adding specific methods for SQL-based operations. ## Features The `JSBaseSQLFoundSet` supports multiselect mode, allowing multiple records to be selected simultaneously, making it suitable for batch operations. It includes mechanisms to iterate over records using the `forEach` method, which dynamically loads records while accounting for concurrent inserts and deletes, ensuring data consistency during operations. Developers can retrieve the internal SQL used by the foundset through the `getSQL` method, along with parameters via `getSQLParameters`. Options are available to include or exclude table filters from the returned SQL and parameters. Additional capabilities include sorting records with `sort`, duplicating the foundset using `duplicateFoundSet`, saving changes with `save`, and reverting unsaved edits using `revertEditedRecords`. ## **Extends** [JSFoundSet](/reference/servoycore/dev-api/database-manager/jsfoundset.md) ## Properties Summarized | Type | Name | Summary | | ---------------------------------------------------------- | --------------------------- | ------- | | [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) | [multiSelect](#multiselect) | | ## Methods Summarized | Type | Name | Summary | | ------------------------------------------------------------------------------------ | -------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------- | | [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) | [dispose()](#dispose) | | | [JSFoundSet](/reference/servoycore/dev-api/database-manager/jsfoundset.md) | [duplicateFoundSet()](#duplicatefoundset) | | | [Object](/reference/servoycore/dev-api/js-lib/object.md) | [forEach(callback)](#foreach-callback) | Iterates over the records of a foundset taking into account inserts and deletes that may happen at the same time. | | [Object](/reference/servoycore/dev-api/js-lib/object.md) | [forEach(callback, thisObject)](#foreach-callback-thisobject) | Iterates over the records of a foundset taking into account inserts and deletes that may happen at the same time. | | [String](/reference/servoycore/dev-api/js-lib/string.md) | [getCurrentSort()](#getcurrentsort) | | | [String](/reference/servoycore/dev-api/js-lib/string.md) | [getDataSource()](#getdatasource) | | | [String](/reference/servoycore/dev-api/js-lib/string.md) | [getName()](#getname) | | | [JSBaseSQLRecord](/reference/servoycore/dev-api/database-manager/jsbasesqlrecord.md) | [getRecordByPk(pk)](#getrecordbypk-pk) | | | [Number](/reference/servoycore/dev-api/js-lib/number.md) | [getRecordIndex(record)](#getrecordindex-record) | Get the record index. | | [String](/reference/servoycore/dev-api/js-lib/string.md) | [getSQL()](#getsql) | Returns the internal SQL of the JSFoundset. | | [String](/reference/servoycore/dev-api/js-lib/string.md) | [getSQL(includeFilters)](#getsql-includefilters) | Returns the internal SQL of the JSFoundset. | | [Array](/reference/servoycore/dev-api/js-lib/array.md) | [getSQLParameters()](#getsqlparameters) | Returns the parameters for the internal SQL of the QBSelect. | | [Array](/reference/servoycore/dev-api/js-lib/array.md) | [getSQLParameters(includeFilters)](#getsqlparameters-includefilters) | Returns the parameters for the internal SQL of the QBSelect. | | [Number](/reference/servoycore/dev-api/js-lib/number.md) | [getSelectedIndex()](#getselectedindex) | Get the current record index of the foundset. | | [Array](/reference/servoycore/dev-api/js-lib/array.md) | [getSelectedIndexes()](#getselectedindexes) | Get the indexes of the selected records. | | [Array](/reference/servoycore/dev-api/js-lib/array.md) | [getSelectedRecords()](#getselectedrecords) | | | [Number](/reference/servoycore/dev-api/js-lib/number.md) | [getSize()](#getsize) | | | [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) | [loadAllRecords()](#loadallrecords) | | | void | [revertEditedRecords()](#reverteditedrecords) | | | [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) | [save()](#save) | | | void | [setSelectedIndex(index)](#setselectedindex-index) | Set the current record index. | | void | [setSelectedIndexes(indexes)](#setselectedindexes-indexes) | Set the selected records indexes. | | void | [sort(sortString)](#sort-sortstring) | | | void | [sort(sortString, defer)](#sort-sortstring-defer) | | | void | [sort(recordComparisonFunction)](#sort-recordcomparisonfunction) | | ## Properties Detailed ### multiSelect **Type**\ [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) true if the foundset is in multi-select mode; false otherwise. ## Methods Detailed ### dispose() **Returns:** [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) true if the foundset was successfully disposed; false otherwise. ### duplicateFoundSet() **Returns:** [JSFoundSet](/reference/servoycore/dev-api/database-manager/jsfoundset.md) a new instance of the duplicated foundset. ### 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](/reference/servoycore/dev-api/js-lib/function.md) **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](/reference/servoycore/dev-api/js-lib/object.md) Object the return value of the callback **Sample** ```js foundset.forEach(function(record,recordIndex,foundset) { //handle the record here }); ``` ### 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](/reference/servoycore/dev-api/js-lib/function.md) **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](/reference/servoycore/dev-api/js-lib/object.md) **thisObject** What the this object should be in the callback function (default it is the foundset) **Returns:** [Object](/reference/servoycore/dev-api/js-lib/object.md) Object the return value of the callback **Sample** ```js foundset.forEach(function(record,recordIndex,foundset) { //handle the record here }); ``` ### getCurrentSort() **Returns:** [String](/reference/servoycore/dev-api/js-lib/string.md) the current sort order as a string. ### getDataSource() **Returns:** [String](/reference/servoycore/dev-api/js-lib/string.md) the data source of the foundset as a string. ### getName() **Returns:** [String](/reference/servoycore/dev-api/js-lib/string.md) the name of the foundset. ### getRecordByPk(pk) **Parameters** * [Array](/reference/servoycore/dev-api/js-lib/array.md) **pk** ; **Returns:** [JSBaseSQLRecord](/reference/servoycore/dev-api/database-manager/jsbasesqlrecord.md) The record corresponding to the given primary key(s), or null if no matching record is found. ### getRecordIndex(record) Get the record index. Will return -1 if the record can't be found. **Parameters** * [JSBaseRecord](/reference/servoycore/dev-api/database-manager/jsbaserecord.md) **record** Record **Returns:** [Number](/reference/servoycore/dev-api/js-lib/number.md) int index. **Sample** ```js var index = foundset.getRecordIndex(record); ``` ### 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](/reference/servoycore/dev-api/js-lib/string.md) String representing the sql of the JSFoundset. **Sample** ```js var sql = foundset.getSQL(true) ``` ### 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](/reference/servoycore/dev-api/js-lib/boolean.md) **includeFilters** include the foundset and table filters \[default true]. **Returns:** [String](/reference/servoycore/dev-api/js-lib/string.md) String representing the sql of the JSFoundset. **Sample** ```js var sql = foundset.getSQL(true) ``` ### getSQLParameters() Returns the parameters for the internal SQL of the QBSelect.\ Table filters are on by default. **Returns:** [Array](/reference/servoycore/dev-api/js-lib/array.md) An Array with the sql parameter values. **Sample** ```js var parameters = foundset.getSQLParameters(true) ``` ### getSQLParameters(includeFilters) Returns the parameters for the internal SQL of the QBSelect.\ Table filters are on by default. **Parameters** * [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) **includeFilters** include the foundset and table filters \[default true]. **Returns:** [Array](/reference/servoycore/dev-api/js-lib/array.md) An Array with the sql parameter values. **Sample** ```js var parameters = foundset.getSQLParameters(true) ``` ### getSelectedIndex() Get the current record index of the foundset. **Returns:** [Number](/reference/servoycore/dev-api/js-lib/number.md) int current index (1-based) **Sample** ```js //gets the current record index in the current foundset var current = foundset.getSelectedIndex(); //sets the next record in the foundset foundset.setSelectedIndex(current+1); ``` ### 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](/reference/servoycore/dev-api/js-lib/array.md) Array current indexes (1-based) **Sample** ```js // modify selection to the first selected item and the following row only var current = foundset.getSelectedIndexes(); if (current.length > 1) { var newSelection = new Array(); newSelection[0] = current[0]; // first current selection newSelection[1] = current[0] + 1; // and the next row foundset.setSelectedIndexes(newSelection); } ``` ### getSelectedRecords() **Returns:** [Array](/reference/servoycore/dev-api/js-lib/array.md) an array of currently selected records in the foundset. ### getSize() **Returns:** [Number](/reference/servoycore/dev-api/js-lib/number.md) the number of records in the foundset. ### loadAllRecords() **Returns:** [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) true if all records are successfully loaded; false otherwise. ### revertEditedRecords() **Returns:** void ### save() **Returns:** [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) true if the changes are successfully saved; false otherwise. ### setSelectedIndex(index) Set the current record index. **Parameters** * [Number](/reference/servoycore/dev-api/js-lib/number.md) **index** index to set (1-based) **Returns:** void **Sample** ```js //gets the current record index in the current foundset var current = foundset.getSelectedIndex(); //sets the next record in the foundset foundset.setSelectedIndex(current+1); ``` ### setSelectedIndexes(indexes) Set the selected records indexes. **Parameters** * [Array](/reference/servoycore/dev-api/js-lib/array.md) **indexes** An array with indexes to set. **Returns:** void **Sample** ```js // modify selection to the first selected item and the following row only var current = foundset.getSelectedIndexes(); if (current.length > 1) { var newSelection = new Array(); newSelection[0] = current[0]; // first current selection newSelection[1] = current[0] + 1; // and the next row foundset.setSelectedIndexes(newSelection); } ``` ### sort(sortString) **Parameters** * [String](/reference/servoycore/dev-api/js-lib/string.md) **sortString** ; **Returns:** void ### sort(sortString, defer) **Parameters** * [String](/reference/servoycore/dev-api/js-lib/string.md) **sortString** ; * [Boolean](/reference/servoycore/dev-api/js-lib/boolean.md) **defer** ; **Returns:** void ### sort(recordComparisonFunction) **Parameters** * [Function](/reference/servoycore/dev-api/js-lib/function.md) **recordComparisonFunction** ; **Returns:** void *** --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.servoy.com/reference/servoycore/dev-api/database-manager/jsbasesqlfoundset.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.