JSFoundSet

Overview

JSFoundSet is the data model for various UI components or forms that link directly (both ways) to a datasource (db table, view, in-mem...).

It eases the read-from-db/datasource and write-to-db/datasource, editing data, data input validations and so on. It's API supports common tasks like sorting, query based loading, working through relations etc. It will automatically handle loading of data in batches from the datasource and supports lazy loading/partial loading in the UI as well (if the UI components themselves (lists / tables / trees etc.) support that).

Properties Summarized

Type	Name	Summary
Array	alldataproviders	Get all dataproviders of the foundset.
Boolean	multiSelect	Get or set the multiSelect flag of the foundset.

Methods Summarized

Type	Name	Summary
Boolean	addFoundSetFilterParam(query)	Add a filter parameter that is permanent per user session to limit a specified foundset of records.
Boolean	addFoundSetFilterParam(query, name)	Add a filter parameter that is permanent per user session to limit a specified foundset of records.
Boolean	addFoundSetFilterParam(dataprovider, operator, value)	Add a filter parameter that is permanent per user session to limit a specified foundset of records.
Boolean	addFoundSetFilterParam(dataprovider, operator, value, name)	Add a filter parameter that is permanent per user session to limit a specified foundset of records.
void	clear()	Clear the foundset.
JSRecord	createRecord()	Create a new record on top of the foundset and change selection to it.
JSRecord	createRecord(onTop)	Create a new record in the foundset.
JSRecord	createRecord(onTop, changeSelection)	Create a new record in the foundset.
JSRecord	createRecord(index)	Create a new record in the foundset and change selection to it at specified index.
JSRecord	createRecord(index, changeSelection)	Create a new record in the foundset.
JSTableFilter	createTableFilterParam(dataprovider, operator, value)	Create a table filter that can be applied to the foundset.
Boolean	deleteAllRecords()	Delete all records in foundset, resulting in empty foundset.
Boolean	deleteRecord()	Delete currently selected record(s).
Boolean	deleteRecord(record)	Delete record from foundset.
Boolean	deleteRecord(index)	Delete record with the given index.
Boolean	dispose()	Dispose a foundset from memory when foundset is no longer needed.
JSFoundSet	duplicateFoundSet()	Get a duplicate of the foundset.
Number	duplicateRecord()	Duplicate current record, change selection to new record, place on top.
Number	duplicateRecord(onTop)	Duplicate selected record, change selection to new record.
Number	duplicateRecord(onTop, changeSelection)	Duplicate selected record.
Number	duplicateRecord(index)	Duplicate record at index in the foundset, change selection to new record, place on top.
Number	duplicateRecord(index, onTop)	Duplicate record at index in the foundset, change selection to new record.
Number	duplicateRecord(index, onTop, changeSelection)	Duplicate record at index in the foundset.
Number	duplicateRecord(index, location)	Duplicate record at index in the foundset, change selection to new record.
Number	duplicateRecord(index, location, changeSelection)	Duplicate record at index in the foundset.
Boolean	find()	Set the foundset in find mode.
Object	forEach(callback)	Iterates over the records of a foundset taking into account inserts and deletes that may happen at the same time.
Object	forEach(callback, thisObject)	Iterates over the records of a foundset taking into account inserts and deletes that may happen at the same time.
String	getCurrentSort()	Get the current sort columns.
Object	getDataProviderValue(dataProviderID)	Get a value based on a dataprovider name.
String	getDataSource()	Get the datasource used.
Array	getFoundSetFilterParams()	Get the list of previously defined foundset filters.
Array	getFoundSetFilterParams(filterName)	Get a previously defined foundset filter, using its given name.
String	getName()	Get foundset name.
JSDataSet	getOmittedPKs()	Returns a JSDataSet with the PKs omitted on this foundset If no PKs have been omitted, an empty JSDataSet will be returned
Array	getParentRecords()	Gets the parent records when called on a related foundset.
QBSelect	getQuery()	Get the query that the foundset is currently using (as a clone; modifying this QBSelect will not automatically change the foundset).
QBSelect	getQuery(includeFilters)	Get the query that the foundset is currently using (as a clone; modifying this QBSelect will not automatically change the foundset).
JSRecord	getRecord(index)	Get the record object at the given index.
JSRecord	getRecordByPk(pk)	Get the loaded record based on its primary key values.
Number	getRecordIndex(record)	Get the record index.
String	getRelationName()	Gets the relation name (null if not a related foundset).
String	getSQL()	Returns the internal SQL of the JSFoundset.
String	getSQL(includeFilters)	Returns the internal SQL of the JSFoundset.
Array	getSQLParameters()	Returns the parameters for the internal SQL of the QBSelect.
Array	getSQLParameters(includeFilters)	Returns the parameters for the internal SQL of the QBSelect.
Number	getSelectedIndex()	Get the current record index of the foundset.
Array	getSelectedIndexes()	Get the indexes of the selected records.
JSRecord	getSelectedRecord()	Get the selected record.
Array	getSelectedRecords()	Get the selected records.
Number	getSize()	Get the number of records in this foundset.
Boolean	hasConditions()	Check wether the foundset has any conditions from a previous find action.
void	invertRecords()	Invert the foundset against all rows of the current table.
Boolean	isCleared()	Returns a boolean if this foundset is in a cleared state (has the clear condition that is added by a clear() call)
Boolean	isDisposed()	Check if the foundset was disposed.
Boolean	isInFind()	Check if this foundset is in find mode.
Boolean	loadAllRecords()	Loads all accessible records from the datasource into the foundset.
Boolean	loadOmittedRecords()	Loads the records that are currently omitted as a foundset.
Boolean	loadRecords()	Reloads all last (related) records again, if, for example, after search in tabpanel.
Boolean	loadRecords(foundset)	Copies foundset data from another foundset.
Boolean	loadRecords(dataset)	Loads a primary key dataset, will remove related sort.
Boolean	loadRecords(querybuilder)	Loads records into form foundset based on a query builder object (also known as 'Form by query').
Boolean	loadRecords(uuidpk)	Loads a single record by primary key, will remove related sort.
Boolean	loadRecords(numberpk)	Loads a single record by primary key, will remove related sort.
Boolean	loadRecords(queryString)	Loads records into form foundset based on a query (also known as 'Form by query').
Boolean	loadRecords(queryString, argumentsArray)	Loads records into form foundset based on a query (also known as 'Form by query').
Number	newRecord()	Create a new record on top of the foundset and change selection to it.
Number	newRecord(onTop)	Create a new record in the foundset and change selection to it.
Number	newRecord(onTop, changeSelection)	Create a new record in the foundset.
Number	newRecord(index)	Create a new record in the foundset and change selection to it.
Number	newRecord(index, changeSelection)	Create a new record in the foundset.
Boolean	omitRecord()	Omit selected record(s) (add it to omit records list), to be shown with loadOmittedRecords.
Boolean	omitRecord(IJSRecord)	Omit record sent as parameter (add it to omit records list), to be shown with loadOmittedRecords.
Boolean	omitRecord(index)	Omit record under the given index (add it to omit records list), to be shown with loadOmittedRecords.
void	reloadWithFilters()	Reloads all last records again with the filters applied.
void	relookup()	Perform a relookup for the currently selected records Lookups are defined in the dataprovider (columns) auto-enter setting and are normally performed over a relation upon record creation.
void	relookup(index)	Perform a relookup for the record under the given index Lookups are defined in the dataprovider (columns) auto-enter setting and are normally performed over a relation upon record creation.
Boolean	removeFoundSetFilterParam(name)	Remove a named foundset filter.
Number	search()	Start the database search and use the results, returns the number of records, make sure you did "find" function first.
Number	search(clearLastResults)	Start the database search and use the results, returns the number of records, make sure you did "find" function first.
Number	search(clearLastResults, reduceSearch)	Start the database search and use the results, returns the number of records, make sure you did "find" function first.
Boolean	selectRecord(pkid1, pkid2, pkidn)	Select the record based on pk data.
void	setDataProviderValue(dataProviderID, value)	Set a value based on a dataprovider name.
Boolean	setFoundSetFilters(filterName, tableFilters)	Set multiple foundset filters at the same time.
void	setSelectedIndex(index)	Set the current record index.
void	setSelectedIndexes(indexes)	Set the selected records indexes.
void	sort(sortString)	Sorts the foundset based on the given sort string.
void	sort(sortString, defer)	Sorts the foundset based on the given sort string.
void	sort(recordComparisonFunction)	Sorts the foundset based on the given record comparator function.
JSFoundSet	unrelate()	Create a new unrelated foundset that is a copy of the current foundset.

