4Groovy Tips and Techniques

Groovy Tips and Techniques

This section provides a compendium of tips and techniques for getting the most out of Groovy in your application.

Accessing Current Date and Time from the Application Server

Oracle’s application development framework exposes functionality to your business object scripts through the predefined adf variable. For example, to reference the application server's current date use the following expression:

adf.currentDate

To reference the application server's current date including the current time, use the expression:

adf.currentDateTime
Note: This function is valid in any Groovy script specific to a particular business object. If necessary to pass the information into other contexts, you can pass its value as a parameter to a function call.

Accessing Current Date and Time from the Database

Oracle’s application development framework exposes functionality to your business object scripts through the predefined adf variable. For example, to reference the database's current date, use the following expression:

adf.currentDBDate

To reference the application server's current date including the current time, use the expression:

adf.currentDBDateTime
Note: This function is valid in any Groovy script specific to a particular business object. If necessary to pass the information into other contexts, you can pass its value as a parameter to a function call.

Understanding Additional Built-in Groovy Functions

This section explains a number of additional helper functions you can use in your scripts. Some provide a simple example as well. Use the Functions tab of the code editor palette to insert any of the built-in functions into your script.

Table Built-in Date Functions

Function

Description

today()

Returns: the current date, with no time

Return Type: Date

now()

The current date and time

Return Type: Timestamp

date(year,month,day)

Returns: a date, given the year, month, and day

Return Type: Date

Parameters:

  • year - a positive integer

  • month - a positive integer between 1 and 12

  • day - a positive integer between 1 and 31

Example: to return a date for February 8th, 1998, use date(1998,2,8)

dateTime(y,m,d,hr,min,sec)

Returns: a timestamp, given the year, month, day, hour, minute, and second

Return Type: Timestamp

Parameters:

  • year - a positive integer

  • month - a positive integer between 1 and 12

  • day - a positive integer between 1 and 31

  • hour - a positive integer between 0 and 23

  • minute - a positive integer between 0 and 59

  • second - a positive integer between 0 and 59

Example: to return a timestamp for February 8th, 1998, at 23:42:01, use dateTime(1998,2,8,23,42,1)

year(date)

Returns: the year of a given date

Return Type: Integer

Parameters:

  • date - date

Example: if curDate represents April 19th, 1996, then year(curDate) returns 1996.

month(date)

Returns: the month of a given date

Return Type: Integer

Parameters:

  • date - a date

Example: if curDate represents April 12th, 1962, then month(curDate) returns 4.

day(date)

Returns: the day for a given date

Return Type: Integer

Parameters:

  • date - a date

Example: if curDate represents July 15th, 1968, then day(curDate) returns 15.

Table Built-in String Functions

Function

Description

contains(s1,s2)

Returns: true, if string s1 contains string s2, false otherwise

Return Type: boolean

Parameters:

  • s1 - a string to search in

  • s2 - a string to search for

Example: if twitterName holds the value @steve, then contains(twitterName,'@') returns true.

endsWith(s1,s2)

Returns: true, if string s1 ends with string s2, false otherwise

Return Type: boolean

Parameters:

  • s1 - a string to search in

  • s2 - a string to search for

For example, if twitterName holds the value @steve, then endsWith(twitterName,'@') returns false.

find(s1,s2)

Returns: the integer position of the first character in string s1 where string s2 is found, or zero (0) if the string is not found

Return Type: Integer

Parameters:

  • s1 - a string to search in

  • s2 - a string to search for

Example: if twitterName holds the value @steve, then find(twitterName,'@') returns 1 and find(twitterName,'ev') returns 4.

left(s,len)

Returns: the first len characters of the string s

Return Type: String

Parameters:

  • s - a string

  • len - an integer number of characters to return

Example: if postcode holds the value 94549-5114, then left(postcode,5) returns 94549.

length(s)

Returns: the length of string s

Return Type: Integer

Parameters:

  • s - a string

Example: if name holds the value Julian Croissant, then len(name) returns 16.

lowerCase(s)

Returns: the string s with any uppercase letters converted to lowercase

Return Type: String

Parameters:

  • s - a string

Example: if sku holds the value 12345-10-WHT-XS, then lowerCase(sku) returns 12345-10-wht-xs.

right(s,len)

Returns: the last len characters of the string s

Return Type: String

Parameters:

  • s - a string

  • len - an integer number of characters to return

Example: if sku holds the value 12345-10-WHT-XS, then right(sku,2) returns XS.

startsWith(s1,s2)

Returns: true, if string s1 starts with s2, false otherwise

Return Type: boolean

Parameters:

  • s1 - a string to search in

  • s2 - a string to search for

Example: if twitterName holds the value @steve, then startsWith(twitterName,'@') returns true.

substringBefore(s1,s2)

Returns: the substring of s1 that precedes the first occurrence of s2, otherwise an empty string

Return Type: String

Parameters:

  • s1 - a string to search in

  • s2 - a string to search for

Examples: if sku holds the value 12345-10-WHT-XS, then substringBefore(sku,'-') returns the value 12345, substringBefore(sku,'12345') returns an empty string, and substringBefore(sku,'16-BLK') also returns an empty string.

substringAfter(s1,s2)

Returns: the substring of s1 that follows the first occurrence of s2. otherwise an empty string

Return Type: String

Parameters:

  • s1 - a string to search in

  • s2 - a string to search for

Example: if sku holds the value 12345-10-WHT-XS, then substringAfter(sku,'-') returns the value 10-WHT-XS, substringAfter(sku,'WHT-') returns the value XS, substringAfter(sku,'XS') returns an empty string, and substringAfter(sku,'BLK') also returns an empty string.

upperCase(s)

Returns: the string s with any lowercase letters converted to uppercase

Return Type: String

Parameters:

  • s - a string

Example: if sku holds the value 12345-10-Wht-xs, then upperCase(sku) returns 12345-10-WHT-XS.

Table Other Built-in Functions

Function

Description

newView(objectAPIName)

Returns: a ViewObject reserved for programmatic use, or null if not available.

Return Type: ViewObject

Parameters:

  • objectAPIName - the object API name whose rows you want to find, create, update, or remove

Example: newView('TroubleTicket') returns a new view object instance you can use to find, create, update, or delete TroubleTicket rows.

key(list)

Returns: a multi-valued key object for use in the ViewObject's findByKey() method.

Return Type: Key

Parameters:

  • list - a list of values for a multi-field key

Example: if a standard object has a two-field key, use key([101,'SAMBA'])

key(val)

Returns: a key object for use in the ViewObject's findByKey() method.

Return Type: Key

Parameters:

  • val - a value to use as the key field

Example: if a standard object has a single-field key, as all custom objects do, use key(123456789)

nvl(o1,o2)

Returns: the object o1 if it is not null, otherwise the object o2.

Return Type: Object

Parameters:

  • o1 - a value to use if not null

  • o2 - a value to use instead if o1 is null

Example: to calculate the sum of Salary and Commission fields that might be null, use nvl(Salary,0) + nvl(Commission,0)

Understanding Groovy's Null-Safe Comparison Operators

It's important to know that Groovy's comparison operators == and != handle nulls gracefully so you don't have to worry about protecting null values in equality or inequality comparisons. Furthermore, the >, >=, <, and <= operators are also designed to avoid null-related exceptions, however you need to be conscious of how Groovy treats null in these order-dependent comparisons. Effectively, a null value is "less than" any other non-null value in the natural ordering, so for example observe the following comparison results.

Table Examples of How null Is Less Than Everything

Left-Side Expression

Operator

Right-Side Expression

Comparison Result

'a'

>

null

true

'a'

<

null

false

100

>

null

true

100

<

null

false

-100

>

null

true

-100

<

null

false

now()

>

null

true

now()

<

null

false

now() - 7

>

null

true

now() - 7

<

null

false

If you want a comparison to treat a null-valued field with different semantics — for example, treating a null MaximumOverdraftAmount field as if it were zero (0) like a spreadsheet user might expect — then use the nvl() function as part of your comparison logic as shown in the following example:

// Change default comparison semantics for the MaximumOverdraftAmount custom field in
// case its value is null by using nvl() to treat null like zero (0)
if (nvl(MaximumOverdraftAmount,0) < -2000) {
  // do something for suspiciously large overdraft amount
} 

As illustrated by the table above, without the nvl() function in the comparison any MaximumOverdraftAmount value of null would always be less than -2000 — since by default null is less than everything.

Testing Whether a Field's Value Is Changed

You can test whether a field's value has changed in the current transaction by using the built-in isAttributeChanged() function. As shown in this example, it takes a single string argument that provides the name of the field whose changed status you want to evaluate:

