Query API

The Platform Query API is available for all server-side formulas. To access these methods, use the rbv_api system object. The order of query parameters is set and cannot be changed.

Queries support the following syntax:

SELECT expression FROM object_name {WHERE expression} {GROUP BY
   expression} {ORDER BY expression}

The SELECT statement consists of the following parts:

• SELECT lists columns or expressions to be selected (mandatory)
• Use field integration names as SQL column names. You can use expressions such as COUNT(1). Selecting all columns with star (*) is not supported.
• FROM clause must consist of exactly one and only one object name (mandatory).
• WHERE clause includes a valid SQL expression to narrow the selection (optional). Use field integration names as SQL column names.
• GROUP BY clause includes an expression (typically a valid field name) to group selection (optional).
• ORDER BY clause includes a valid SQL expression to order selection (optional).

Query Limitations

Only data fields with stored input values (text, decimal, etc.) can be used in a SELECT expression. Dependent fields, such as formulas, cannot be used in query methods. A relationship (i.e. Lookup) field can be used, but will only take the ID of the first related record--not an array of all related records. Related fields can be used if they point to a data field.

The limitation preventing formulas from being used in the Query API can be easily bypassed in simple cases.

Consider this Formula: {!amount} * {!price}

Though you cannot use this Formula in a query directly, you can create a query such as:

SELECT SUM(amount*price)
    FROM order WHERE ...

For conditions involving date fields, you can use special tokens:

• TODAY for the current time
• WEEK for 12 AM of last Sunday
• MONTH for 12 AM of 1st day of the current month
• QUARTER for 12 AM of 1st day of the current quarter
• YEAR for 12 AM of 1st day of the current year
• CURR_USER for id of the currently logged in user

You can also use built-in functions:

• #YEAR(date) returns the date's year as an integer
• #MONTH(date) returns the date's month as an integer in the range of 1 - 12
• #DAY(date) returns the date's day of the month as an integer
• #ISO(literal_string) returns the date or date/time in ISO format, which can be used in a query. Examples: #ISO(2013-09-20T), #ISO(2013-09-20T20:05:01Z)
• #IF(expr, val1, val2) returns val1 if expr is evaluated to true, val2 otherwise.

The following query selects records created in the current year (starting from midnight January 1st) by current user:

SELECT name, id FROM order WHERE
    createdAt>=YEAR && createdBy=CURR_USER 

Query methods do not support arithmetic operations with data tokens like view filters do. For instance, MONTH-1 does not represent the first day of the previous month. You should use a query with parameters instead. If a query includes a Date or Date/Time column, an instance of the JavaScript Date class is returned. You can use any JavaScript Date class methods. See selectValue() for a code example.

You can also use single-quoted integration codes for picklists and status fields in a query WHERE clause. For example:

SELECT count(1) FROM order WHERE status='Q'

The following example reads records of where the user role integration code equals SALES:

SELECT loginName, role,
    email FROM USER WHERE role = 'SALES'

0bject and field integration names are case-sensitive, while other components of an SQL query are not. When an integration code is used to reference pick list item, status, or role in the query, the corresponding item must actually exist and be unique. This means that two picklists in the query object cannot use the same integration code. Otherwise, the integration code will not be resolved and will likely cause a query error.

If you query values of picklist fields, you will get numeric values corresponding to the ID of a selected item. If you wish to receive the integration code of the selected item instead, add the #code suffix to the name of the picklist field. Add the #value suffix to extract the display name of the selected item. The same syntax can be used for multi-select picklists, status fields, radio Buttons etc. For example:

• SELECT my_piclkist FROM invoice WHERE id={!id): returns the numeric ID of selected item
• SELECT my_piclkist#code FROM invoice WHERE id={!id): returns the integration code of selected item
• SELECT my_piclkist#value FROM invoice WHERE id={!id): returns the display name of selected item

Examples of valid queries for the User object:

• Select fields for users whose name starts with ‘M': SELECT id, name, updatedAt, updatedBy FROM USER WHERE name LIKE 'M%' ORDER BY name
• Count number of users whose name starts with ‘M': SELECT count(1) FROM USER WHERE name LIKE 'M%'
• Count number of users created or updated in current quarter: SELECT count(1) FROM USER WHERE updatedBy>=QUARTER
• Count number of approval records for current record (identified by {!id} token): SELECT count(1) FROM $APPROVAL WHERE approvedRecord={!id}
• Summarize amount*price expression for all records per category (picklist): SELECT sum(amount*price), category FROM sales GROUP BY category
• Extract records created between two dates:

  var d1 = new Date('2/2/2014');
  var d2 = new Date('4/2/2014');
  var arr = rbv_api.selectQuery(
  "SELECT id, name FROM a1 WHERE createdAt BETWEEN ? AND ?", 100, d1, d2);
  rbv_api.printArr(arr);
  

Example of an HTML report that displays the total number of users in the system:

<html><h2>
    Total users: #EVAL[ rbv_api.selectValue("SELECT COUNT(1) FROM USER") ]
</h2></html>

Although UI views do not make distinction between NULL values and 0 for numeric fields, the query API treats NULL values and 0 differently. To mimic UI behavior you need to explicitly filter NULL values. For example:

SELECT count(1) FROM invoice WHERE amount=0 OR amount IS NULL Important Limitations

Access control applies to query methods. See Security and Access Control for more details. Passwords cannot be retrieved through the query API due to security precautions.When applying these statements to lookup fields, the selectQuery() and selectValue() methods return the first related ID. If you need to retrieve all related IDs, use the getRelatedIds() method.

Some security precautions related to queries:

• 5000 characters or less
  Reviewers, does this mean the query expression itself must be less than 5K or the results?
• Cannot include separators: ; \n /
• Cannot include reserved words: ALTER, BEGIN, CALL, CASCADE, DELETE, DROP, DATABASE, GRANT, EXECUTE, INSERT, REVOKE, RENAME, SHUTDOWN, SCHEMA, UPDATE, LOGIN_NAME, PASSWORD

You can also use queries with parameters (see the method descriptions). Modifying the previous example to use parameter results in these statements:

count(1) FROM USER WHERE name LIKE ?SELECT

Then, supply the SQL query parameter 'M%'

You can perform JavaScript calculations and then pass results to a SQL query with parameters. This technique has a number of advantages over embedding variables directly into the text of a SQL query. To select ID of related record you can use query similar to this:

 select R1234 from
    invoice where id={!id}

However please keep in mind that this query will only work for single relationships (1:1 and N:1). For multiple relationships (1:N and N:N) result of this query is undetermined. You should use getRelatedIds() or a template loop.

Using the UI to Design a Query

To design and test your query you can use specially designed helper page. Click the Test Query button below formula text area to pop up that helper page. Enter your query starting with the SELECT keyword. Use merge tokens corresponding to all available fields and objects, as well as available SQL operands and built-in functions. To test your query, select a record of the current object type and click Test. The system will display the parsed query and results (up to 10 rows) or error message. When you finish your testing - copy the resulting SQL query into the body of your formula as the first parameter to one of the query methods.

The following shows an example query: