Query Class Reference

Query against one record Query.selectId(table, id) Query.updateId(table, id, column, value) Query.deleteId(table, id) Query.insert(table, values)	Selecting multiple records Query.select(table, columns, where, orderby) Query.count(table, where) Query.options(table, where) Query.names(table, id) Query.search(table, search, columns, where)
Modifying multiple records Query.update(table, values, where) Query.deleteWhere(table, where)	Archiving & Restoring (Web Only) Query.selectDeleted(table, columns, where) Query.selectArchived(table, columns, where) Query.restoreId(table, id) Query.archiveId(table, id)

Overview

This class perfoms SQL queries on the local database tables

Query.select(table, columns, where, orderby)

Performs a SQL select query on a table, returns all the rows matching the where clause and ordered by orderby

table (string) is the name of the table in the format appid.tablename. If appid is ommitted it uses the currently loaded application id.
columns (string) defines the column names to be returned, separated by a ; column1;column2;column3;.... or use "*" to return all columns
where (string) is a contains a SQL where clause. A null or empty string will return all rows in the table
ordebry (string) is the SQL orderby clause, containing the column name and the optionla DESC modifier.
The return value is array of javascript objects, each object containing property matching the column name.

Config.tables["tasks"] = "id;name;status INTEGER;price REAL;date DATE";
...
var allTasks = Query.select("tasks", "id;name;status;date", null, "date DESC");
var openTasks = Query.select("tasks", "id;name;status;date", "status=0", "name");
var today = Date.now();
var overdueTasks = Query.select("tasks", "id;name;status;date", "status=0 AND date<{today}", "name");

Query.selectId(table, id)

Return one unique javascript object matching the primary id (string) for the table or null if none exists. It returns all columns values

Config.tables["mytable"] = "id;name;date DATE";
...
var lead = Query.selectId("mytable", id);
if (lead != null) {
    // you can use lead.id, lead.name, lead.date
}
...

Query.count(table, where)

Returns the number of records matching the where (string) clause for the table.

Query.insert(table, values)

Insert a new record into table and fill in its columns with the values javascript object.
Each property of the values object must corresponds to a column name. values MUST NOT BE NULL.
Returns the primary key id (string) of the inserted record.

Config.tables["leads"] = "id;name;date DATE"
...
var id = Query.insert("leads", {name:"John", date:Date.now()});
var id = Query.insert("leads", {});

Query.updateId(table, id, column, value)

Update one record, identified by its primary key id from table by setting the column name value.

var lead = Query.selectId("leads", leadid);
var onchange = "Query.updateId('leads',{leadid},this.id,this.value)";
List.addTextBox("name", "Lead Name", lead.name, onchange);

Query.update(table, values, where)

Update all records matching the where clause be settings their values using the values javascript object.

Query.deleteId(table, id)

Deletes one record identfied by its primary key id from table

Query.deleteId("leads", leadid);
History.back();

Query.deleteWhere(table, where)

Deletes all records matching the where clause from table;

Query.options(table, where)

Convenience method returning a string corrected formatted containing all id, name value pairs matching the where clause from table. The returned value is designed to be used with the List.addComboBox() or List.addComboBoxMulti() methods

Config.tables["groups"] = "id;name";
Config.tables["leads"] = "id;name;groupid";
....
var lead = Query.selectId("leads", leadid);
var onchange = "Query.updateId('leads',{lead.id},this.id,this.value)";
List.addComboBox("groupid", "Lead Group", lead.groupid, onchange, Query.options("groups"));

Query.names(table, id)

Convenience method returning the value of the name column macthing the records identified by the id primary key from table.

Config.tables["groups"] = "id;name";
Config.tables["leads"] = "id;name;groupid";
....
var lead = Query.selectId("leads", leadid);
List.addItemLabel("Lead Group", Query.names("groups", lead.id));

Query.search(table, search, columns, where)

Performs a full text search on the requested columns and returns all mathcing rows

table (string) is the name of the table in the format appid.tablename. If appid is ommitted it uses the currently loaded application id.
search (string) the search string to search
columns (string) defines the column names to be searched, separated by a ; column1;column2;column3
where (string) is a contains a SQL where clause to restrict the search
The return value is array of javascript objects

Config.tables["tasks"] = "id;name;status INTEGER;price REAL;date DATE";

Config.onsearch = "mySearchFunction";

function mySearchFunction(str) {
    var where = "status=1";
    var items = Query.search("tasks", str, "name;price", where);
    ....

}

Query.restoreId(table, id)

Restored one previously deleted record identfied by its primary key id from table.

Query.restoreId("leads", leadid);

Query.archiveId(table, id)

Archive one record identfied by its primary key id from table.

Query.archiveId("leads", leadid);