Pull Custom Object Record by Most recent Date in Field | Community
Skip to main content
Samantha_Cossum
Samantha_Cossum
New Participant
May 8, 2019
Solved

Pull Custom Object Record by Most recent Date in Field

  • May 8, 2019
  • 1 reply
  • 5272 views

Hi all,

We store our customer's training history in a custom object. We use the velocity scripts to pull in the date of upcoming trainings and I am trying to take that script and adjust it to pull the last day they had a specific training but having issues getting it to pull the most recent date. 

I've read a bunch of articles on here and and around the web but I'm still struggling to get it to work/apply to our specific situation. Our code is a little messy because we pull off the training types (which have changed over the years) and for this specific use case, that the status of training is complete (due to a change in our sync the case changed so I'm accounting for that as well). The actually date I want to pull in is the most recent end date of the training. Below is my code.Everything I tried to add to make it work just broke the script so this is without any of tries at sorting. 

#set($startDate = $trainingRecord_cList)
#set($type = $trainingRecord_cList)
#foreach($event in $startDate)
#if(($event.trainingType == "New Patient Mastery On-Site Solution (New Member)" || $event.trainingType == "New Patient Mastery (New Member)" || $event.trainingType == "5 Star Telephone Training (New Member)" || $event.trainingType == "5 Star Telephone Training (New Member))" || $event.trainingType == "5 Star Telephone Training (Upgrade)" || $event.trainingType == "Advanced Telephone & Capacity Techniques" || $event.trainingType == "New Patient Mastery (Upgrade)" || $event.trainingType == "New Patient Mastery On-Site Solution (Upgrade)") && ($event.trainingStatus == "complete" || $event.trainingStatus == "Complete"))
#set($origDate = $convert.parseDate($event.trainingScheduledEndDate,"yyyy-MM-dd"))
#set($formattedDate = $date.format('full_date',$origDate))
#end    
#end
$formattedDate‍‍‍‍‍‍‍‍‍

I'm still newer to Velocity and the scripts we use for our trainings were built beside an outside company before I came on so any suggestions would be very much appreciated! 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by SanfordWhiteman
#set( $interestingTypes = [
  "New Patient Mastery On-Site Solution (New Member)",
  "New Patient Mastery (New Member)",
  "5 Star Telephone Training (New Member)",
  "5 Star Telephone Training (New Member))",
  "5 Star Telephone Training (Upgrade)",
  "Advanced Telephone & Capacity Techniques",
  "New Patient Mastery (Upgrade)",
  "New Patient Mastery On-Site Solution (Upgrade)"
] )
#foreach( $event in $sorter.sort($trainingRecord_cList, "trainingScheduledEndDate:desc") )
#if( $interestingTypes.contains($event.trainingType) && $event.trainingStatus.equalsIgnoreCase("complete") )
#set( $latestEventDate = $event.trainingScheduledEndDate )
#break
#end    
#end‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This will give you $latestEventDate set to the latest date (still as the original date-like String, not true Date -- you can sort by date-like Strings without converting them, as long as the format is yyyy-MM-dd).

I'll leave it to you to format the date-like String to Date based on my blog post.

1 reply

SanfordWhiteman
SanfordWhiteman
New Participant
May 8, 2019

First, any work with dates/datetimes in Velocity must be aware of timezones. See my seminal post: https://blog.teknkl.com/velocity-days-and-weeks/ 

Let's try to state your requirement more clearly. Do you want to filter the $trainingRecord_cList so it only contains the subset of records matching a certain combo of $trainingStatus and $trainingType, then sort that subset by date to get the latest $trainingScheduledEndDate?

Samantha_Cossum
Samantha_CossumAuthor
New Participant
May 8, 2019

That's exactly right. It needs to be one of the training types above and be at the stage complete before we even look at the end dates to sort. Basically, we are trying to tell them the last time they had a specific training. 

I'll take a look at your time zones post as well! 

SanfordWhiteman
SanfordWhitemanAccepted solution
New Participant
May 8, 2019
#set( $interestingTypes = [
  "New Patient Mastery On-Site Solution (New Member)",
  "New Patient Mastery (New Member)",
  "5 Star Telephone Training (New Member)",
  "5 Star Telephone Training (New Member))",
  "5 Star Telephone Training (Upgrade)",
  "Advanced Telephone & Capacity Techniques",
  "New Patient Mastery (Upgrade)",
  "New Patient Mastery On-Site Solution (Upgrade)"
] )
#foreach( $event in $sorter.sort($trainingRecord_cList, "trainingScheduledEndDate:desc") )
#if( $interestingTypes.contains($event.trainingType) && $event.trainingStatus.equalsIgnoreCase("complete") )
#set( $latestEventDate = $event.trainingScheduledEndDate )
#break
#end    
#end‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This will give you $latestEventDate set to the latest date (still as the original date-like String, not true Date -- you can sort by date-like Strings without converting them, as long as the format is yyyy-MM-dd).

I'll leave it to you to format the date-like String to Date based on my blog post.

Terms & ConditionsAccessibility statement

Loading footer...