JSFoundSet

Property Summary

Methods Summary

Properties Details

alldataproviders

Get all dataproviders of the foundset.

Returns 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.

Returns Boolean

Sample

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

Methods Details

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 = foundset.deleteRecord(rec);
//can return false incase of related foundset having records and orphans records are not allowed by the relation

deleteRecord(index)

Delete record with the given index.

Parameters Number index The index of the record to delete.

Returns Boolean boolean true if record could be deleted.

Sample

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

dispose()

Dispose a foundset from memory when foundset is no longer needed. Should be used to destroy separate foundsets (is an optimization for memory management). A related foundset or a foundset which is linked to visible forms/components cannot be disposed. Returns whether foundset was disposed or not.

Returns Boolean boolean foundset was disposed

Sample

foundset.dispose();

duplicateFoundSet()

Get a duplicate of the foundset. This is a full copy of the foundset (cached pks,records, relation, filters, search criteria, omitted records, selection).

Returns JSFoundSet foundset duplicate.

Sample

var dupFoundset = foundset.duplicateFoundSet();
foundset.find();
//search some fields
var count = foundset.search();
if (count == 0)
{
	plugins.dialogs.showWarningDialog('Alert', 'No records found','OK');
	foundset.loadRecords(dupFoundset);
}

duplicateRecord()

Duplicate current record, change selection to new record, place on top.

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(onTop)

Duplicate selected record, change selection to new record.

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

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(onTop, changeSelection)

Duplicate selected record.

Parameters Boolean onTop when true the new record is added as the topmost record. Boolean changeSelection when true the selection is changed to the duplicated record.

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(index)

Duplicate record at index in the foundset, change selection to new record, place on top.

Parameters Number index The index of the record to duplicate; defaults to currently selected index. Ignored if first given parameter is a boolean value.

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(index, onTop)

Duplicate record at index in the foundset, change selection to new record.

Parameters Number index The index of the record to duplicate; defaults to currently selected index. Ignored if first given parameter is a boolean value. Boolean onTop when true the new record is added as the topmost record.

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(index, onTop, changeSelection)

Duplicate record at index in the foundset.

Parameters Number index The index of the record to duplicate; defaults to currently selected index. Ignored if first given parameter is a boolean value. Boolean onTop when true the new record is added as the topmost record. Boolean changeSelection when true the selection is changed to the duplicated record.

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(index, location)

Duplicate record at index in the foundset, change selection to new record.

Parameters Number index The index of the record to duplicate; defaults to currently selected index. Ignored if first given parameter is a boolean value. Number location the new record is added at specified index

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

duplicateRecord(index, location, changeSelection)

Duplicate record at index in the foundset.

Parameters Number index The index of the record to duplicate; defaults to currently selected index. Ignored if first given parameter is a boolean value. Number location the new record is added at specified index Boolean changeSelection when true the selection is changed to the duplicated record.

Returns Number 0 if record was not created or the record index if it was created.

Sample

foundset.duplicateRecord();
foundset.duplicateRecord(false); //duplicate the current record, adds at bottom
foundset.duplicateRecord(1,2); //duplicate the first record as second record
//duplicates the record (record index 3), adds on top and selects the record
foundset.duplicateRecord(3,true,true);

find()

Set the foundset in find mode. (Start a find request), use the "search" function to perform/exit the find.

Before going into find mode, all unsaved records will be saved in the database. If this fails (due to validation failures or sql errors) or is not allowed (autosave off), the foundset will not go into find mode. Make sure the operator and the data (value) are part of the string passed to dataprovider (included inside a pair of quotation marks). Note: always make sure to check the result of the find() method.

When in find mode, columns can be assigned string expressions (including operators) that are evaluated as: General: c1||c2 (condition1 or condition2) c|format (apply format on condition like 'x|dd-MM-yyyy') !c (not condition) #c (modify condition, depends on column type) ^ (is null) ^= (is null or empty) <x (less than value x) >x (greater than value x) <=x (less than or equals value x) >=x (greater than or equals value x) x...y (between values x and y, including values) x (equals value x)

Number fields: =x (equals value x) ^= (is null or zero)

Date fields: #c (equals value x, entire day) now (equals now, date and or time) // (equals today) today (equals today)

