Velocity Script- How do I get the data from most recent SFDC Custom Object record?

[Malay_Chakrabo1]

Hi there, 

I am trying to get various Velocity script to fetch data from a Subscription SFDC Custom Object and I wanted to ask help in understanding how do I make sure that I am selecting the relevant Subscription record. I am trying to only select the records whose subscription is ending in the next 3 months. 

Thus, I wanted to check if there is a way in VTL to filter/constraint certain conditions? So the script always picks up the relevant data. ?

Example Script: 

##Fetch Subscription Data from SFDC Custom object
#set($SubscriptionName = ${Subscription__cList.get(0).Name})
#set($SubscriptionStatus = ${Subscription__cList.get(0).Subscription_Status__c})
#set($AutoRenew = ${Subscription__cList.get(0).AutoRenew__c})
#set($NextRenewalDate = ${Subscription__cList.get(0).NextRenewalDate__c})

##Conditions 
#if (($SubscriptionStatus == "Active") && ($AutoRenew == "true"))
${SubscriptionName}
#else
 Test Fallback Name
#end

With this script where there are multiple subscription records, the script is picking up the oldest record always and the fallback sentence is displayed. 

Please let me know if you know how I can select the right record to fetch data from? 

Thanks

[SanfordWhiteman · Accepted solution]

First, let's touch up your code to use simpler syntax and to also use .equals() instead of == (the == operator is broken in obscure ways and should be avoided so you don't have hard-to-find bugs).

##Fetch Subscription Data from SFDC Custom object
#set( $SubscriptionName = $Subscription__cList.get(0).Name )
#set( $SubscriptionStatus = $Subscription__cList.get(0).Subscription_Status__c )
#set( $AutoRenew = $Subscription__cList.get(0).AutoRenew__c )
#set( $NextRenewalDate = $Subscription__cList.get(0).NextRenewalDate__c )

##Conditions 
#if ( $SubscriptionStatus.equals("Active") && $AutoRenew.equals("true") )
${SubscriptionName}
#else
Test Fallback Name
#end

Now, let's think about the logic. You're fetching the first item (.get(0), which is the same as [0] by the way) in the list of objects.

That won't work: you need to iterate over the list and keep only the items you want. There's no explicit collection filtering mechanism. You #foreach over the list and then pull out the ones you want. For example:

#set( $interestingItems = [] )
#foreach( $item in $originalItem__cList )
#if( $item.field1.equals("interestingValue") )
#set( $void = $interestingItems.add($item) )
#end
#end

Then your smaller list $interestingItems holds the ones you pulled out.

You'll also want to read my seminal post on date/datetime math in VTL:

https://blog.teknkl.com/velocity-days-and-weeks/

[Mike_St_Louis]

Hey Sanford - 
So using your methodology - we were able to get the script below to produce the results from the SFDC Subscription Object that we needed, so thank you for that...

#set( $interestingItems = [] )
#foreach( $item in $Subscription__cList )
#if( $item.Subscription_Status__c.equals("Active") )
#if( $item.AutoRenew__c.equals("1") )
#if( $calNow.compareTo($renewSubDate) < 90)
#set( $void = $interestingItems.add($item) )

But how would we go about turning the results like this below into a listed format by just "Subscription Name" to appear in the token in the email?

{Name=A-S00013411, Subscription_Status__c=Active, AutoRenew__c=1, NextRenewalDate__c=2020-09-21, SubscriptionEndDate__c=2020-09-21} {Name=A-S00013675, Subscription_Status__c=Active, AutoRenew__c=1, NextRenewalDate__c=2020-10-05, SubscriptionEndDate__c=2020-10-05}

[SanfordWhiteman]

${display.list($interestingItems,"<br>","<br>","Name")}

[Mike_St_Louis]

Ok, list is now showing, but for some reason, we have double of one "SubscriptionName" and I have been trying to get it to only display the "Name" of the subscription that is in a specific date range. I have tried using ".compareTo" and "$date.difference" and it still just pulls in all those in "$interestingItems", and the first result twice as shown below as it looks in the email...

A-S00013411 A-S00013411
A-S00013675

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Denver") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $renewDate = $convert.parseDate($Subscription__cList.get(0).NextRenewalDate__c,'yyyy-MM-dd') ) 
#set( $renewSubDate = $convert.toCalendar($renewDate) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
#set( $Subscription =  $Subscription__cList.get(0).Name )
#set( $Status = $Subscription__cList.get(0).Subscription_Status__c)
#set( $AutoRenew = $Subscription__cList.get(0).AutoRenew__c)

#set( $interestingItems = [] )
#foreach( $item in $Zuora__Subscription__cList )
#if( $item.Subscription_Status__c.equals("Active") )
#if( $item.AutoRenew__c.equals("1") )
#set( $void = $interestingItems.add($item) )

#if( $date.difference($calNow,$renewSubDate).getDays() <=45 )
${display.list($interestingItems,"<br>","<br>","Name")}
#else
NOPE
#end
#end
#end
#end

[SanfordWhiteman]

Your $renewDate (and in turn $renewSubDate) is created only once, from the first subscription in the list.

You need to create a new comparable date in the loop for that item's NextRenewalDate__c.

[Malay_Chakrabo1]

Thanks Sanford for the guidance we were able to build the intended logic based on your methodology. 

However, the script is adding an additional space after the output. Thus, I was hoping to check if you could point out to us what might be creating the extra whitespace? 

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Denver") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $renewDate = $convert.parseDate($Subscription__cList.get(0).NextRenewalDate__c,'yyyy-MM-dd') ) 
#set( $renewSubDate = $convert.toCalendar($renewDate) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
#set( $Subscription =  $Subscription__cList.get(0).Name )
#set( $Status = $Subscription__cList.get(0).Subscription_Status__c)
#set( $AutoRenew = $Subscription__cList.get(0).AutoRenew__c)

#set( $interestingItems = [] )
#foreach( $item in $Zuora__Subscription__cList )
#if( $item.Subscription_Status__c.equals("Active") )
#if( $item.Zuora__AutoRenew__c.equals("1") )
#set( $void = $interestingItems.add($item) )

#set( $nextRenewDate = $convert.parseDate($item.Zuora__NextRenewalDate__c,'yyyy-MM-dd') ) 
#set( $nextRenewSubDate = $convert.toCalendar($nextRenewDate) )


#if( $date.difference($calNow,$nextRenewSubDate).getDays() == 44 )

#set( $FRIENDLY_24H_DATETIME_WITH_FRIENDLY_TZ = "dd MMMM yyyy" )
${date.format(
  $FRIENDLY_24H_DATETIME_WITH_FRIENDLY_TZ,
  $nextRenewSubDate
)}
#elseif( $date.difference($calNow,$nextRenewSubDate).getDays() == 13 )

#set( $FRIENDLY_24H_DATETIME_WITH_FRIENDLY_TZ = "dd MMMM yyyy" )
${date.format(
  $FRIENDLY_24H_DATETIME_WITH_FRIENDLY_TZ,
  $nextRenewSubDate
)}
#end
#end
#end
#end

[SanfordWhiteman · Accepted solution]

Well, Velocity is stricly space-preserving.

So even those line breaks in your code are line breaks in the email (whether or not they affect layout, due to the HTML's whitespace-swallowing behavior).

To suppress the final line break, do:

${date.format(
  $FRIENDLY_24H_DATETIME_WITH_FRIENDLY_TZ,
  $nextRenewSubDate
)}##