controller.loadRecords() method is used to load records to a form’s foundset.
This TIP will make you familiar with different flavors/ways of loading records by using controller.loadRecords() method.

controller.loadRecords() can be used in Three different flavors/ways. The syntax of the method is :

controller.loadRecords([foundset/pkdataset/query], [queryArgumentsArray])

You can load records to the foundset by passing a foundset(must be a related one), dataset or simply a SQL Query. Servoy will load the records accordingly.

Loading Records from a Foundset

You can load records by passing an foundset object as the first argument to the controller.loadRecords() method. The Foundset must be a related one, on the form, on which you are loading records. For example, let say, your form based on the ‘projects’ table and you want to the load all the projects that the current logged in user is related to. You can do that, just by passing the relation to the controller.loadRecords() method of the form based on ‘projects’ table.

// load all the related projects that the current user related to
controller.loadRecords(g_current_user_projects);

Loading Records from a Dataset

You can load records by passing a Dataset containing all the PKs to the controller.loadRecords() method.

// set up the SQL Query for selecting all the projects
// ordered by project title
var _query = "SELECT projects.project_id FROM projects \
ORDER BY projects.project_title ASC";

// load records according to the dataset
controller.loadRecords(_dataset);

Loading Records from a Query

You can load record by directly passing a SQL Query to the controller.loadRecords() method. You can even pass the parameters for your SQL Query as an argument.
Servoy will load the records according to the result of the SQL Query.

// get current date-time stamp
var _curDateTime = application.getServerTimeStamp();

// load all over due projects and ordered by project title
controller.loadRecords(_query, [_curDateTime]);

You need to take care of following points, while loading record by using SQL Query.

Your SQL Query must start with a SELECT statement

Your SQL Query must contain the FROM keyword.

Your SQL Query can not contain GROUP BY, HAVING or UNION clause.

The resulted columns of your SQL Query must be the Pks of the table, on which you are loading records.

All your columns in your SQL Query must be fully qualified like, ‘projects.project_id’.

Here, you may have noticed one thing, loading records from a Dataset and from an SQL Query is almost same. The Query has been executed and the records will be loaded according to the result, then what is the difference in between these two ways?

Loading records from a Dataset gives you the flexibility of limiting number of records loaded.

// execute the query; get the PKs
var _dataset = databaseManager.getDataSetByQuery(controller.getServerName()
, _query, null, 10000);

In the getDataSetByQuery() method, the last argument is used to limit the maximum number of records fetched as a result of the query. So, here, in the above example a max of 10,000 of records will be loaded to the form, not more than that.