if (isAttributeChanged('Status')) {
  // perform some logic here in light of the fact
  // that status has changed in this transaction
}

Avoiding Validation Threshold Errors By Conditionally Assigning Values

When you write scripts for validation rules that modify the values of fields in the current object, you must be aware of how this affects the object's so-called "validation cycle". Before allowing an object to be saved to the database, the application development framework ensures that its data passes all validation rules. The act of successfully running all defined validation rules results in the object's being marked as valid and allows the object to be saved along with all other valid objects that have been modified in the current transaction. If as part of executing a validation rule your script modifies the value of a field, this marks the object "dirty" again. This results in ADF's subjecting the object again to all of the defined validation rules to ensure that your new changes do not result in an invalid object. If the act of re-validating the object runs your scripts that modify the field values again, this process could result in a cycle that would appear to be an infinite loop. ADF avoids this possibility by imposing a limit of 10 validation cycles on any given object. If after 10 attempts at running all the rules the object still has not been able to be successfully validated due to the object's being continually modified by its validation logic, ADF will throw an exception complaining that you have exceeded the validation threshold:

Validation threshold limit reached. Invalid Entities still in cache

A simple way to avoid this from happening is to test the value of the field your script is about to assign and ensure that you perform the field assignment (or setAttribute() call to modify its value) only if the value you intend to assign is different from its current value. An example script employing this approach would look like this:

// Object-level validation rule on a PurchaseOrder object
// to derive the default purchasing rep based on a custom
// algorithm defined in an object function named 
// determinePurchasingRep() if both the Discount and NetDaysToPay
// fields have changed in the current transaction.
if (isAttributeChanged('Discount') && 
    isAttributeChanged('NetDaysToPay')) {
  def defaultRep = determinePurchasingRep()
  // If new defaultRep is not the current rep, assign it
  if (PurchasingRep != defaultRep) {
    PurchasingRep = defaultRep
  }
}
return true
Note: This example illustrates how to avoid a typical problem that can occur when using a validation rule to perform field derivations. The recommended trigger to use for such purposes would be the field-level "After Value Changed" trigger, or alternatively the "Before Insert" and/or "Before Update" trigger. It is still a good practice to perform conditional field assignment in those cases, too. See Deriving Values of a Field When Other Fields Change Value for more information on deriving field values.

Understanding "Before Commit" Performance Impact

When you write a trigger to derive field values programmatically, wherever possible use the Before Insert or Before Update triggers instead of Before Commit. When the Before Commit trigger fires, the changes in the row have already been sent to the database, and performing further field assignments therein requires doing a second round trip to the database to permanently save your field updates to each row modified in this way. When possible, using the Before-save triggers sets the field values before the changes are sent the first time to the database, resulting in better performance.

Note: If your script utilizes the getEstimatedRowCount() function on view object query with a complex filter, then use the Before Commit trigger for best results. The database COUNT() query the function performs to return the estimate is more accurate when performed over the already-posted data changes made during the current transaction.

Detecting Row State in After Changes Posted to Database Trigger

When writing an Before Commit trigger, if your code needs to detect the effective row state of the current object, use the getPrimaryRowState() function covered in Determining the State of a Row. For example, it can use getPrimaryRowState().isNew() to notice that the current object was created in the current transaction or getPrimaryRowState().isModified() to conclude instead that it was an existing row that was changed.

Avoiding Posting Threshold Errors By Conditionally Assigning Values

Despite the recommendation in Understanding “Before Commit” Performance Impact, if you still must use an Before Commit trigger then you must be aware of how this affects the object's so-called "posting cycle". For example, you might use it to perform field value assignments when your custom logic must perform a query that filters on the data being updated in the current transaction. If your trigger modifies the value of a field, this marks the object "dirty" again. This results in subjecting the object again to all of the defined validation rules to ensure that your new changes do not result in an invalid object. If the object passes validation, then your trigger's most recent field value changes must be posted again to the database. In the act of re-posting the object's changes, your trigger may fire again. If your trigger again unconditionally modifies one or more field values again, this process could result in a cycle that would appear to be an infinite loop. The runtime avoids this possibility by imposing a limit of 10 posting cycles on any given object. If after 10 attempts to post the (re)validated object to the database it remains "dirty," due to the object's being continually modified by your trigger logic, then the system will throw an exception complaining that you have exceeded the posting threshold:

Post threshold limit reached. Some entities yet to be posted

A simple way to avoid this from happening is to test the value of the field your script is about to assign and ensure that you perform the field assignment (or setAttribute() call to modify its value) only if the value you intend to assign is different from its current value. An example script employing this approach would look like this:

// After Changes Posted in Database Trigger
// If total score is 100 or more, set status to WON.
def totalScore = calculateTotalScoreUsingQuery()
if (totalScore >= 100) {
  // Only set the status to WON if it's not already that value
  if (Status != 'WON') {
    Status = 'WON'
  }
}

Functional Restrictions in Trigger Scripts

This section documents functional restrictions of which you should be aware when writing custom Groovy script in triggers.

  • Before Rollback Trigger

    Your trigger should not set the value of any fields in this trigger. The changes are too late to be included in the current transaction.

Passing the Current Object to a Helper Function

If an object function executes in the context of the object on which it is defined. However, if your object function needs to accept another object as a parameter, ensure that you choose Object as the parameter’s data type.

When writing code in any trigger, object function, or other object script, you can use the expression adf.source to pass the current object to another function you invoke that accepts a business object as a parameter.

Referencing Original Values of Changed Fields

When the value of a field gets changed during the current transaction, your code can still access the so-called "original value" of the field. This is the value it had when the existing object was retrieved from the database. Sometimes it can be useful to reference this original value as part of your business logic. To do so, use the getOriginalAttributeValue() function as shown below (substituting your field's name for the example's priority):

// Assume we're in context of a TroubleTicket
if (isAttributeChanged('priority')) {
  def curPri = priority
  def origPri = getOriginalAttributeValue('priority')
  println("Priority changed: ${origPri} -> ${curPri}")
  // do something with the curPri and origPri values here
}

Raising a Warning From a Validation Rule Instead of an Error

When your validation rule returns false, it causes a validation error that stops normal processing. If instead you want to show the user a warning that does not prevent the data from being saved successfully, then your rule can signal a warning and then return true. For example, your validation rule would look like this:

// if the discount is over 50%, give a warning
if (Discount > 0.50) {
  // raise a warning using the default declarative error message
  adf.error.warn(null)
}
return true

Throwing a Custom Validation Exception

When defining object level validation rules or triggers, normally the declaratively-configured error message will be sufficient for your needs. When your validation rule returns false to signal that the validation has failed, the error message you've configured is automatically shown to the user. The same occurs for a trigger when it calls the adf.error.raise(null) function. If you have a number of different conditions you want to enforce, rather than writing one big, long block of code that enforces several distinct conditions, instead define a separate validation rule or trigger (as appropriate) for each one so that each separate check can have its own appropriate error message.

That said, on occasion you may require writing business logic that does not make sense to separate into individual rules, and which needs to conditionally determine which among several possible error messages to show to the user. In this case, you can throw a custom validation exception with an error string that you compose on the fly using the following technique:

// Throw a custom object-level validation rule exception
// The message can be any string value
throw new oracle.jbo.ValidationException('Your custom message goes here')

Note that choose this approach, your error message is not translatable in the standard way, so it becomes your responsibility to provide translated versions of the custom-thrown error messages. You could use a solution like the one presented in Returning Locale-Sensitive Custom Strings for accomplishing the job.

Returning Locale-Sensitive Custom Strings

When you throw custom validation error messages, if your end users are multi-lingual, you may need to worry about providing a locale-specific error message string. To accomplish this, you can reference the current locale (inferred from each end user’s browser settings) as part of a function that encapsulates all of your error strings. Consider a getMessage function like the one below. Once it is defined, your validation rule or trigger can throw a locale-sensitive error message by passing in the appropriate message key:

// context is trigger or object-level validation rule
throw new oracle.jbo.ValidationException(getMessage('BIG_ERROR'))

The function is defined as follows.

  • Function Name: getMessage

  • Return Type: String

  • Parameters: stringKey String

Function Definition

// Let "en" be the default lang
// Get the language part of the locale
// e.g. for locale "en_US" lang part is "en"
def defaultLang = 'en';
def userLocale = adf.context.getLocale() as String
def userLang = left(userLocale,2)
def supportedLangs=['en','it']
def lookupLang = supportedLangs.contains(userLang)
                 ? userLang : defaultLang
def messages =
   [BIG_ERROR:  [en:'A big error occurred',
                 it:'È successo un grande errore'],
    SMALL_ERROR:[en:'A small error occurred',
                 it:'È successo un piccolo errore']
]
return messages[stringKey][lookupLang]

