Pull Custom Object Record by Most recent Date in Field

[Samantha_Cossum]

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! 

[SanfordWhiteman]

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]

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 · Accepted solution]

#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.

[Samantha_Cossum]

Working like a charm and I am in awe of how much cleaner and easier to follow this is! Going to have to put in some time to convert our other scripts to this format (and add time zone!). I think I got the time zone part correct but below is my code if you have a moment to double check! 

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone(${lead.Person_Time_Zone}) )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601 = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#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 )
#set($origDate = $convert.parseDate($latestEventDate,$ISO8601DateOnly,$defaultLocale,$defaultTimeZone))
#set($formattedDate = $date.format('full_date',$origDate,$defaultLocale,$defaultTimeZone))
$formattedDate
#break
#end    
#end‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Really really appreciate all you do for the community Sanford Whiteman‌!!

[SanfordWhiteman]

Great! The one thing I would change is take out the formal reference (curly braces) on this line as it can lead to confusion:

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone(${lead.Person_Time_Zone}) )‍‍

You only need

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone($lead.Person_Time_Zone) )
‍‍‍‍

... I am in awe of how much cleaner and easier to follow this is! Going to have to put in some time to convert our other scripts to this format...

Yep, I strongly endorse this collection-first code structure in Velocity.

In fact needing to use it in Velocity to stay sane (since the language is so verbose) has pushed me to use it in other languages, like in JS. Not too many people can say they learned good habits from Velocity!

[Samantha_Cossum]

Is there anything I should look out for that could break this? Doing some more testing and getting some random people where nothing is populating even though I know they have a record. Below is an example:

[SanfordWhiteman]

What's the output when you simply output $trainingRecord_cList for that lead?

[Samantha_Cossum]

Pulls in quite a few records including the one I'm looking for:

{trainingScheduledStartDate=2018-01-19, trainingScheduledEndDate=2018-01-19, trainingStatus=Complete, trainingType=New Patient Mastery On-Site Solution (Upgrade), updatedAt=2018-01-20 05:05:15}] 

It's working for the majority but he few giving me issues don't seem to have anything in common. Different training types, different cases for complete, different dates and placement within their list of COs. 

[SanfordWhiteman]

Betcha it's the timezone, then.