X

It's All About the Platform.

Limitations in View Object Filtering in Groovy Script

Guest Author

Introduction

This blog post considers different aspects of filtering View Object data in Groovy script. It follows on from this blog article which is about querying records in View Objects.

View Criteria filter expressions are a convenient way to filter data result sets in view objects. There are some considerations, that are useful to note when executing them from within Groovy. The following sections detail these considerations in an attempt to explain how to best use view criterias when querying view objects using Groovy..

View Criteria Usage Overview

The following is an example of view criteria usage from the Oracle Sales Cloud documentation. It illustrates how to query records from a view object and is here to refresh your memory on how to use view criterias in the first place:

/*
 * 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_c')  
// 2. Append a view criteria using a filter expression  
vo.appendViewCriteria("AssignedTo_Id_c = 100000000089003 and Status_c = '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
}

Here are some considerations when using view criterias.

Field Names and Operators

When creating a groovy expression to build a bind value for use in the view criteria, only a field name is allowed on the left side of an operator. In the above example 100000000089003 = AssignedTo_Id_c would not be allowed in the expression but AssignedTo_Id_c = 100000000089003 would be, even though they are both the same.

Calculated Expressions and Operand Values

When using a View Criteria, any expression functions have to be evaluated and stored within variables before the variables can be referenced from within the View Criteria expression values.

For example the View Criteria expression ResolutionPromisedDate_c <= today() + 3 will not work within a view criteria expression because the date is being calculated from within the expression. Instead the value for today has to be evaluated first def thisDay = today() and then it can be referenced in the view criteria bind value expression ResolutionPromisedDate_c <= thisDay+3.

Field Names and Operand Values

When querying records it's not possible to filter only the rows where two columns have the same value. For example an expression like HomePhoneNumber_c = WorkPhoneNumber_c 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.

Further filtering would have to be performed within Groovy after the view criteria has been applied. For example when looping over the results for display the variables can be compared and the row displayed on the condition that HomePhoneNumber_c = WorkPhoneNumber_c.

Related Object Fields and Filter Expressions

It is not possible to reference fields of related objects directly in the filter query expression. For example the following view criteria filter expression would not work:

def vo = newView('<OBJECT_API_NAME>');
vo.executeQuery();
def row = vo.getCurrentRow();
def vo2 = newView('<OBJECT2_API_NAME>');
vo2.appendViewCriteria("Status_c = row.getAttribute('<FIELD_NAME>')");

The <FIELD_NAME> field would have to be evaluated prior to appending the view criteria and use the already-computed value as a literal operand value. The following would work:

def vo = newView('<OBJECT_API_NAME>');
vo.executeQuery();
def row = vo.getCurrentRow();
def att = row.getAttribute('<FIELD_NAME>');
def vo2 = newView('<OBJECT2_API_NAME>');
vo2.appendViewCriteria("Status_c = att");

Variable Types

When creating a view criteria expression, only the following data types can be used within the expression: 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.

Further Information

Here are some links to related information to help understand this functionality further:

Join the discussion

Comments ( 5 )
  • Subramanian Nachiappan Monday, January 2, 2017

    Hi,

    Requirement : I want to custom copy an Opportunity and create a new Opportunity and load the newly created Opportunity by groovy script.

    I am able to create a custom copy of Opportunity using groovy but unable to navigate to it in groovy.

    can you give some suggestions.


  • Peter Wednesday, January 4, 2017

    Hi Subramanian,

    Is it possible to paste the groovy code you are using to copy the Opportunity data and navigate to it?

    What triggers your groovy code?

    kind regards,

    Peter.


  • Subramanian Nachippan Thursday, January 5, 2017

    Hi Peter,

    Groovy Code to Copy Opportunity

    newOptyRec = optyVO.createRow();

    newOptyRec.setAttribute('StatusCode',var_newStatusCd)

    newOptyRec.setAttribute('Name',var_NewOptyName)

    newOptyRec.setAttribute('OpportunityType_c',var_newOptyType)

    newOptyRec.setAttribute('EffectiveDate',var_newCloseDate)

    optyVO.insertRow(newOptyRec)

    and do you have any in-site on the Out of the box copy functionality in OSC CRM for opportunity using the function :

    doCopyOpportunityRow(NEW_OPPORTUNITY_NAME)

    Regards,

    Subramanian Nachiappan


  • Peter Thursday, January 5, 2017

    Hi Subramanian,

    Not sure how you are navigating to the row after you have copied and created it.

    This blog article describes how to query and insert rows into View Objects using Groovy. Hopefully that will have the answer you need - https://blogs.oracle.com/fadevrel/entry/application_composer_series_using_groovy

    hope that helps.


  • Subramanian Nachiapan Friday, January 6, 2017

    Hi Peter,

    i am actually new to groovy and sales cloud.i want code samples for navigating to the new opportunity which i create using the below script.

    newOptyRec = optyVO.createRow();

    newOptyRec.setAttribute('StatusCode',var_newStatusCd)

    newOptyRec.setAttribute('Name',var_NewOptyName)

    newOptyRec.setAttribute('OpportunityType_c',var_newOptyType)

    newOptyRec.setAttribute('EffectiveDate',var_newCloseDate)

    optyVO.insertRow(newOptyRec)

    Thanks,

    Subramanian Nachiappan


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.