Properties Detailed

alldataproviders

Get all dataproviders of the foundset.

Type Array

Sample

var dataprovidersNames = alldataproviders;
application.output("This foundset has " + dataprovidersNames.length + " data providers.")
for (var i=0; i<dataprovidersNames.length; i++)
	application.output(dataprovidersNames[i]);

multiSelect

Get or set the multiSelect flag of the foundset.

Type Boolean

Sample

// allow user to select multiple rows.
foundset.multiSelect = true;

Methods Detailed

addFoundSetFilterParam(query)

Add a filter parameter that is permanent per user session to limit a specified foundset of records.

Filters on tables touched in the query will not be applied to the query filter. For example, when a table filter exists on the order_details table, a query filter with a join from orders to order_details will be applied to the foundset, but the filter condition on the orders_details table will not be included.

Use clear(), reloadWithFilters(), loadRecords() or loadAllRecords() to make the filter effective. Multiple filters can be added to the same dataprovider, they will all be applied.

Parameters

• QBSelect query condition to filter on.

Returns: Boolean true if adding the filter succeeded, false otherwise.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
   query.or.add(
            query.columns.shipcity.eq('Amersfoort'))
   .add(    query.columns.shipcity.eq('Amsterdam')));

var success = foundset.addFoundSetFilterParam(query, 'cityFilter'); // possible to add multiple
// Named filters can be removed using foundset.removeFoundSetFilterParam(filterName)