Text fields: #c (case insensitive condition) = x (equals a space and 'x') ^= (is null or empty) %x% (contains 'x') %x_y% (contains 'x' followed by any char and 'y') % (contains char '%') _ (contains char '_')

Related columns can be assigned, they will result in related searches. For example, "employees_to_department.location_id = headoffice" finds all employees in the specified location).

Searching on related aggregates is supported. For example, "orders_to_details.total_amount = '>1000'" finds all orders with total order details amount more than 1000.

Arrays can be used for searching a number of values, this will result in an 'IN' condition that will be used in the search. The values are not restricted to strings but can be any type that matches the column type. For example, "record.department_id = [1, 33, 99]"

Multiple Find Records for Logical OR: It's important to note that when in Find Mode, a foundset will initially contain one record object. However, multiple record objects may be used to articulate search criteria. This has the effect that the criteria described in each record are concatenated by a SQL OR.

Returns Boolean true if the foundset is now in find mode, false otherwise.

Sample

if (foundset.find()) //find will fail if autosave is disabled and there are unsaved records
{
	columnTextDataProvider = 'a search value'
	// for numbers you have to make sure to format it correctly so that the decimal point is in your locales notation (. or ,)
	columnNumberDataProvider = '>' + utils.numberFormat(anumber, '####.00');
	columnDateDataProvider = '31-12-2010|dd-MM-yyyy'
	foundset.search()
}

// Multiple Find Records for Logical OR - example
// Find customers in the city of Berlin AND in the postal code 12209...
// OR customers in the city of San Francisco AND in the postal code 94117
if (foundset.find()) // Enter find mode    city = 'Berlin';
{
	city = 'Berlin';   // Assign a search criteria
	postalcode = '12209';
	foundset.newRecord();   // Create a new search record
	city = 'San Francisco';
 postalcode = '94117';
	foundset.search();      // Execute the query and load the records
}

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

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 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

foundset.forEach(function(record,recordIndex,foundset) {
 	//handle the record here
 });

getCurrentSort()

Get the current sort columns.

Returns String String sort columns

Sample

//reverse the current sort

//the original sort "companyName asc, companyContact desc"
//the inversed sort "companyName desc, companyContact asc"
var foundsetSort = foundset.getCurrentSort()
var sortColumns = foundsetSort.split(',')
var newFoundsetSort = ''
for(var i=0; i<sortColumns.length; i++)
{
	var currentSort = sortColumns[i]
	var sortType = currentSort.substring(currentSort.length-3)
	if(sortType.equalsIgnoreCase('asc'))
	{
		newFoundsetSort += currentSort.replace(' asc', ' desc')
	}
	else
	{
		newFoundsetSort += currentSort.replace(' desc', ' asc')
	}
	if(i != sortColumns.length - 1)
	{
		newFoundsetSort += ','
	}
}
foundset.sort(newFoundsetSort)

getDataProviderValue(dataProviderID)

Get a value based on a dataprovider name.

Parameters String dataProviderID data provider name

Returns Object Object value

Sample

var val = foundset.getDataProviderValue('contact_name');

getDataSource()

Get the datasource used. The datasource is an url that describes the data source.

Returns String String data source.

Sample

var dataSource = foundset.getDataSource();

getFoundSetFilterParams()

Get the list of previously defined foundset filters.

For column-based table filters, a row of 5 fields per filter are returned. The "columns" of a row from this array are: tablename, dataprovider, operator, value, filtername

For query-based filters, a row of 2 fields per filter are returned. The "columns" of a row from this array are: query, filtername

Returns Array Array of filter definitions.

Sample

var params = foundset.getFoundSetFilterParams()
for (var i = 0; params != null && i < params.length; i++)
{
 if (params[i].length() == 5) {
		application.output('FoundSet filter on table ' + params[i][0] + ': '+ params[i][1] + ' '+params[i][2] + ' '+params[i][3] + (params[i][4] == null ? ' [no name]' : ' ['+params[i][4]+']'))
	}
 if (params[i].length() == 2) {
		application.output('FoundSet filter with query ' + params[i][0]+ ': ' + (params[i][1] == null ? ' [no name]' : ' ['+params[i][1]+']'))
<