1. Groovy Scripting Reference
  2. Avoid Using newView() Inside a Loop

Avoid Using newView() Inside a Loop

Using newView() inside a loop can lead to unpredictable ExprResourceException errors when you inadvertently create more view objects than the system allows in a single trigger or object function.

For example, consider the following code that iterates over an unknown number of uncleared transaction records. For each uncleared transaction processed, if the transaction currency is not GBP then it queries the historical exchange rate based on the transaction date to convert the non-GBP currency amount in question into GBP. It uses the newView() function inside the loop to query the ExchangeRate_c business object and does so without using bind variables. This approach creates one new view object for each loop iteration. If the number of transaction rows being iterated over is unpredictably large, this technique can produce an ExprResourceException error when it hits the upper limit on number of view objects that can be created in a single trigger or function.

// NON-BEST-PRACTICE EXAMPLE: USES newView() INSIDE A LOOP !!
// ~~~~~~~~~~~~~~~~~~~~~~~~~  May lead to unpredictable ExprResourceException error
// Create view object for processing uncleared transactions
def txns = newView('Transaction_c')
txns.appendViewCriteria("Cleared_c = 'N'")
selectAttributesBeforeQuery(txns,['Id','Cleared_c','Currency_c','Amount_c','Date_c'])
txns.executeQuery()
// Process each uncleared transaction
while (txns.hasNext()) {
  def rate = 1
  def txn = txns.next()
  def curr = txn.Currency_c
  if (curr != 'GBP') {
    def date = txn.Date_c
    // NON-BEST PRACTICE: USE OF newView() INSIDE A LOOP !!
    def rates = newView('ExchangeRate_c')
    rates.appendViewCriteria("From_c = '${curr}' and To_c = 'GBP' and Date_c = '${date}'")
    rates.executeQuery()
    rate = rates.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)   
  }
}
In these situations, use this approach instead:
  • Create a single view object outside the loop that references bind variables in its filter criteria
  • Inside the loop, set the values of the bind variables for the current loop iteration
  • Execute the query on the single view object once per loop iteration

By adopting this technique, you use a single view object instead of an unpredictably large number of view objects and you avoid encountering the ExprResourceException when iterating over a larger number of rows. The code below implements the same functionality as above, but follows these best practice guidelines.

// BEST PRACTICE: Single VO with bind variables outside the loop
// ~~~~~~~~~~~~~
// Create view object to be reused inside the loop for exchange rates
def rates = newView('ExchangeRate_c')
addBindVariable(rates,'Base','Text')
addBindVariable(rates,'ForDate','Date')
rates.appendViewCriteria("From_c = :Base and To_c = 'GBP' and Date_c = :FromDate")
// Create view object for processing uncleared transactions
def txns = newView('Transaction_c')
txns.appendViewCriteria("Cleared_c = 'N'")
selectAttributesBeforeQuery(txns,['Id','Cleared_c','Currency_c','Amount_c','Date_c'])
txns.executeQuery()
// Process each uncleared transaction
while (txns.hasNext()) {
  def rate = 1
  def txn = txns.next()
  def curr = txn.Currency_c
  if (curr != 'GBP') {
    def date = txn.Date_c
    // BEST PRACTICE: Set bind variables & execute view object created outside loop
    setBindVariable(rates,'Base',curr)
    setBindVariable(rates,'ForDate',date)
    rates.executeQuery()
    rate = rates.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)   
  }
}

If the functionality inside the loop becomes more involved, you may benefit by refactoring it into an object function. The object function below shows an exchangeRateForCurrencyOnDate() helper function that accepts the single view object created outside the loop as a parameter of type Object. Inside the function, it sets the bind variables, executes the view object's query, and returns the resulting exchange rate.

Object Function: Float exchangeRateForCurrencyOnDate( Object rates, String curr, Date date) 

// Set bind variables and execute view object passed in
setBindVariable(rates,'Base',curr)
setBindVariable(rates,'ForDate',date)
rates.executeQuery()
return rates.first()?.Rate_c

After refactoring the code into this object function, the if block in the original best-practice code above can be changed to:

  // etc.
  if (curr != 'GBP') {
    def date = txn.Date_c
    // Pass single 'rates' view object into the helper function
    rate = exchangeRateForCurrencyOnDate(rates,curr,date)
  }
  // etc.

Using the queryMaps() or queryRows() helper functions described in Simplifying Business Logic Queries you can perform the same optimized best-practice technique described in this section with fewer lines of code like this:

// 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)  
  }
}

Related Topics