8 Replies Latest reply on Mar 20, 2017 8:40 AM by Alex Bridges

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

    Alex Bridges

      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!

        • Re: How to use Velocity to grab newest entry in custom object?
          Sanford Whiteman

          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.

          2 of 2 people found this helpful
            • Re: How to use Velocity to grab newest entry in custom object?
              Alex Bridges

              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!

              1 of 1 people found this helpful