Velocity email scripting - sorting a list

[Phillip_Wild]

Curly one for you guys here...it's been driving me crazy.

We're digging into email scripting with our Salesforce custom objects. Specifically, I'm trying to extract a list of dates from a custom object, sort them descending, and return the number of days that the highest date is from today. Here's an example...

A customer buys a service that departs on the 1st March 2014, 4th September 2014 and the 11th July 2015. The answer I am looking for is 11th July 2015 - today's date = 5.

Here's the code so far:

#set($ServicesArray = [])

## Set a blank array called ServicesArray - all good so far!

#foreach ($srv in ${TravellerServices__cList})

  #if (${srv.ServiceTypeTS__c} == "Tour")

#set($Elements = $ServicesArray.add(${srv.Service_Start_Date__c}))

      #end

## For each service in TravellerServices__cList (our Salesforce object), if it's a Tour type, then add it to my blank ServicesArray. I know this works correctly because when I print the list after this point, the output is [date1, date2, date3...date n].

#foreach($srv in $sorter.sort($ServiceArray, ["$Service_Start_Date__c:asc"]))

    $srv.Service_Start_Date__c

#end

## This is my attempt at a sort based on some googling Apache Velocity - Velocity User Guide  and SortTool (VelocityTools 2.0-beta4 Documentation) . This is where it stops working. I don't really know the syntax I should be using, and any attempts to sort either fail in Marketo (I get an error upon sending sample email) or the sample email sent is never delivered (which I can only assume is an error too).

Additionally, I haven't even been able to get something as simple as "return me n element from this list" to work using a function such as $ServiceArray.get(0) . This purely prints that text in the email. I've also tried setting it to a variable first  - #set ($DateRequired = $ServiceArray.get(0)) with no success either.

Any ideas? Gold star for anyone who can help!

[Anonymous]

Phillip Wild​. Good morning. I moved your post to the Products and Support​, where most of our product expert hang out.

[Anonymous]

Hi Phillip,

First word of caution:  not all functions that are described in Apache documentation are available in the Marketo VTL environment.  Marketo's VTL environment settings and controls are entirely opaque, so it's a matter of trial and error to see which functions work and which don't.

I've also found that while I can define an array in Marketo VTL with a fixed set of values, I've had problems with creating an empty array and assigning values into it.  My guess is that there are some safeguards in Marketo to maintain run-time efficiencies when building thousands or millions of messages, but I honestly don't know.  The array variable you're trying to use might not displaying correctly if it just doesn't exist.  Marketo won't tell you that and won't tell you why.  Sometimes, that's just the way it is.  😞

What I've done in VTL in the situation you describe is to create a foreach loop where I check each value against the prior one and "keep" the older date.  Once you've gone all the way through the loop, your variable contains the oldest date.  It's not very efficient, but it's more efficient than trying to figure out why something more elegant doesn't work.

Also, looking at your code, you may be missing some #end markers.  You need one for every #if and #foreach.  That's a simple syntax issue and may be affecting your script as well (though you may also not be sharing the full script - I'm not trying to nitpick, just help where I can).  🙂

-patrick

[Phillip_Wild]

Thanks Patrick, good info!

I have tried the comparison within the #foreach loop and that seems to work. But...getting a comparison between today's date and the values in a variable seems very difficult.

$date gives you today, in a horrible format. But I can't seem to do a simple subtraction between that field and another. It's probably a data type issue, but without knowing how to transform both into the same date data type I can't get it to work. Any ideas?

[Anonymous]

I am having exactly the same issue (re: sorting) - From the Marketo Email Scripting page I cannot see how you can sort when the SortTool is actually disabled from within Marketo (org.apache.velocity.tools.generic.SortTool)!?!

[Phillip_Wild]

Neil, I've given up on it for now, but I may revisit it - I'll post here if I manage to get it to work. But I don't have high hopes given how much I've already tried....

[Anonymous]

So you can't order values at all?  i.e. you haven't managed to yet?!

[Anonymous]

Not sure I can help with your original inquiry, but the comment about subtracting the dates I can give you a starting point on. Here is an example of one script where I transformed the current date and a custom object date field using the Calendar tool to allow me to compare them. In this case, I was trying to find orders that were expiring in the next 60 days.

#set ( $todayCalObj = $date.toCalendar($date.toDate("yyyy-MM-dd",$date.get('yyyy-MM-dd'))) )

#set ( $orderCalObj = $date.toCalendar($date.toDate("yyyy-MM-dd",${Order__c.Expiration_Date__c})) )

#set ( $dateDiff = ($orderCalObj.getTimeInMillis() - $todayCalObj.getTimeInMillis()) / 86400000 )

#if ( $dateDiff > 0 )

#if( $dateDiff < 61 )

[Anonymous]

Just to add one more thing, the Sort Tool is listed on the Marketo developer site as not supported right now.

[Phillip_Wild]

Hi Kristen

Do you have a link to where this is mentioned? I'd like to check it out, thanks.

Phil