foundset.loadAllRecords(); // to make param(s) effective

addFoundSetFilterParam(query, name)

Add a filter parameter that is permanent per user session to limit a specified foundset of records.

Filters on tables touched in the query will not be applied to the query filter. For example, when a table filter exists on the order_details table, a query filter with a join from orders to order_details will be applied to the foundset, but the filter condition on the orders_details table will not be included.

Use clear(), reloadWithFilters(), loadRecords() or loadAllRecords() to make the filter effective. The filter is removed again using removeFoundSetFilterParam(name).

The table of the query has to be the same as the foundset table.

Parameters

• QBSelect query condition to filter on.

• Object name String name, used to remove the filter again.

Returns: Boolean true if adding the filter succeeded, false otherwise.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(
   query.or.add(
            query.columns.shipcity.eq('Amersfoort'))
   .add(    query.columns.shipcity.eq('Amsterdam')));

var success = foundset.addFoundSetFilterParam(query, 'cityFilter'); // possible to add multiple
// Named filters can be removed using foundset.removeFoundSetFilterParam(filterName)

foundset.loadAllRecords(); // to make param(s) effective

addFoundSetFilterParam(dataprovider, operator, value)

Add a filter parameter that is permanent per user session to limit a specified foundset of records. Use clear(), reloadWithFilters(), loadRecords() or loadAllRecords() to make the filter effective. Multiple filters can be added to the same dataprovider, they will all be applied.

Parameters

• String dataprovider String column to filter on.

• String operator String operator: =, <, >, >=, <=, !=, (NOT) LIKE, (NOT) IN, (NOT) BETWEEN and IS (NOT) NULL optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null), prefix with "sql:" to allow the value to be interpreted as a custom query.

• Object value Object filter value (for in array and between an array with 2 elements)

Returns: Boolean true if adding the filter succeeded, false otherwise.

Sample

var success = foundset.addFoundSetFilterParam('customerid', '=', 'BLONP', 'custFilter'); // possible to add multiple
// Named filters can be removed using foundset.removeFoundSetFilterParam(filterName)

// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var ok = foundset.addFoundSetFilterParam('companyname', '#^||=', 'servoy')

// Filters with in-conditions can be used with arrays or with custom queries:
success = foundset.addFoundSetFilterParam("productcode", "in", [120, 144, 200]);
success = foundset.addFoundSetFilterParam("city", "in", ["London", "Paris"]);
// use "sql:in" in stead of "in" to allow the value to be interpreted as a custom query
success = foundset.addFoundSetFilterParam("countrycode", "sql:in", "select country code from countries where region in ('Europe', 'Asia')");

foundset.loadAllRecords(); // to make param(s) effective

// see https://wiki.servoy.com/display/DOCS/Using+Table+Filters

addFoundSetFilterParam(dataprovider, operator, value, name)

Add a filter parameter that is permanent per user session to limit a specified foundset of records. Use clear(), reloadWithFilters(), loadRecords() or loadAllRecords() to make the filter effective. The filter is removed again using removeFoundSetFilterParam(name).

Parameters

• String dataprovider String column to filter on.

• String operator String operator: =, <, >, >=, <=, !=, (NOT) LIKE, (NOT) IN, (NOT) BETWEEN and IS (NOT) NULL optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null), prefix with "sql:" to allow the value to be interpreted as a custom query.

• Object value Object filter value (for in array and between an array with 2 elements)

• String name String name, used to remove the filter again.

Returns: Boolean true if adding the filter succeeded, false otherwise.

Sample

var success = foundset.addFoundSetFilterParam('customerid', '=', 'BLONP', 'custFilter'); // possible to add multiple
// Named filters can be removed using foundset.removeFoundSetFilterParam(filterName)

// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var ok = foundset.addFoundSetFilterParam('companyname', '#^||=', 'servoy')

