Pulling Salesforce Opportunity Value using email scripting

[Benjamin_Ortiz1]

Can anyone provide a basic example of pulling an opportunity field value as a token using email scripting?

[SanfordWhiteman]

Opportunities are available in the Velocity context as OpportunityList. This is an ArrayList, or array of objects. Visualize it like this:

[
   {
      "property1" : "apple",
      "property2" : 12
    },
   {
      "property1" : "pear",
      "property2" : 34
    }
]

Reference an item in the array with square brackets.  OpportunityList[0] is the first item.

Reference properties of an item using dot-notation or square brackets (brackets are safer): OpportunityList[0]["property2"] is the number 12.

If you haven't already, you should check my posts at http://blog.teknkl.com/tag/velocity if you're going to dip into email scripting.

[Benjamin_Ortiz1]

Thanks for the reply Sanford. Basically what I am trying to accomplish is if an Opportunity meets certain criteria, then send an email with an opportunity field as a token in that email.

So IF Opportunity Stage = 'ABC' then send email with the Opportunity field 'DUE DATE' as a token. 

What I am trying to wrap my head around is how the value is pulled from the 'DUE DATE' field when multiple Opportunities are associated with a single Contact in SFDC.

[SanfordWhiteman]

Thanks for the reply Sanford. Basically what I am trying to accomplish is if an Opportunity meets certain criteria, then send an email with an opportunity field as a token in that email.

So IF Opportunity Stage = 'ABC' then send email with the Opportunity field 'DUE DATE' as a token.

What I am trying to wrap my head around is how the value is pulled from the 'DUE DATE' field when multiple Opportunities are associated with a single Contact in SFDC.

It's pulled from the DueDate property of the Opportunity with the stage you want (obviously if your field is actually all caps DUEDATE, use that spelling).

So say this is how your OpportunityList looks:

  {
    "Stage" : "pear",
    "DueDate" : "2017-07-03"
  },
  {
    "Stage" : "apple",
    "DueDate" : "2017-07-09"
  },
  {
    "Stage" : "orange",
    "DueDate" : "2017-06-01"
  },
  {
    "Stage" : "apple",
    "DueDate" : "2017-05-09"
  }

Then you'd first filter the list to get the Opportunities matching your stage, sorting by date in case there's more than one:

#set( $filteredOppties = [] )
#foreach( $oppty in $sorter.sort($OpportunityList, "DueDate") )
#if( $oppty["Stage"] == "apple" )
#set( $tmp = $filteredOppties.add($oppty) )
#end
#end

Now

${filteredOppties}

is a list of opportunities with Stage "apple", ordered by DueDate ascending.

And

${filteredOppties[0]}

is the first such opportunity.

And

${filteredOppties[0]["DueDate"]}

is the DueDate field of the first opportunity.

It's all quite linear (it's hard to write complex code in VTL).

[Benjamin_Ortiz1]

THANK YOU - I owe you a beer.

I am now pulling the due date field formatted as 2017-05-02...I would like the full date to read Tuesday, May 02, 2017.

I saw the below in another thread but I am not sure how this would work into the script:

$date.format('full_date',$dateInXDays.time)

[SanfordWhiteman]

#set( $dateOptions = {
  "formats" : {
    "system" : "yyyy-MM-dd",
    "userin" : "yyyy-MM-dd",
    "userout" : "yyyy-MM-dd"
  },
  "timezones" : {
    "system" : "America/Chicago",
    "userin" : "America/New_York",
    "userout" : "America/New_York"
  },
  "locale" : $date.getLocale()
} )
#set( $interestingDueDateLike = $filteredOppties[0]["DueDate"] )
#set( $interestingDueDate = $convert.parseDate(
  $interestingDueDateLike,
  $dateOptions.formats.userin, 
  $dateOptions.locale, 
  $date.getTimeZone().getTimeZone($dateOptions.timezones.userin)
) )
#set( $interestingDueDateFormatted = $date.format( 'full_date', $interestingDueDate ) )
${interestingDueDateFormatted}

Substitute your primary tz for the timezone.userin property.

Any Velocity code that deals with actual date objects (as opposed to printing date-like strings verbatim) must be timezone-aware.  That's why the code suddenly jumps in complexity. Otherwise, it's guaranteed that some of your dates will be off, as midnight is different across time zones.  (Pretty much all Velocity date examples are broken for this reason, including Marketo's own.)

[Benjamin_Ortiz1]

Thanks Sanford, would this go in the same script right after:

                #set( $tmp = $filteredOppties.add($oppty) ) 

I've added it in however the date is still displaying as yyy-MM-dd

[SanfordWhiteman]

Thanks Sanford, would this go in the same script right after:

#set( $tmp = $filteredOppties.add($oppty) )

No, it goes after the entirety of the original script, i.e. after the closing #end.

...
#end
#end
#set( $dateOptions = {
  "formats" : {
    "system" : "yyyy-MM-dd",
    "userin" : "yyyy-MM-dd",
    "userout" : "yyyy-MM-dd"
  },
  "timezones" : {
    "system" : "America/Chicago",
    "userin" : "America/New_York",
    "userout" : "America/New_York"
  },
  "locale" : $date.getLocale()
} )
...

[Benjamin_Ortiz1]

Now I am seeing the below appended, Any Ideas? Sry for all of the follow up here, really appreciate the help!

[Benjamin_Ortiz1]

Below is what the actual field name looks like when I drag the opportunity token over from the right, could that effect the code above to change the date format? Thanks!

${OpportunityList.get(0).Due_Date__c}