Raising a Trigger's Optional Declaratively-Configured Error Message

In contrast with a validation rule where the declarative error message is mandatory, when you write a trigger it is optional. Since any return value from a trigger's script is ignored, the way to cause the optional error message to be shown to the user is by calling the adf.error.raise() method, passing null as the single argument to the function. This causes the default declarative error message to be shown to the user and stops the current transaction from being saved successfully. For example, you trigger would look like this:

// Assume this is in a Before Insert trigger
if (someComplexCalculation() == -1) {
  // raise an exception using the default declarative error message
  adf.error.raise(null)
}

Accessing the View Object for Programmatic Access to Business Objects

A "view object" is a component that simplifies querying and working with business object rows. The newView() function allows you to access a view object dedicated to programmatic access for a given business object. Each time the newView(objectAPIName) function is invoked for a given value of object API name, a new view object instance is created for its programmatic access. This new view object instance is in a predictable initial state. Typically, the first thing you will then do with this new view object instance is:

  • Call the findByKey() function on the view object to find a row by key, or

  • Append a view criteria to restrict the view object to only return some desired subset of business objects rows that meet your needs, as described in Finding Objects Using a View Criteria.

A view object will typically be configured to return its results in sorted order. If the default sort order does not meet your needs, you can use the setSortBy() method on the view object to provide a comma-separated list of field names on which to sort the results. The new sort order will take effect the next time you call the executeQuery() method on the view object. See Defining the Sort Order for Query Results for further details on sorting options available.

A view object instance for programmatic access to a business object is guaranteed not to be used directly by your application user interface pages. This means that any iteration you perform on the view object in your script will not inadvertently affect the current row seen in the user interface.

Table Most Commonly Used View Object Methods

Method Name

Description

findByKey()

Allows you to find a row by unique id.

Returns: an array of rows having the given key, typically containing either zero or one row.

Parameters:

  • key - a key object representing the unique identifier for the desired row

  • maxRows - an integer representing the maximum number of rows to find (typically 1 is used)

Example: See Finding an Object by Id

findRowsMatchingCriteria()

Allows you to find a set of matching rows based on a filter criteria.

Returns: an iterator you can use to process the matching rows using methods iter.hasNext() and iter.next() ofr one row.

Parameters:

  • viewCriteria - a view criteria representing the filter. The easiest way to create a new view criteria is to use the newViewCriteria() function.

  • maxRows - an integer representing the maximum number of rows to find ( -1 means return all matching rows up to a limit of 500)

Example: See Finding Rows in a Child Rowset Using findRowsMatchingCriteria

appendViewCriteria()

Appends an additional view criteria query filter.

Parameters:

  • filterExpr - a String representing a filter expression.

  • ignoreNullBindVarValues - an optional boolean parameter indicating whether expression predicates containing null bind variable values should be ignored (defaults to false if not specified).

Returns: - void.

Alternatively, if you already have created a view criteria using newViewCriteria() you can pass that view criteria as the single argument to this function.

executeQuery()

Executes the view object's query with any currently appended view criteria filters.

Returns: - void.

hasNext()

Returns: - true if the row iterator has more rows to iterate over, false if there are no further rows in the iterator or it is already on or beyond the last row.

next()

Returns: - the next row in the iterator

reset()

Resets the view object's iterator to the "slot" before the first row.

Returns: - void.

first()

Returns: - the first row in the row iterator, or null if the iterator's row set is empty

createRow()

Creates a new row, automatically populating its system-generated Id primary key field.

Returns: - the new row

insertRow()

Inserts a new row into the view object's set of rows.

Returns: - void

setSortBy()

Set the sort order for query results.

Returns: - void

Defining the Sort Order for Query Results

To define the sort order for view object query results, call the setSortBy() method on the view object instance you are working with before calling its executeQuery() method to retrieve the results. The setSortBy() function takes a single string argument whose value can be a comma-separated list of one or more field names in the object. The following example shows how to use this method to sort by a single field.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by agent
applyViewCriteriaForSupportAnalyst(vo, analystId)
vo.setSortBy('Priority')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

By default the sort order will be ascending, but you can make your intention explicit by using the asc or desc keyword after the field's name in the list, separated by a space. The example below shows how to sort descending by the number of callbacks.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by customer
applyViewCriteriaForCustomerCode(vo, custCode)
vo.setSortBy('NumberOfCallbacks desc')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

As mentioned before, the string can be a comma-separated list of two or more fields as well. This example shows how to sort by multiple fields, including explicitly specifying the sort order.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by customer
applyViewCriteriaForCustomerCode(vo, custCode)
// Sort ascending by Priority, then descending by date created
vo.setSortBy('Priority asc, CreationDate desc')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}

By default, when sorting on a text field, its value is sorted case-senstively. A value like 'Blackberry' that starts with a capital 'B' would sort before a value like 'apple' with a lower-case 'a'. To indicate that you'd like a field's value to be sorted case-insensitively, surround the field name in the list by the UPPER() function as shown in the following example.

def vo = newView('TroubleTicket')
// Use object function to simplify filtering by customer
applyViewCriteriaForCustomerCode(vo, custCode)
// Sort case-insensitively by contact last name, then by priority
vo.setSortBy('UPPER(ContactLastName),Priority')
vo.executeQuery()
while (vo.hasNext()) {
  def curRow = vo.next()
  // Work with current row curRow here
}
Tip: While it is possible to sort on a formula field or dynamic choice field by specifying its name, don't do so unless you can guarantee that only a small handful of rows will be returned by the query. Sorting on a formula field or dynamic choice list must be done in memory, therefore doing so on a large set of rows will be inefficient.

Finding an Object by Id

To find an object by id, follow these steps:

  1. Use the newView() function to obtain the view object for programmatic access for the business object in question

  2. Call findByKey(), passing in a key object that you construct using the key() function

The new object will be saved the next time you save your work as part of the current transaction. The following example shows how the steps fit together in practice.

// Access the view object for the custom TroubleTicket object
def vo = newView('TroubleTicket')
def foundRows = vo.findByKey(key(100000000272002),1)
def found = foundRows.size() == 1 ? foundRows[0] : null;
if (found != null) {
  // Do something here with the found row
}

To simplify the code involved in this common operation, you could consider defining the following findRowByKey() helper function:

  • Function Name: findRowByKey

  • Return Type: oracle.jbo.Row

  • Parameters: vo oracle.jbo.ViewObject, idValue Object

Function Definition

println('findRowByKey')
def found = vo.findByKey(key(idValue),1)
return found.size() == 1 ? found[0] : null;

After defining this helper function, the example below shows the simplified code for finding a row by key.

// Access the view object for the custom TroubleTicket object
def vo = newView('TroubleTicket')
def found = findRowByKey(vo,100000000272002)
if (found != null) {
  // Do something here with the found row
}

Finding Objects Using a View Criteria

A "view criteria" is a declarative data filter for the custom or standard objects you work with in your scripts. After creating a view object using the newView() function, but before calling executeQuery() on it, use the appendCriteria() method to add a filter so the query will return only the rows you want to work with. This section explains the declarative syntax of view criteria filter expressions, and provides examples of how to use them. At runtime, the application development framework translates the view criteria into an appropriate SQL WHERE clause for efficient database execution.

Using a Simple View Criteria

To find custom or standard objects using a view criteria, perform the following steps:

  1. Create a view object with the newView() function

  2. Append a view criteria with the appendViewCriteria() function, using an appropriate filter expression

  3. Execute the query by calling executeQuery()

  4. Process the results

The example below queries the TroubleTicket object to find the trouble tickets assigned to a particular staff member with id 100000000089003 and which have a status of Working.

/*
 * Query all 'Working'-status trouble tickets assigned to a staff member with id 100000000089003
 */
// 1. Use the newView() function to get a view object
def vo = newView('TroubleTicket')
// 2. Append a view criteria using a filter expression
vo.appendViewCriteria("assignedTo = 100000000089003 and status = 'Working'")
// 3. Execute the query
vo.executeQuery()
// 4. Process the results
if (vo.hasNext()) {
  def row = vo.next()
  // Do something here with the current result row
}

Syntax of View Criteria Filter Expressions

You use a view criteria filter expression to identify the specific rows you want to retrieve from a view object. Each expression includes the case-sensitive name of a queriable field, followed by an operator and one or more operand values (depending on the operator used). Each operand value can be either a literal value or a bind variable value. An attempt to filter on a field that is not queriable or a field name that does not exist in the current object will raise an error. The following are simple examples of filter expressions.

To test whether a value is null you must use the is null or the is not null keywords:

  • Comment is null

  • Comment is not null

