How to use Velocity to grab newest entry in custom object?

[Anonymous]

Hello fine humans of Marketo Nation!

I'm facing a bit of a quandary with our Velocity scripting, and could use your help.

Basically, my company is sending out appointment emails, which are triggered based on updates to the custom object. A customer could have multiple entries for the custom object.

Here's my issue: the emails are often spitting out old information, not the newest available. The script that's in there was around before I arrived, and was apparently implemented by a 3rd party that we no longer work with. From what I can tell, it's bunk, and is operating no differently than if we just had the basic info dragged over in the token script editor. I can include it if people would find it helpful, though honestly it looks like a mess and I can't make any sense of it.

My question to you: How do I get Marketo to pull the newest entry of a custom object? Most of the fields I'm working with are strings and one date. I've been trying to do the date field just to keep it simple, but no matter how I'm structuring it, it's spitting out the oldest date.

An example of what the basic script would be for the date token: ${appointment_cList.get(0).appointmentDate}

There's also IDs for appointmentTime and appointmentCode.

Any advice would be most appreciated!

[SanfordWhiteman · Accepted solution]

Hey Alex,

To get the first and last objects in an ArrayList (in Velocity, Custom Object entries are presented as Java ArrayLists, that is, arrays of objects), use

#if( $AppointmentList && !$AppointmentList.isEmpty() )
#set( $firstItem = $AppointmentList[0] )
#set( $lastItem = $AppointmentList[$math.sub($AppointmentList.size(),1)] )
#end

What's almost awesomely confusing is you can't use typical "get last" syntax from just about any other language, including Java:

#set( $lastItem = $AppointmentList[$AppointmentList.size()-1] ) ## Won't work, syntax error!

Velocity doesn't support what we think of as the ubiquitous subtraction operator, as in $a - 1 or $a - $b, inside square brackets. One of several restrictions on nested literal calculations, which I don't even think are documented. But if you call one of the $math methods (passing the size and the literal number 1 into the method) it's fine.

Among other examples, this reminds us that while Velocity is a "child language" of Java, with access to thousands of awesome Java methods... it isn't Java. I'm writing about little factoids like this in an upcoming blog post, continuing from an old post on sorting lists.

[Anonymous]

Thank you so much for this insight, Sanford. I look forward to reading your blog post!

Velocity is such a weird language, and there doesn't seem to be amazing documentation for learning it. Do you know of any resources for learning more beyond velocity.apache.org?

So my next question is: If I'm able to sort the objects using an ArrayList, how do I then grab the individual fields/IDs within the object? For instance, I have three tokens (date, time, location) I'm trying to pull info for, all belonging to the same object (appointment). Would it be something along the lines of using the script you posted above just to pull the correct object, and then using the usual list.get(0).ID script to pull the data?

Okay, looking at what you wrote, along with the original code, I'm thinking the last statement I wrote is bunk, so I'm going to rephrase.

Below is the script I'm using to find an appointment date:

## find closest future LLS appointment

## begin selection logic

#set($matchedAppt = false)

#foreach($appt in ${appointment_cList})

#set($matchedApptDate = false)

#set($currentApptDate = $date.toDate('yyyy-M-d', $appt.appointmentDate))

#set($currentApptOffset = $date.difference($date, $currentApptDate).getMilliseconds())

## if appointment is in future

#if($currentApptOffset > 0 && $appt.orderType == 'LLS')

## first match

#if(! $matchedAppt)

#set($matchedAppt = $appt)

## nth match

#elseif($matchedAppt)

#set($matchedApptDate = $date.toDate('yyyy-M-d', $matchedAppt.appointmentDate))

#end

##endif match

## if comparable

#if($currentApptDate && $matchedApptDate)

#set($difference = $date.difference($currentApptDate, $matchedApptDate).getMilliseconds())

## current closer than existing match

#if($difference > 0)

#set($matchedAppt = $appt)

#end

#end

##endif comparable

#end

##endif future

#end

##endfor

## end selection logic

## begin usage

#if($matchedAppt && $matchedAppt.appointmentDate)

<span>$date.format('full_date',$date.toDate('yyyy-M-d', $matchedAppt.appointmentDate))</span>##

#else

<span>Unknown</span>##

#end

Would I then insert the snippet of script you provided into that, and then it would pick the most recent object from there??

Another fun factoid: I hate this script because whenever I try to send myself a test lead sample email, it spits out "unknown" at me. When I do the basic ${appointment_cList.get(0).appointmentDate}, it at least spits out the earliest custom object entry and I can actually see that information. Hence why I'm asking about getting it to grab the most recent entry instead of messing with the selection logic above...something is clearly off with it.

Slowly it's starting to make a little more sense, but I'm still struggling to understand the logic. I come from a front-end dev world so this is new to me.

Thanks again!

[SanfordWhiteman]

Do you need any of the rest of that (convoluted and probably broken for your needs) code?

The snippet I gave is all you need to pop the last CO entry. Then $lastItem.appointmentDate is its date.

If you need more logic I'll tell you how to build it, but the old code should go.

[Anonymous]

I'm happy to ditch that code, I think it's useless as well but wasn't 100% since I'm still getting to know the language.

If you are available to help me build/execute, that would be amazing. I tried to put the snippet together with the $lastItem.appointmentDate but it didn't send the test email, which usually means I got something in the script wrong.

I can be reached at alex.bridges@llsa.com; I'm working from home off and on today and through the weekend. Last resort is I try to hire a Marketo consultant on Monday, but I feel like I'm missing something super obvious here and that I can probably avoid it......if I can figure out what it is I'm missing.

[Anonymous]

Alex,

if the campaign is triggered you could use TriggerObject to access the custom object data.

#set($appointmentId = $!{TriggerObject.appointmentId})

you could replace appointmentId with the fields from your CO.

From OP ' which are triggered based on updates to the custom object.'  Did you mean new data is added or update to existing data?

thanks

[SanfordWhiteman]

This needs to work in batch and trigger campaigns, so that doesn't apply.

[SanfordWhiteman]

#set($appointmentId = $!{TriggerObject.appointmentId})

Also P.S. in #set you don't need to silence the the reference or use curly brackets. Just makes the code harder to read.

[Anonymous]

Sanford-

Thank you again for your help on this. I just made all changes live this morning and everything is working great. This has been a lifesaver!