1. Groovy Scripting Reference
  2. Using Query Helper Functions

Using Query Helper Functions

After defining the global functions in the following section, your business logic will be able to easily perform query tasks using a syntax that evokes the structure of a SQL select statement.

For example, to query the email, last name, and first name from the StaffMember business object's table where the job code is Sales Representative, you'll be able to write code like this:

def salesReps = adf.util.queryMaps(select: 'Email,LastName,FirstName',
                                     from: 'StaffMember',
                                    where: 'JobId = :JobCode',
                                  orderBy: 'LastName,FirstName',
                                    binds: [JobCode: 'SA_REP'])

Only the select and from named parameters are required. You specify the others only when you need to use them. The queryMaps() function returns a List of Map objects, each containing values for only the fields you've mentioned in the select parameter.

If you need to retrieve rows to update them as part of your business logic, then instead of queryMaps() use the queryRows() method as shown below. The result is a List of Row objects whose attributes are updateable and which will be validated and saved along with any other modified objects in the current transaction. Notice that since the result is a List we can use it directly in a for loop. Also note that we've included the Salary column in the select list that our code intends to update inside the loop.
// Update the salary of sales reps to increase it by 5 percent
for (curRep in adf.util.queryRows(select: 'Salary',
                                    from: 'StaffMember',
                                   where: 'JobId = :JobCode',
                                   binds: [JobCode: 'SA_REP'])) {
  // Round the salary to two digits after increasing by 5%
  curRep.Salary = (curRep.Salary * 1.05 as Double).round(2)
}

If your where clause will identify a single row, or if you only care to retrieve the first row of the result, then use the companion single-row-return functions:

  • Map queryMap(Map options) — returns first query result row as a Map, or null if no row was returned
  • Object queryRow(Map options) — returns first query result row, or null if no row was returned

In you only care to compute the total number of rows that would be returned by the query, use the queryCount() function. However, keep in mind that if your goal is to test for the existence of a single row it is more efficient to use queryMap() or queryRow() and test whether its return value is not null.

The complete list of named parameters the query methods support includes the following. All places that mention business object names and field names are case-sensitive.

  • select — comma-separated list of business object field names (String, required)
  • from — name of business object (String , required)
  • where — view criteria filter predicate (String, optionally referencing bind variables)
  • orderBy — comma-separated list of field names (String, optionally suffixed by " desc" for descending)
  • binds — pairs of bind variable names/values referenced in the where predicate (Map)
  • ignoreNullBinds — [Boolean] set to true to ignore where predicate elements involving null bind values

In addition to the user-defined by variables, your where clause can reference any of these built-in bind variable names:

  • SysUser — name of the currently-logged-in user, or anonymous otherwise (String)
  • SysToday — current date (Date)
  • SysNow — current date and time (Datetime)

For example, to query the list of currency exchange rates for the current date, you could write a query like this. Since the use case expects to return a single row and is not planning to modify the data, it's using queryMap() to retrieve the single Rate_c attribute that the code requires to compute the converted currency value.

def rate = adf.util.queryMap(
              select: 'Rate_c',
                from: 'ExchangeRate_c',
               where: 'From_c = :Base and To_c = :Other and Date_c = :SysToday',
               binds: [Base: 'GBP', Other: 'EUR'])
def convertedVal = sourceVal * rate.Rate_c
The helper methods queryRows(), queryMaps(), queryRow(), queryMap(), and queryCount() call the same core query() helper function in their implementation. This function centralizes:
  • Validating that the select and from required options are present
  • Creating a view object using newView() using the object name passed in the from parameter
  • Tokenizing the select list field names and passing them to selectAttributesBeforeQuery()
  • Defining any user-supplied bind variables if a where and binds parameter are supplied
  • Applying the view criteria filter expression if a where parameter is supplied
  • Setting the values of any system and/or user-supplied bind variables
  • Returning the view object

If your code needs to re-execute the same query multiple times with different values for its bind variables, use the core query() function that returns a view object. You can iterate the results yourself, reassign appropriate bind variables, and then execute the view object again without creating multiple, distinct view objects. This technique is important to avoid a runtime exception for using too many view objects in a single trigger or object function.

For example, consider the following code that iterates over an unknown number of uncleared transaction records and for each one queries the exchange rate into the target currency. It uses the queryMap() function inside a loop. This approach creates one new view object for each loop iteration. If the number of rows being iterated is unpredictably large, this technique can produce a runtime resource exception when it hits the upper limit on number of view objects that can be created in a single trigger or function.
// Will be updating the queried rows, so use queryRows()
for (txn in adf.util.queryRows(select:'Id,Cleared_c,Currency_c,Amount_c,Date_c', 
                                 from: 'Transaction_c',
                                where: "Cleared_c = 'N'")) {
  def rate = 1
  // If transaction currency is different than GBP, lookup historical
  // exchange rate for the date of the transaction to convert the 
  // transaction currency into GBP
  if (txn.Currency_c != 'GBP') {
    // NOT BEST PRACTICE: Using a query inside a loop!
    rate = adf.util.queryMap(
              select: 'Rate_c',
                from: 'ExchangeRate_c',
               where: "From_c = :Base and To_c = 'GBP' and Date_c = :ForDate",
               binds: [Base: txn.Currency_c, ForDate: txn.Date_c ])?.Rate_c
  }
  if (rate) {
    txn.Cleared_c = 'Y'
    // Multiply original txn amount by rate and round to 2 decimal places 
    txn.AmountInGBP_c = (txn.Amount_c * rate as Double).round(2)   
  }
}
Rather than using the query functions inside the body of a loop, the best practice is to call the core query() helper function. It returns a view object you can use over and over inside the loop. For each loop iteration, you set the bind variables to appropriate values and re-execute the query. This best-practice rewrite of the above routine is shown below.
// BEST PRACTICE: Create a reusable query for looking up the exchange rate using
// ~~~~~~~~~~~~~  bind variable values of the correct datatype. Inside the loop
// set the correct bind var values and execute the same view object over & over
def rateVO = adf.util.query(
                select: 'Rate_c',
                  from: 'ExchangeRate_c',
                 where: "From_c = :Base and To_c = 'GBP' and Date_c = :ForDate",
                 binds: [Base: 'XXX', ForDate: today() ])
// Will be updating the queried rows, so use queryRows()
for (txn in adf.util.queryRows(select:'Id,Cleared_c,Currency_c,Amount_c,Date_c', 
                                 from: 'Transaction_c',
                                where: "Cleared_c = 'N'")) {
  def rate = 1
  // If transaction currency is different than GBP, lookup historical
  // exchange rate for the date of the transaction to convert the 
  // transaction currency into GBP
  if (txn.Currency_c != 'GBP') {
    // Set any bind variables to new values for current loop iteration
    setBindVariable(rateVO,'Base',txn.Currency_c)
    setBindVariable(rateVO,'ForDate',txn.Date_c)
    // Execute the same view object with the new bind variable values
    rateVO.executeQuery()
    rate = rateVO.first()?.Rate_c
  }
  if (rate) {
    txn.Cleared_c = 'Y'
    // Multiply original txn amount by rate and round to 2 decimal places 
    txn.AmountInGBP_c = (txn.Amount_c * rate as Double).round(2)  
  }
}