rbv_api.selectValue()
Purpose
Runs an SQL SELECT query and returns a single value. This simplified version of
rbv_api.selectQuery() is useful for calculating sum and performing other
operations on single values.
The SELECT query used in this method consists of the following parts:
- The SELECT statement expects columns or expressions to be selected (mandatory). Use the field integration names as SQL column names. You can use expressions such as COUNT(1). You cannot use * to retrieve all columns.
- The FROM clause must consist of exactly one object name (mandatory).
- The WHERE clause can include a valid SQL expression to narrow the selection (optional). Use field integration names as SQL column names.
- The ORDER BY clause can include a valid SQL expression to order the selection (optional). Use field integration names as SQL column names.
You can use special tokens in your queries such as:
- TODAY for current time
- WEEK for 12PM of last Sunday
- MONTH for 12PM of 1st day of current month
- QUARTER for 12PM of 1st day of current quarter
- YEAR for 12PM of 1st day of current year
- CURR_USER for id of currently logged in user
Object and Field names are case-sensitive, while other components of the SQL query are not.
Use #code suffix to fetch integration codes for picklist fields rather than IDs. See Adding business logic for more information.
Syntax
rbv_api.selectValue (query, arg1, arg2…)
Parameters
query
SQL SELECT query. See examples below and see Query API for limitations.
args
Variable number of parameters passed to query (optional)
Return value
Query result as a single value
Permissions required
View permission for the selected object type.
Example
The following example calculates the most recent update date for a set of related records.
It is used in a formula field of type Date:
rbv_api.selectValue("SELECT updatedAt FROM related_Object
WHERE R474176={!id} order by updatedAt desc");
The following example performs the same calculation, but uses an obj_id
parameter with the template token {!id} to obtain the ID of the current
record. This formula field will run the SELECT query and bring back most
recent value for the updatedAt field as a Date.
var obj_id = {!id};
var d = rbv_api.selectValue("SELECT updatedAt FROM related_Object
WHERE R474176=? order by updatedAt desc", obj_id);
The following example calculates the number of records updated in the previous month
(relative to the current date). Please note that the Verbose flag is set
for debugging and query parameters are used:
rbv_api.setVerbose(true);
var d1=new Date(rbv_api.firstDayOfMonth(0));
var d2=new Date(rbv_api.firstDayOfMonth(-1));
return rbv_api.selectNumber("SELECT COUNT(1)
FROM my_object where updatedAt>=? and updatedAt<?", d2, d1);