For equality use the = sign, and for inequality use either the != or the <> operators. Literal datetime values must adhere exclusively to the format shown here.

  • NextCallSchedule = '2015-07-15 16:26:30'

  • Priority = 3

  • Priority != 1

  • Priority <> 1

  • ActivityType != 'RS'

  • ActivityType <> 'RS'

For relational comparisons, use the familiar <, <=, >, or > operators, along with between or not between. Literal date values must adhere exclusively the format shown here.

  • CreationDate >= '2015-07-15'

  • Priority <= 2

  • Priority < 3

  • Priority <> 1

  • Priority > 1

  • Priority >= 1

  • TotalLoggedHours >= 12.75

  • Priority between 2 and 4

  • Priority not between 2 and 4

For string matching, you can use the like operator, employing the percent sign % as the wildcard character to obtain "starts with", "contains", or "ends with" style filtering, depending on where you place your wildcard(s):

  • RecordName like 'TT-%'

  • RecordName like '%-TT'

  • RecordName like '%-TT-%'

To test whether a field's value is in a list of possibilities, you can use the in operator:

  • ActivityType in ('OC','IC','RS')

You can combine expressions using the conjunctions and and or along with matching sets of parentheses for grouping to create more complex filters like:

  • (Comment is null) or ( (Priority <= 2) and (RecordName like 'TT-99%'))

  • (Comment is not null) and ( (Priority <= 2) or (RecordName like 'TT-99%'))

When using the between or in clauses, you must surround them by parentheses when you join them with other clauses using and or or conjunctions.

You use a filter expression in one of two ways:

  1. Append the view criteria filter expression using appendViewCriteria() to a view object created using newView()

  2. Create the view criteria by passing a filter expression to newViewCriteria(), then filter a related collection with findRowsMatchingCriteria()

Filter expressions are not validated at design time, so if your expression contains typographical errors like misspelled field names, incorrect operators, mismatched parentheses, or other errors, you will learn of the problem at runtime when you test your business logic.

Tips for Formatting Longer Criteria Across Multiple Lines

Groovy does not allow carriage returns or newlines to appear inside of a quoted string, so for example, the following lines of script would raise an error:

