Re: Pull Custom Object Record by Most recent Date in Field

Samantha_Cossum · ‎05-08-2019

I did double check and there's is set but just to test I set the default to match what we have it as and it still didn't pull in any thing

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Toronto") )
#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 (Upgrade)",
  "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)"
] )
#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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I even tried stripping out timezone and parsing the date to see if I used the base code if it would pull but still not luck. Going through it more I think it has something to do with the sort. When I try this:

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Toronto") )
#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 (Upgrade)",
  "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)"
] )
#foreach( $event in $trainingRecord_cList)
#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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

It pulls in information. For the example I gave it doesn't do the correct one but for another person who gave me the issue (and only has one training that fits the criteria) it also pulled it.

SanfordWhiteman · ‎05-08-2019

Without the sort it would only pick the right one by coincidence -- you must sort descending if you want to get the latest.

But the trainingScheduledEndDate cannot be null for any of the records if you're going to sort on it (null is not sortable). Check that.

Samantha_Cossum · ‎05-09-2019

That's it! Not all the trainings are scheduled so not every record has those dates. I switched to created date and that seems to be working.

That's again for all your help. I really really appreciate it!