Velocity Script - Find most recent complete record - HELP

Douglas_Meierdi
Level 2

Velocity Script - Find most recent complete record - HELP

Hello Marketo Community -

I'm trying to build a token to populate an email with information about the contractor the customer most recently worked with. The script references a custom object called "Job" which contains information about - you guessed it - a job. These are jobs created by customers and the customer doesn't always complete the transaction (i.e. hire a contractor).

The script I have seems to find the first job record ever. What I need is a script that will find the most recent record where they actually hired someone. Here's what I have:

#set($url = "www.upcounsel.com/jobs/new?aid=${jobList.get(0).attorneyId}")

<p align="center">

  <img src="${jobList.get(0).attorneyImgUrl}" alt="" style="max-height:150px;max-width: 150px;border-radius:100%;"><br>

  <a href="https://${url}" style="margin-top: 15px; padding: 8px 20px; color: #ffffff; width: 175px; line-height: 30px; display: inline-block; text-align: center; font-family: Helvetica; text-decoration: none; background-color: #00C76B; border-radius: 3px;">

    Rehire ${jobList.get(0).attorneyName}

  </a>

</p>

So how do I get this script to A) Start with the last record and work backwards and B) evaluate the record to ensure that the fields it needs to pull are not blank?

6 REPLIES 6
SanfordWhiteman
Level 10 - Community Moderator

Re: Velocity Script - Find most recent complete record - HELP

What are the field(s) that signify a completed transaction? Does the mere existence of the ​attorneyId property on the job ​determine that the job is complete? Or must ​attorneyId, attorneyName, ​and/or some other fields all be non-empty?

Douglas_Meierdi
Level 2

Re: Velocity Script - Find most recent complete record - HELP

Correct - if the attorneyId is present, they've been hired and attorneyName and attorneyImage would also be populated.

Douglas_Meierdi
Level 2

Re: Velocity Script - Find most recent complete record - HELP

Also - currentstatus would be "completed_and_paid"

SanfordWhiteman
Level 10 - Community Moderator

Re: Velocity Script - Find most recent complete record - HELP

We should be looking for

     currentstatus == "completed_and_paid"

so:

#set( $lastCompletedJob = {} )

#set( $jobCount = $jobList.size() )

#foreach( $i in [$math.sub($jobCount,1)..0] )

#set( $job = $jobList[$i] )

#if( $job.currentstatus == "completed_and_paid" )

#set( $lastCompletedJob = $job )

#break

#end

#end

#set($url = "www.upcounsel.com/jobs/new?aid=${lastCompletedJob.attorneyId}")

<p align="center">

  <img src="${lastCompletedJob.attorneyImgUrl}" alt="" style="max-height:150px;max-width: 150px;border-radius:100%;"><br>

  <a href="https://${url}" style="margin-top: 15px; padding: 8px 20px; color: #ffffff; width: 175px; line-height: 30px; display: inline-block; text-align: center; font-family: Helvetica; text-decoration: none; background-color: #00C76B; border-radius: 3px;">

    Rehire ${lastCompletedJob.attorneyName}

  </a>

</p>

Douglas_Meierdi
Level 2

Re: Velocity Script - Find most recent complete record - HELP

Thanks Sanford - I'll try this, see how it works and let you know if it does. Also - can you recommend any good tutorials to learn Velocity? I'm looking at this and it makes no sense to me what exactly it's doing.

SanfordWhiteman
Level 10 - Community Moderator

Re: Velocity Script - Find most recent complete record - HELP

http://blog.teknkl.com/tag/velocity

And all my posts here on it.

The problem with trying to learn Velo is that nobody out there (except for me, that's one of my bizarre specializations) focuses on it within a Marketo context. Velocity/VTL is a venerable, stable, and incredibly useful language, but used primarily for web page templating -- and even more confusingly, used by Java programmers, who often have access to extend the VTL language by adding their own methods, while within Marketo we have only the base language and tools.

I'm looking at this and it makes no sense to me what exactly it's doing.

  • declare a variable to hold the last completed Job (not actually necessary in VTL, but looks cleaner)
  • get size() of the Job list
  • last index of the list is size() - 1
  • walk backward through the list from size() - 1 to 0
  • when you find a Job whose currentstatus is "completed and paid"
    • set the last completed Job to this job
    • exit the loop