// Filters with in-conditions can be used with arrays or with custom queries:
success = foundset.addFoundSetFilterParam("productcode", "in", [120, 144, 200]);
success = foundset.addFoundSetFilterParam("city", "in", ["London", "Paris"]);
// use "sql:in" in stead of "in" to allow the value to be interpreted as a custom query
success = foundset.addFoundSetFilterParam("countrycode", "sql:in", "select country code from countries where region in ('Europe', 'Asia')");

foundset.loadAllRecords(); // to make param(s) effective

// see https://wiki.servoy.com/display/DOCS/Using+Table+Filters

clear()

Clear the foundset.

This will set a special condition in the query that makes the query not return any results.

But if new Records are added to this foundset, then those records become the query pk set. So it will then behave the same as loadRecord(pkset) of a pkset of those new records.

You can query for this state in the isCleared() call so you can call loadRecords() to remove that cleared state if needed.

Returns: void

Sample

//Clear the foundset, including searches that may be on it
foundset.clear();

createRecord()

Create a new record on top of the foundset and change selection to it. Returns the new record or null if record was not created.

Returns: JSRecord IJSRecord the new record

Sample

var rec = foundset.createRecord(); // add as first record

createRecord(onTop)

Create a new record in the foundset. Returns the new record or null if the record can't be made.

Parameters

• Boolean onTop when true the new record is added as the topmost record.

Returns: JSRecord IJSRecord of new record.

Sample

var rec = foundset.createRecord(false); // add as last record, do change selection

createRecord(onTop, changeSelection)

Create a new record in the foundset. Returns the new record or null if the record can't be made.

Parameters

• Boolean onTop when true the new record is added as the topmost record; when false the record is added to the end, if all records are loaded, otherwise it will be added to the top

• Boolean changeSelection when true the selection is changed to the new record.

Returns: JSRecord IJSRecord of new record.

Sample

var rec = foundset.createRecord(false, false); // add as last record, do not change selection

createRecord(index)

Create a new record in the foundset and change selection to it at specified index. Returns the new record or null if the record can't be made.

Parameters

• Number index the new record is added at specified index (1-based).

Returns: JSRecord IJSRecord of new record.

Sample

var rec = foundset.createRecord(1); // add as first record, do change selection

createRecord(index, changeSelection)

Create a new record in the foundset. Returns the new record or null if the record can't be made.

Parameters

• Number index the new record is added at specified index (1-based).

• Boolean changeSelection when true the selection is changed to the new record.

Returns: JSRecord IJSRecord of new record.

Sample

var rec = foundset.createRecord(1, false); // add as first record, do not change selection

createTableFilterParam(dataprovider, operator, value)

Create a table filter that can be applied to the foundset. Multiple filters can be applied at the same time using foundset.setTableFilters().

Parameters

• String dataprovider A specified dataprovider column name.

• String operator One of "=, <, >, >=, <=, !=, LIKE, or IN" optionally augmented with modifiers "#" (ignore case) or "^||" (or-is-null), prefix with "sql:" to allow the value to be interpreted as a custom query.

• Object value The specified filter value.

Returns: JSTableFilter table filter or null when no filter could be created.

Sample

// filter on messages table where messagesid>10
var filter = foundset.createTableFilterParam('messagesid', '>', 10)

// some filters with in-conditions
var filter = foundset.createTableFilterParam('productcode', 'in', [120, 144, 200])
// use "sql:in" in stead of "in" to allow the value to be interpreted as a custom query
var filter = foundset.createTableFilterParam('countrycode', 'sql:in', 'select country code from countries where region = "Europe"')

// you can use modifiers in the operator as well, filter on companies where companyname is null or equals-ignore-case 'servoy'
var filter = foundset.createTableFilterParam('companyname', '#^||=', 'servoy')

// the value may be null, this will result in 'column is null' sql condition.
var filter = foundset.createTableFilterParam('verified', '=', null)

// apply multiple filters at the same time, previous filters with the same name are removed:
var success = foundset.setTableFilters('myfilters', [filter1, filter2])

deleteAllRecords()

Delete all records in foundset, resulting in empty foundset.

Returns: Boolean boolean true if all records could be deleted.

Sample

var success = foundset.deleteAllRecords();

deleteRecord()

Delete currently selected record(s). If the foundset is in multiselect mode, all selected records are deleted.

Returns: Boolean boolean true if all records could be deleted.

Sample

var success = foundset.deleteRecord();
//can return false incase of related foundset having records and orphans records are not allowed by the relation

deleteRecord(record)

Delete record from foundset.

Parameters

• JSRecord record The record to delete from the foundset.

Returns: Boolean boolean true if record could be deleted.

Sample

var success