def vo = newView('StaffMember')
// ERROR: Single-line quotes cannot contain carriage returns or new lines
vo.appendViewCriteria("
  (Salary between 10000 and 24000) 
  and JobId <> 'AD_VP' 
  and JobId <> 'PR_REP' 
  and CommissionPct is null
  and Salary != 11000 
  and Salary != 12000 
  and (DepartmentId < 100  
       or DepartmentId > 200)
")
vo.executeQuery()

Luckily, Groovy supports the triple-quote-delimited, multi-line string literal, so you can achieve a more readable long view criteria filter expression using this as shown:

def vo = newView('StaffMember')
vo.appendViewCriteria("""
  (Salary between 10000 and 24000) 
  and JobId <> 'AD_VP' 
  and JobId <> 'PR_REP' 
  and CommissionPct is null
  and Salary != 11000 
  and Salary != 12000 
  and (DepartmentId < 100  
       or DepartmentId > 200)
""")
vo.executeQuery()

Using String Substitution for Literal Values into a View Criteria Expression Used Only Once

If you will only be using a view object a single time after calling newView(), you can use Groovy's built-in string substitution feature to replace variable or expression values directly into the view criteria expression text as shown in the following example:

def vo = newView('StaffMember')
def loSal = 13500
def anon = 'Anonymous'
vo.appendViewCriteria("(Salary between ${loSal} and ${loSal + 1}) and LastName != '${anon}'")
vo.executeQuery()

Notice that you must still include single quotes around the literal string values. The string subsitution occurs at the moment the string is passed to the appendViewCriteria() function, so if the values of the loSal or anon variables change, their new values are not reflected retroactively in the substituted string filter criteria expression. In this example below, Groovy substitutes the values of the loSal and anon into the view criteria expression string before passing it to the appendViewCriteria() function. Even though their values have changed later in the script, when the vo.executeQuery() is performed a second time, the view object re-executes using the exact same filter expression as it did before, unaffected by the changed variable values.

def vo = newView('StaffMember')
def loSal = 13500
def anon = 'Anonymous'
vo.appendViewCriteria("(Salary between ${loSal} and ${loSal + 1}) and LastName != '${anon}'")
vo.executeQuery()
// ... etc ...
loSal = 24000
anon = 'Julian'
// The changed values of 'loSal' and 'anon' are not used by the
// view criteria expression because the one-time string substitutions
// were done as part of the call to appendViewCriteria() above.
vo.executeQuery()

If you need to use a view object with appended view criteria filter expression multiple times within the same script, use named bind variables as described in the following section instead of string substitution. Using named bind variables, the updated values of the variables are automatically used by the re-executed query.

Using Custom Bind Variables for View Criteria Used Multiple Times

Often you may need to execute the same view object multiple times within the same script. If your operand values change from query execution to query execution, then named bind variables allow you to append a view criteria once, and use it many times with different values for the criteria expression operands. Just add one or more named bind variables to your view object, and then set the values of these bind variables as appropriate before each execution. The bind variables act as "live" placeholders in the appended filter expression, and their current values are used each time the view object's query is executed.

To add a named bind variable, use the addBindVariable() function. Pass a view object or rowset as the first argument and a string value to define the name of the bind variable as the second argument as shown in the example below. You can name your bind variable using any combination of letters, numbers, and underscores, as long as the name starts with a letter.

def vo = newView('StaffMember')
addBindVariable(vo,'VarLastName')
setBindVariable(vo,'VarLastName','King')
vo.appendViewCriteria('LastName = :VarLastName')
vo.executeQuery()
while (vo.hasNext()) {
  def r = vo.next();
  // Will return "Steven King" and "Janette King"
}
setBindVariable(vo,'VarLastName','Higgins')
vo.executeQuery()
while (vo.hasNext()) {
  def r = vo.next();
  // Will return "Shelley Higgins"
}

You can reference a named bind variable in the view criteria expression anywhere a literal value can be used, prefacing its name by a colon (e.g. :VarLastName). After adding the bind variable, you use the setBindVariable() function one or more times in your script to assign values to the variable. Until you explicitly set its value for the current view object or rowset, your bind variable defaults to having a value of null. Accidentally leaving the value null will result in retrieving no rows for most filter expressions involving a bind variable operand due to how the SQL language treats the null value in comparisons. The current value of the bind variable is used each time your script executes the view object. In the example below, this causes the rows for employees "Steven King" and "Janette King" to be returned during the first view object execution, and the row for "Shelly Higgins" to be returned on the second view object execution.

By default, the data type of the named bind variable is of type Text. If you need to use a bind variable in filter expressions involving number, date, or datetime fields, then you need to explicitly define a bind variable with the appropriate type for best performance. To add a bind variable of a specific datatype, pass one of the values Text, Number, Date, or Datetime as a string value to the optional third argument of the addBindVariable() function. For example, the following script uses two bind variables of type Number and another of type Date. Notice that the data type name is not case-sensitive (e.g. Number, number, or NUMBER are all allowed).

def vo = newView('TroubleTicket')
addBindVariable(vo,'VarLowPri','number')
addBindVariable(vo,'VarHighPri','Number')
addBindVariable(vo,'VarDueDate','DATE')
setBindVariable(vo, 'VarLowPri', 1)
setBindVariable(vo, 'VarDueDate', 2)
setBindVariable(vo, 'VarDueDate', today() + 3)
vo.appendViewCriteria('(priority between :VarLowPri and :VarHighPri) and dueDate < :VarDueDate')
vo.executeQuery()
while (vo.hasNext()) {
  def row = vo.next()
  // Returns trouble tickets with priorities 1 and 2 that are
  // due within three days from today
}
setBindVariable(vo, 'VarLowPri', 3)
setBindVariable(vo, 'VarDueDate', 4)
setBindVariable(vo, 'VarDueDate', today() + 5)
vo.executeQuery()
while (vo.hasNext()) {
  def row = vo.next()
  // Returns trouble tickets with priorities 3 and 4 that are
  // due within five days from today
}

Using View Criteria to Query Case-Insensitively

If you want to filter in a case-insensitive way, you can use the upper() function around the field name in the filter. If you are not sure whether the operand value is uppercase, you can also use the upper() function around the operand like this:

  • upper(JustificationCode) = 'BRK'

  • upper(JustificationCode) = upper(:codeVar)

  • upper(JustificationCode) like upper(:codeVar)||'%'

Limitations of View Criteria Filter Expressions

While view criteria filter expressions are extremely convenient, they do not support every possible type of filtering that you might want to do. This section describes several constructs that are not possible to express directly, and where possible, suggests an alternative way to achieve the filtering.

  • Only a case-sensitive field name is allowed before the operator

    On the left hand side of the operator, only a case-sensitive field name is allowed. So, for example, even a simple expression like 1 = 1 is considered illegal because the left-hand side is not a field name.

  • Cannot reference a calculated expression directly as an operand value

    You might be interested in querying all rows where one field is equal to a calculated quantity. For example, when querying trouble tickets you might want to find all open tickets whose Resolution Promised Date is less than three days away. Unfortunately, an expression like ResolutionPromisedDate <= today() + 3 is not allowed because it uses a calculated expression on the right hand side of the operator. As an alternative, you can compute the value of the desired expression prior to appending the view criteria and use the already-computed value as a literal operand value string substitution variable in the string or as the value of a bind variable.

  • Cannot reference a field name as an operand value

    You might be interested in querying all rows where one field is equal to another field value. For example, when querying contacts you might want to find all contacts whose Home Phone Number is equal to their Work Phone Number. Unfortunately, an expression like HomePhoneNumber = WorkPhoneNumber is not allowed because it uses a field name on the right hand side of the operator. A clause such as this will be ignored at runtime, resulting in no effective filtering.

  • Cannot reference fields of related objects in the filter expression

    It is not possible to reference fields of related objects directly in the filter query expression. As an alternative, you can reference the value of a related expression prior to appending the view criteria and use the already-computed value as a literal operand value string substitution variable in the string or as the value of a bind variable.

  • Cannot use bind variable values of types other than Text, Number, Date, or Datetime

    It is not possible to use bind variable values of types other than the four supported types: Text, Number, Date, and Datetime. An attempt to use other data types as the value of a bind variable may result in errors or in the criteria's being ignored.

Finding Rows in a Child Rowset Using findRowsMatchingCriteria

In addition to using view criteria to filter a view object that you create using newView(), you can also use one to retrieve a subset of the rows in a related collection. For example, if a TroubleTicket custom object contains a child object collection of related activities, you can process selected activities in the related collection using code as shown below:

def vo = newView('TroubleTicket')
vo.appendViewCriteria("priority = 1 and status = 'Open'")
vo.executeQuery()
def vc = null
// Process all open P1 trouble tickets
while (vo.hasNext()) {
  def curTicket = vo.next()
  def activities = curTicket.activityCollection
  if (vc == null) {
    addBindVariable(activities,'TodaysDate','date')
    vc = newViewCriteria(activities,"activityType in ('OC','IC') and creationDate > :TodaysDate")
  }
  // Process the activities created today for inbound/outbound calls
  setBindVariable(activities,'TodaysDate',today())
  def iter = activities.findRowsMatchingCriteria(vc,-1)
  while (iter.hasNext()) {
    def activity = iter.next()
    // process the activity here
  }
}

The newViewCriteria() function accepts an optional third parameter ignoreNullBindVarValues of boolean type that you can use to indicate whether filter expression predicates containing null bind variable values should be ignored. If omitted, the default value of this parameter is false.

Creating a New Object

To create a new object, follow these steps:

  1. Use the newView() function to obtain the view object for programmatic access for the business object in question

  2. Call the createRow() function on the view object to create a new row

  3. Set the desired field values in the new row

  4. Call insertRow() on the view object to insert the row.

The new object will be saved the next time you save your work as part of the current transaction. The example below shows how the steps fit together in practice.

// Access the view object for the custom TroubleTicket object
def vo = newView('TroubleTicket')
// Create the new row
def newTicket = vo.createRow()
// Set the problem summary
newTicket.ProblemSummary = 'Cannot insert floppy disk'
// Assign the ticket a priority
newTicket.Priority = 2
// Insert the new row into the view object
vo.insertRow(newTicket)
// The new data will be saved to the database as part of the current
// transaction when it is committed. 

Updating an Existing Object

If the object you want to update is the current row in which your script is executing, then just assign new values to the fields as needed.

However, if you need to update an object that is different from the current row, perform these steps:

  1. Use newView() to access the appropriate view object for programmatic access

  2. Find the object by id or find one or more objects using a view criteria, depending on your requirements

  3. Assign new values to fields on this row as needed

The changes will be saved as part of the current transaction when the user commits it.

Tip: See Avoiding Validation Threshold Errors By Conditionally Assigning Values for a tip about how to avoid your field assignments from causing an object to hit its validation threshold.

Permanently Removing an Existing Object

To permanently remove an existing object, perform these steps:

  1. Use newView() to access the appropriate view object for programmatic access

  2. Find the object by id or find one or more objects using a view criteria, depending on your requirements

  3. Call the remove() method on the row or rows as needed

The changes will be saved as part of the current transaction when the user commits it.

Reverting Changes in a Single Row

To revert pending changes to an existing object, perform these steps:

  1. Use newView() to access the appropriate view object for programmatic access

  2. Find the object by id

  3. Call the revertRowAndContainees() method as follows on the row

    yourRow.revertRowAndContainees() 

Understanding Why Using Commit or Rollback In Scripts Is Strongly Discouraged

By design you cannot commit or rollback the transaction from within your scripts. Any changes made by your scripts get committed or rolled-back along with the rest of the current transaction. If your script code were allowed to call commit() or rollback(), this would affect all changes pending in the current transaction, not only those performed by your script and could lead to data inconsistencies.

Using the User Data Map

The application development framework provides a map of name/value pairs that is associated with the current user's session. You can use this map to temporarily save name/value pairs for use by your business logic. Be aware that the information that you put in the user data map is never written out to a permanent store, so values your code puts into the user data map are only available during the current request.

To access the server map from a validation rule or trigger, use the expression adf.userSession.userData as shown in the following example:

// Put a name/value pair in the user data map
adf.userSession.userData.put('SomeKey', someValue)

// Get a value by key from the user data map
def val = adf.userSession.userData.SomeKey
Tip: See Using Groovy Maps and Lists with REST Services for more information on using maps in your scripts.

Referencing Information About the Current User

The adf.context.getSecurityContext() expression provides access to the security context, from which you can access information about the current user like her user name or whether she belongs to a particular role. The following code illustrates how to reference these two pieces of information:

// Get the security context
def secCtx = adf.context.getSecurityContext()
// Check if user has a given role
if (secCtx.isUserInRole('MyAppRole')) {
  // get the current user's name
  def user = secCtx.getUserName()
  // Do something if user belongs to MyAppRole
}

Using Aggregate Functions

Built-in support for row iterator aggregate functions can simplify a number of common calculations you will perform in your scripts, especially in the context of scripts written in a parent object which has one or more collections of child objects.

Understanding the Supported Aggregate Functions

Five built-in aggregate functions allow summarizing rows in a row set. The most common use case is to calculate an aggregate value of a child collection in the context of a parent object. The table below provides a description and example of the supported functions.

Table Supported Aggregate Functions

Aggregate Function

Description

Example (in Context ofTroubleTicket Parent Object

avg

Average value of an expression

ActivityCollection.avg('Duration')

min

Minimum value of an expression

ActivityCollection.min('Duration')

max

Maximum value of an expression

ActivityCollection.max('Duration')

sum

Sum of the value of an expression

ActivityCollection.sum('Duration')

count

Count of rows having a non-null expression value

ActivityCollection.count('Duration')

Understanding Why Aggegrate Functions Are Appropriate Only to Small Numbers of Child Rows

The aggregate functions described in this section compute their result by retrieving the rows of a child collection from the database and iterating through all of these rows in memory. This fact has two important consequences. The first is that these aggregate functions should only be used when you know the number of rows in the child collection will be reasonably small. The second is that your calculation may encounter a runtime error related to exceeding a fetch limit if the child collection's query retrieves more than 500 rows.

Understanding How Null Values Behave in Aggregate Calculation

When an ADF aggregate function executes, it iterates over each row in the row set. For each row, it evaluates the Groovy expression provided as an argument to the function in the context of the current row. If you want a null value to be considered as zero for the purposes of the aggregate calculation, then use the nvl() function like this:

// Use nvl() Function in aggregate expression
def avgDuration = ActivityCollection.min('nvl(Duration,0)')

Performing Conditional Counting

In the case of the count() function, if the expression evaluates to null then the row is not counted. You can supply a conditional expression to the count() function which will count only the rows where the expression returns a non-null value. For example, to count the number of child activities for the current trouble-ticket where the Duration was over half an hour, you can use the following expression:

// Conditional expression returns non-null for rows to count
// Use the inline if/then/else operator to return 1 if the
// duration is over 0.5 hours, otherwise return null to avoid
// counting that the non-qualifying row.
def overHalfHourCount = ActivityCollection.count('nvl(Duration,0) > 0.5 ? 1 : null')

Understanding the Difference Between Default Expression and Create Trigger

There are two ways you can assign default values to fields in a newly-created row and it is important to understand the difference between them.

The first way is to provide a default value expression for one or more fields in your object. Your default value expression should not depend on other fields in the same object since you cannot be certain of the order in which the fields are assigned their default values. The default value expression should evaluate to a legal value for the field in question and it should not contain any field assignments or any setAttribute() calls as part of the expression. The framework evaluates your default expression and assigns it to the field to which it is associated automatically at row creation time.

On the other hand, If you need to assign default values to one or more fields after first allowing the framework to assign each field's literal default values or default value expression, then the second way is more appropriate. Define a Create trigger on the object and inside that trigger you can reference any field in the object as well as perform any field assignments or setAttribute() calls to assign default values to one or more fields.

Deriving Values of a Field When Other Fields Change Value

There are three different use cases where you might want to derive the value of a field. This section assists you in determining which one is appropriate for your needs.

Deriving the Value of a Formula Field When Other Fields Change Value

If the value of your derived field is calculated based on other fields and its calculated value does not need to be permanently stored, then use a formula field. To derive the value of the formula field, perform these two steps:

  1. Configure the field’s Value Calculation setting to Calculate value with a formula

  2. Enter the formula as Groovy script that returns a value compatible with the field’s type

Deriving the Value of Non-Formula Field When Other Fields Change Value

If the value of your derived field must be stored, then use one of strategies in this section to derive its value.

Deriving a Non-Formula Field Using a Before Trigger

If your derived value depends on multiple fields, or you prefer to write all field derivation logic in a single trigger, then create an appropriate "before" trigger (Before Insert and/or Before Update) that computes the derived values and assigns each to its respective field. See Testing Whether a Field's Value Is Changed for more information on this function and Avoiding Validation Threshold Errors By Conditionally Assigning Values for a tip about how to avoid your field assignments from causing an object to hit its validation threshold.

Deriving a Non-Formula Field Using an After Field Changed Trigger

If your derived value depends on a single field’s value, then consider writing an After Field Changed trigger. When this trigger fires, the value of the field in question has already changed. Therefore, you can simply reference the new value of the field by name instead of using the special newValue expression (as would be required in a field-level validation rule to reference the field's candidate new value that is attempting to be set).

Setting Invalid Fields for the UI in an Object-Level Validation Rule

When a field-level validation rule that you've written returns false, ADF signals the failed validation with an error and the field is highlighted in the user interface to call the problem to the user's attention. However, since object-level validation rules involve multiple fields, the framework does not know which field to highlight in the user interface as having the problematic value. If you want your object-level validation rule to highlight one or more fields as being in need of user review to resolve the validation error, you need to assist the framework in this process. You do this by adding a call to the adf.error.addAttribute() function in your validation rule script before returning false to signal the failure. For example, consider the following rule to enforce: A contact cannot be his/her own manager. Since the id field of the Contact object cannot be changed, it will make sense to flag the manager reference field as the field in error to highlight in the user interface. Here is the example validation rule.

  • Rule Name: Contact_Cannot_Be_Own_Manager

  • Error Message: A contact cannot be his/her own manager

Rule Body

// Rule depends on two fields, so must be
// written as object-level rule
if (manager == id) {
  // Signal to highlight the Manager field on the UI
  // as being in error. Note that Manager_Id field
  // is not shown in the user interface!
  adf.error.addAttribute('manager')
  return false
}
return true

Determining the State of a Row

A row of data can be in any one of the following states:

  • New

    A new row that will be inserted into the database during the next save operation.

  • Unmodified

    An existing row that has not been modified

  • Modified

    An existing row where one or more values has been changed and will be updated in the database during the next save operation

  • Deleted

    An existing row that will be deleted from the database during the next save operation

  • Dead

    A row that was new and got removed before being saved, or a deleted row after it has been saved

To determine the state of a row in your Groovy scripts, use the function getPrimaryRowState() and its related helper methods as shown in the following example.

// Only perform this business logic if the row is new
if (getPrimaryRowState().isNew()) 
{
  // conditional logic here
}

The complete list of helper methods that you can use on the return value of getPrimaryRowState() is shown below:

  • isNew()

    Returns boolean true if the row state is new, false otherwise.

  • isUnmodified()

    Returns boolean true if the row state is unmodified, false otherwise.

  • isModified()

    Returns boolean true if the row state is modified, false otherwise.

  • isDeleted()

    Returns boolean true if the row state is deleted, false otherwise.

  • isDead()

    Returns boolean true if the row state is dead, false otherwise.

Understanding How Local Variables Hide Object Fields

If you define a local variable whose name is the same as the name of a field in your object, then be aware that this local variable will take precedence over the current object's field name when evaluated in your script. For example, assuming an object has a field named Status, then consider the following object validation script:

// Assuming current object has a Status field, define local variable of the same name
def Status = 'Closed'
/* 
 *    :
 * Imagine pages full of complex code here
 *    :
 */
// If the object's current status is Open, then change it to 'Pending'
// ------------------
// POTENTIAL BUG HERE: The Status local variable takes precedence
// ------------------  so the Status field value is not used!
//
if (Status == 'Open') {
  Status = 'Pending'
}

At the top of the example, a variable named Status is defined. After pages full of complex code, later in the script the author references the custom field named Status without remembering that there is also a local variable named Status defined above. Since the local variable named Status will always take precedence, the script will never enter into the conditional block here, regardless of the current value of the Status field in the current object. As a rule of thumb, use a naming scheme for your local variables to ensure their names never clash with object field names.

Invoking REST Services from Your Scripts

Calling a REST service endpoint from your scripts involves these high-level steps:

  • Create a service connection with one or more endpoints, choosing meaningful Service Id and Endpoint Ids

  • Write Groovy code to:

    • Acquire a new service object using newService(’yourServiceId’)

    • Set any necessary path parameters, query parameters, or HTTP header fields

    • Construct a payload object if one is required

    • Invoke an endpoint method on the service object, passing a payload object if needed

    • If successful, process the response payload, checking HTTP status code if needed

    • If exception was thrown, catch and handle it, checking HTTP status code if needed

This section explains these steps in more detail.

Creating a Service Connection

To invoke a REST service from your Groovy script, start by creating a service connection. Visual Builder defaults a value for its Service Name and Service Id fields, but allows you to change both if desired. Since your code will reference the service using its Service Id, choose an identifier that will help others reading your code understand what the service does. Note that the value of Service Id cannot be changed after the service connection is created.

Each service consists of one or more endpoints. Each of a service’s endpoints is a distinct operation that the service can perform. Visual Builder defaults a value for each endpoint’s Endpoint Id, but allows you to subsequently change it when you edit the endpoint. Your Groovy code will directly reference the Endpoint Id as a method name when you invoke the service, so choose an identifier that will help others reading your code understand the function each method performs.

Consider a service connection with a Service Name of UsersService and a Service Id of usersService. Suppose it has endpoints with Endpoint Id values getUser, getUsers, createUser, and updateUser. Your Groovy code will use the respective id values of the service and endpoint to call a service operation like getUser at runtime. For example, you might write:
def userSvc = newService('usersService') // NOTE: Service Id, not Service Name
def newUser = userSvc.createUser([id:456, name:'Steve'])
Attention: If you update the Endpoint Id of a service endpoint after Groovy code has referenced it, adjust your code to use the corresponding new method name or runtime errors will result.

Acquiring a New Service Object

Every service call you make in a script requires a service object. To obtain an appropriate service object on which to invoke an endpoint method, use the newService() function, passing in the service id representing the service you want to use. Consider a service connection with a Service Name of UsersService and a Service Id of usersService. Use the id value of the service to acquire an appropriate service object:

// NOTE: Service Id, not Service Name
def userSvc = newService('usersService')

Setting Path Parameters If Needed

A service connection encapsulates a base URL. For example, a service named UsersService might correspond to a base URL of https://hcm.example.org. Each service endpoint, in turn, corresponds to a relative path that complements the service’s base URL to define a unique resource. For example, one of this example service’s endpoints might have an id of getUsers and correspond to the /users path. In this case, calling the service may require just two lines of code:

// Get the list of users
def userSvc = newService('usersService')
def userList = userSvc.getUsers()

Sometimes an endpoint’s path includes a substitution parameter whose value represents the unique id of a resource, like the id of a user in this example. Consider another endpoint named getUser with /users/{userid} as its path. The {userid} represents a path parameter named userid whose value your code must supply before calling the endpoint method. A failure to do so will result in a runtime error. To set a path parameter named userid, use the service object’s pathParams map as shown in this example:

// Get information for the user 3037
def userSvc = newService('usersService')
userSvc.pathParams.userid = '3037'
def user = userSvc.getUser()

If the name of a path parameter is not a legal Groovy identifier because it contains a character like a hyphen or space (e.g. user-id), use this alternative map syntax instead:

userSvc.pathParams['user-id'] = '3037'

If you set multiple path parameters in a single line, then the user-id map key still need to be quoted like this:

userSvc.pathParams = ['user-id': '3037', anotherParam: 'anotherValue']

A service endpoint can also have more than one path parameter. In this case, you can set each path parameter separately like this:

// Get information for the user 3037
def userSvc = newService('usersService')
userSvc.pathParams.userid = '3037'
userSvc.pathParams.anotherParam = 'anotherValue'
def user = userSvc.getUser()

Alternatively, you can set all path parameters at the same time by assigning the pathParams map like this:

// Get information for the user 3037
def userSvc = newService('usersService')
userSvc.pathParams = [userid: '3037', anotherParam: 'anotherValue']
def user = userSvc.getUser()

Setting Query Parameters If Needed

In addition to path parameters, a service endpoint may require supplying one or more values for so-called “query” parameters. These parameters can affect how the service responds to your request, or may even be mandatory. For example, suppose the getUser endpoint supports a query parameter named format whose valid values are compact and verbose, and a query parameter named currency to specify the three-letter code of the currency in which to report the user’s balance. The service documentation will clarify whether the format and currency parameters are required or optional, and explain any relevant default behavior. Failure to supply a value for a required query parameter may result in a runtime error. To use this endpoint to retrieve the compact form of a given user’s information for the euro currency, use the service object’s queryParams map as shown in this example:

// Get compact info for the user 3037
def userSvc = newService('usersService')
userSvc.pathParams.userid = '3037'
userSvc.queryParams.format = 'compact'
userSvc.queryParams.currency = 'EUR'
def user = userSvc.getUser()

When setting multiple query parameters, as an alternative approach to setting each parameter on its own line as shown above, it’s also possible to assign the entire parameters map in a single assignment. The example below is equivalent to the one above, but notice the queryParams map is set to a map containing two entries in a single line:

// Get compact info for the user 3037
def userSvc = newService('usersService')
userSvc.pathParams.userid = '3037'
userSvc.queryParams = [format: 'compact', currency: 'EUR']
def user = userSvc.getUser()

If the name of a query parameter is not a legal Groovy identifier because it contains a character like a hyphen or space (e.g. base-currency), use this alternative map syntax instead:

userSvc.queryParams['base-currency'] = 'EUR'

If you are assigning the entire map at once, then it is still required to quote the key value, but the syntax looks like this:

userSvc.queryParams = [format: 'compact', 'base-currency':'EUR']

Setting HTTP Headers If Needed

Beyond using path parameters, and query parameters, a service endpoint might support behavior controlled by header fields. Each header field is a name/value pair where the value is a list containing one or more values. Suppose the documentation for our getUser service explains that it supports retrieving user information in either XML or JSON format, based on the value of an HTTP header field named Accept. Assume the two values it recognizes for this field are either application/xml or application/json. To retrieve the verbose form of a given user’s information in JSON format, use the service object’s requestHTTPHeaders map as shown in this example:

// Get compact info for the user 3037
def userSvc = newService('usersService')
userSvc.pathParams.userid = '3037'
userSvc.queryParams.format = 'verbose'
// Notice the value is a list containing one string!
userSvc.requestHTTPHeaders.Accept = ['application/json'] 
def user = userSvc.getUser()

If the name of a header field is not a legal Groovy identifier because it contains a character like a hyphen or space (e.g. Content-Type), use this alternative map syntax instead:

userSvc.requestHTTPHeaders['Content-Type'] = ['application/json']

Using Groovy Maps and Lists with REST Services

When passing and receiving structured data from a REST service endpoint, a Groovy Map represents an object and its properties. In fact, maps and lists are all you need to work with service request and response payloads. In particular, you never need to work directly with the JavaScript Object Notation (JSON) string representation of an object because the platform automatically converts between Groovy objects and JSON as necessary.

For example, an Employee object with properties named Empno , Ename, Sal, and Hiredate would be represented by a Map object having four key/value pairs, where the names of the properties are the keys. You can create an empty Map using the syntax:

def newEmp = [:]

Then, you can add properties to the map using the explicit put() method like this:

newEmp.put('Empno',1234)
newEmp.put('Ename','Sean')
newEmp.put('Sal',9876)
newEmp.put('Hiredate',date(2013,8,11))

Alternatively, and more conveniently, you can assign and/or update map key/value pairs using a simpler direct assignment notation like this:

newEmp.Empno = 1234
newEmp.Ename = 'Sean'
newEmp.Sal = 9876
newEmp.Hiredate = date(2013,8,11)

Finally, you can also create a new map and assign some or all of its properties at once using the constructor syntax:

def newEmp = [Empno    : 1234,
              Ename    : 'Sean',
              Sal      : 9876,
              Hiredate : date(2013,8,11)]

To create a collection of objects you use the Groovy List object. You can create one object at a time and then create an empty list, and call the list's add() method to add both objects to the list:

def dependent1 = [Name: 'Dave', BirthYear: 1996]
def dependent2 = [Name: 'Jenna', BirthYear: 1999]
def listOfDependents = []
listOfDependents.add(dependent1)
listOfDependents.add(dependent2)

To save a few steps, the last three lines above can be done in a single line by constructing a new list with the two desired elements in one line like this:

def listOfDependents = [dependent1, dependent2]

You can also create the list of maps in a single go using a combination of list constructor syntax and map constructor syntax:

def listOfDependents = [[Name: 'Dave', BirthYear: 1996], 
                        [Name: 'Jenna',BirthYear: 1999]]

If the employee object above had a property named Dependents that was a list of objects representing dependent children, you can assign the property using the same syntax as shown above (using a list of maps as the value assigned):

newEmp.Dependents = [[Name: 'Dave', BirthYear: 1996], 
                     [Name: 'Jenna',BirthYear: 1999]]

Lastly, note that you can also construct a new employee with nested dependents all in one statement by further nesting the constructor syntax:

def newEmp = [Empno      : 1234,
              Ename      : 'Sean',
              Sal        : 9876,
              Hiredate   : date(2013,8,11),
              Dependents : [
                            [Name: 'Dave', BirthYear: 1996], 
                            [Name: 'Jenna', BirthYear: 1999]
                           ] 
             ]

For more information on Maps and Lists, see Working with Lists and Working with Maps

Checking Success Status and Handling Exceptions

Each service endpoint method call can succeed or fail. If a call succeeds, then your script continues normally. If necessary, you can check the exact value of the success status code in the range of 200-399 using the HTTPStatusCode field of the service object. If a call fails, an exception named RestConnectionException is thrown. Use Groovy’s try/catch syntax around the service endpoint method invocation to properly handle an eventual error. If you ignore this best practice, the unhandled exception will be reported to your end user, perhaps causing unnecessary confusion or alarm. If necessary, you can check the exact value of the failure status code in the range of 400599 by using the statusCode field of the exception object. The following example shows both of these techniques in practice:

// import the exceptions for later use below
import oracle.adf.model.connection.rest.exception.RestConnectionException
import oracle.jbo.ValidationException
// Fetch user info for an existing user
def userSvc = newService('usersService')
userSvc.pathParams.userid = '3037'
try {
  def user = userSvc.getUser()
  def status = userSvc.HTTPStatusCode
  // perform logic here on success
}
catch (RestConnectionException rcex) {
  def status = rcex.statusCode
  // on failure, handle error here
  throw new ValidationException('User registry unavailable, try again later.')
}

Calling Service Endpoint Methods

For a registered service having id someService, to call its service endpoint with id someEndpoint in your script, do the following:

  1. Import RestConnectionException and JboException for error handling

  2. Define a service object variable svc and assign it newService(’someService’)

  3. Configure pathParams, queryParams, and requestHTTPHeaders maps of svc as needed

  4. Define a variable reqPayload if needed and assign it a value

  5. Inside a try block...
    • Define a variable respPayload to hold the response payload

    • Assign svc.someEndpoint(…) to the variable

    • Process the response payload after checking svc.HTTPStatusCode if needed

  6. Inside the corresponding catch block for RestConnectionException...

    • Handle the error appropriately, after checking rcex.statusCode if necessary

    • Return false to fail a validation rule or raise a custom exception

A full example that follows these guidelines looks like this object function:
// Object function cloneUser( userIdToClone String )
// Imports only needed once at the top of the script
import oracle.adf.model.connection.rest.exception.RestConnectionException
import oracle.jbo.ValidationException
def svc = newService('usersService')
svc.pathParams.userid = userIdToClone
svc.queryParams['api-key'] = 'vCDDpu3NjiytQF'
svc.requestHTTPHeaders['Some-Header'] = ['Somevalue']
// No payload needed for getUser()
def cloneUserResponse = svc.getUser()
// Diagnostic println visible in the Logs window
println("User ${userIdToClone} to clone: "+
        "city = ${cloneUserResponse.address.city}, "+
        "zip = ${cloneUserResponse.address.zipcode}")
def createRequest = [name: userName, 
                  address: [ city: cloneUserResponse.address.city, 
                             zipcode: cloneUserResponse.address.zipcode ]]
// Get new service, else must clear params/headers maps from previous call
svc = newService('usersService')
try {
  def newUserResponse = svc.createUser(createRequest)
  // Assign business object field remoteUserId
  // with the system-generated user id from response
  remoteUserId = newUserResponse.id
  println("Set remoteUserId of new LocalUser to ${newUserResponse.id}")
}
catch (RestConnectionException rcex) {
  throw new ValidationException('User registry unavailable, try again later.')
} 

Browsing Available Service Endpoint Methods

When writing your scripts, as shown in the figure below, the Services tab in the Code Helper palette displays the endpoint methods for all service connections. Each service’s Service Name appears in a title bar in the list. Use the toggle control to the left of each service name to expand or collapse the list of that service’s endpoints. Clicking on the right arrow in the palette margin inserts the appropriate Groovy code to call the service endpoint in question, providing template code where appropriate.


Services Tab in Code Helper Palette

For example, suppose you had clicked on the right-arrow next to the getUser function as shown in the figure. This would insert the following lines of Groovy code into your script wherever the cursor is positioned in the editor:
def usersService = newService('usersService');
usersService.pathParams['id'] = 'idValue'; // TODO: Change this value
def usersServiceGetUser = usersService.getUser(); 

After doing this, notice the TODO comment and adjust the example idValue in quotes to be the appropriate user id value you want to retrieve from the UsersService service.

Formatting Numbers and Dates Using a Formatter

Groovy provides the Formatter object that you can use in a text formula expression or anywhere in your scripts that you need for format numbers or dates. The general pattern for using a Formatter is to first construct a new instance like this, passing the the expression for the current user's locale as an argument:

def fmt = new Formatter(adf.context.locale)

This Formatter object you've instantiated will generally be used to format a single, non-null value by calling its format() method like this:

def ret = fmt.format( formatString, arg1 [, arg2, ..., argN] )

Note that if you call the format() method of the same Formatter object multiple times, then the results are concatenated together. To format several distinct values without having their results be concatentated, instantiate a new Formatter for each call to a format() method.

The format string can include a set of special characters that indicate how to format each of the supplied arguments. Some simple examples are provided below, however the complete syntax is covered in the documentation for the Formatter class.

Example of Formatting a Number Using a Formatter

To format a number numberVal as a floating point value with two (2) decimal places and thousands separator you can do:

Double dv = numberVal as Double
def fmt = new Formatter(adf.context.locale)
def ret = (dv != null) ? fmt.format('%,.2f', dv) : null

If the value of numberVal were 12345.6789, and the current user's locale is US English, then this would produce a formatted string like:

12,345.68

If instead the current user's locale is Italian, it would produce a formatted string like:

12.345,68

To format a number numberVal as a floating point value with three (3) decimal places and no thousands separator you can do:

Double dv = numberVal as Double
def fmt = new Formatter(adf.context.locale)
def ret = (dv != null) ? fmt.format('%.3f', dv) : null

If the value of numberVal were 12345.6789, and the current user's locale is US English, then this would produce a formatted string like:

12345.679

To format a number value with no decimal places to have a zero-padded width of 8, you can do:

Long lv = numberVal as Long
def fmt = new Formatter(adf.context.locale)
def ret = (lv != null) ? fmt.format('%08d', lv) : null

If the value of numberVal were 5543, then this would produce a formatted string like:

00005543

Formatting a Date Using a Formatter

To format a datetime value datetimeVal to display only the hours and minutes in 24-hour format, you can do:

Date dv = datetimeVal as Date
def fmt = new Formatter(adf.context.locale)
def ret = (dv != null) ? fmt.format('%tH:%tM', dv, dv) : null

If the value of datetimeVal were 2014-03-19 17:07:45, then this would produce a formatted string like:

17:07

To format a date value dateVal to display the day of the week, month name, the day, and the year, you can do:

Date dv = dateVal as Date
def fmt = new Formatter(adf.context.locale)
def ret = (dv != null) ? fmt.format('%tA, %tB %te, %tY',dv,dv,dv,dv) : null

If the value of dateVal were 2014-03-19, and the current user's locale is US English, then this would produce a formatted string like:

Wednesday, March 19, 2014

Working with Field Values Using a Parameterized Name

When writing reusable code, if your object function needs to perform the same operations on different fields, you can parameterize the field name. Start by defining a function parameter of type String whose value at runtime will be the name of a field in the current object. Then, when your code needs to access the value of the parameterized field, just call getAttribute(fieldNameParam). To assign a new value to that field, call setAttribute(fieldNameParam,newValue). In either case, if the value of the field name parameter passed in does not match the name of some field in the current object, a NoDefException will be thrown to signal an error.

Consider the following example of an object function named conditionalIncrement() that increments the value of the number field whose name is passed in only if the field’s value is less than a maximum value also passed in:

// Object function: void conditionalIncrement(fieldName String, maxValue Long)
// ---------------
def fieldValue = getAttribute(fieldName)
if (fieldValue < maxValue) {
  setAttribute(fieldName, fieldValue + 1)
} 

The first line defines a fieldValue variable to store the value of the field whose name is passed in. If its value is less than maxValue, then line three assigns the field a new value that is one greater than its current value. Once you define an object function like conditionalIncrement(), then any Groovy scripts on the same object can invoke it, passing in appropriate argument values. For example, in one script suppose you need to increment the value of a field named UsageCount if its value is less than 500:

// Increment the usage count if it is less than 500
conditionalIncrement('UsageCount', 500)

In another script, imagine you need to increment the value of a DocumentVersionNumber field if its value is less than 1000. You can use the same object function: just pass in different values for the field name and maximum value parameters:

// Increment the document version number if it is less than 1000
conditionalIncrement('DocumentVersionNumber', 1000)

Of course the getAttribute() and setAttribute() functions can also accept a literal String value as their first argument, so you could theoretically write conditional logic like:

// Ensure document is not locked before updating request-for-approval date
// NOTE: more verbose get/setAttribute() approach
if (getAttribute('DocumentStatus') != 'LOCKED') {
  setAttribute('RequestForApprovalDate', today())
}

However, in the example above, when the name of the field being evaluated and assigned is not coming from a parameter or local variable, then it is simpler and more readable to write this equivalent code instead:

// Ensure document is not locked before updating request-for-approval date
// NOTE: More terse, elegant direct field name access
if (DocumentStatus != 'LOCKED') {
  RequestForApprovalDate = today()
}

When invoked on their own, the getAttribute() and setAttribute() functions operate on the current object. However, anywhere in your script code where you are working with a business object Row, you can also call these functions on that particular row as shown in the following example of an object function. Notice that it also parameterizes the name of the object passed to the newView() function:

// Object function: String getRowDescription(objectName String, displayFieldName String, id Long)
// ---------------
// Create a new view object to work with the business object whose name is
// passed in the objectName parameter
def view = newView(objectName)
// Find the row in that view whose key is given by the value of the id parameter
def rows = view.findByKey(key(id),1)
// If we found exactly one row, return the value of the display field name on
// that row, whose field name is given by the value in the displayFieldName parameter
return rows.size() == 1 ? return rows[0].getAttribute(displayFieldName) : null

With such a function defined, we can invoke it from any script in the object to access the display field value of different objects we might need to work with:

// Get RecordName of the Task object with key 123456
def taskName = getRowDescription('Task','RecordName',123456)
// Get the Name of the Territory object with key 987654
def optyName = getRowDescription('Territory','Name',987654)

If you use the getAttribute() or setAttribute() to access field values on a related object, remember that the first argument must represent the name of a single field on the object on which you invoke it. For example, the following is not a correct way to use the setAttribute() function to set the Status field of the parent TroubleTicket object for an activity because TroubleTicket?.Status is not the name of a single field on the current Activity object:

// Assume script runs in context of an Activity object (child of TroubleTicket)
// INCORRECT way to set a parent field's value using setAttribute()
setAttribute('TroubleTicket?.Status', 'Open')

Instead, first access the related object and store it in a local variable. Then you can assign a field on the related object as follows:

// Assume script runs in context of an Activity object (child object TroubleTicket)
// First access the parent object
def parentTicket = TroubleTicket
// Then call the setAttribute on that parent object
parentTicket?.setAttribute('Status', 'Open')