Sorting a Marketo Custom Object with Velocity

[Wyatt_Bales4]

This topic has been briefly touched on a few other threads (e.g. Velocity Snippets #1: Sorts and seeks​) with Sanford Whiteman but hoping to further clarify.

Use Case: Display a table for each store locations' order data if the store's order data is yesterday.

Design: API upsert into Marketo Custom Object with triggered smart campaign which fires email

Issue: Sometimes a franchise owner may sell a store an no longer be upserted into Marketo. Thus, old order data will live in the custom object. Our #foreach loop will bring back the oldest record instead of the custom object row which was updated.

I realize from Kenny Elkington's response (Velocity: Sort Opportunities by date​), you can use $TriggerObject but we still need to load multiple rows instead of just the most recently updated. Therefore I believe a #foreach loop is still needed.

I understand the .get(0) finds the first/oldest row in the custom object so we've dropped that but still looking for a way to sort and/or only load rows which have an order date = yesterday.

Appreciate the help and please forgive my lack of Velocity understanding.

#foreach (${mcDonaldsOrderSummaryV2_c.storeNumber} in ${mcDonaldsOrderSummaryV2_cList})
##CONVERT CUSTOM OBJECT STRING DATE TO DATE FOR COMPARISON
#set ($OrderDate = ${mcDonaldsOrderSummaryV2_cList.get(0).dateOfOrder})
#set ($parsedOrderDate = $convert.parseDate($OrderDate, "yyyy-MM-dd"))
##SET LOCAL VARIABLE FOR A YESTERDAYS DATE
#set ($TodaysDate = $date)
#set ($formattedTodaysDate = $date.format("yyyy-MM-dd", $TodaysDate))
#set ($dayago = $date.calendar)
$dayago.add(5,-1)
##A Week Ago's Date: $date.format('yyyy-MM-dd', $dayago.time)<br>
##SET LOCAL VARIABLE WHICH IS THE DIFFERENCE BETWEEN ORDER DATE AND A WEEK AGO
#set ($dateDifference = $date.difference($parsedOrderDate, $dayago).getDays())
##IF THE ORDER DATE IS GREATER THAN OR LESS THAN A DAY, DON'T BRING IT BACK
#if ($dateDifference > 2)
#elseif ($dateDifference <= 2)

[SanfordWhiteman · Accepted solution]

Hi Wyatt,

First, a few general pointers:

• Using #foreach( $object.property in $list ) is confusing because Velocity will ignore the property. Just use #foreach( $object in $list )
• Strive to avoid formal notation (${object} or ${object.property}) unless you're outputting content. Formal notation can cause syntax errors when refectoring, like here​
• Always remember that once they are "living" dates (Java Date/Calendar objects), dates have times. And times have timezones. This can lead to many confusing situations if you're not meticulous about declaring timezones at the top of your scripts, as I do here​

For your case, you're not really sorting, you're filtering (it doesn't matter what sort order the matching objects have within the list, it matters whether the date matches your target date).

While Velocity doesn't have a formal collection filtering function, you can use a #foreach and when you find a match, add it to a new list. In this case the new, filtered list is $yesterdaysOrders.

## set up date config/constants
#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#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( $ISO8601Date = "yyyy-MM-dd" )
## the real work
#set( $yesterdaysOrders = [] )
#foreach( $mcDonaldsOrderSummaryV2_c in $mcDonaldsOrderSummaryV2_cList )
#set( $calOrderDate = $convert.toCalendar(
  $convert.parseDate(
    $mcDonaldsOrderSummaryV2_c.orderDate, 
    $ISO8601Date, 
    $defaultLocale, 
    $defaultTimeZone 
  )
) )
#if( $convert.toInteger($date.whenIs($calOrderDate).getDays()).equals(-1) ) 
#set( $ret = $yesterdaysOrders.add($mcDonaldsOrderSummaryV2_c) )
#end
#end
All yesterday's orders are: $yesterdaysOrders

[Wyatt_Bales4]

Humbly appreciate the help, Sanford Whiteman​. You saved us quite the headache over here. I didn't realize you could throw objects into an array and then reference the fields within said objects normally. Fantastic.

For others who are struggling with how to sort or filter out stale custom object records and load tables (e.g. load my transaction history for the last 3 days across all locations), here's a code snippet we used:

##SETUP DATE CONFIG/CONSTRAINTS
#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Los_Angeles") )  
#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( $ISO8601Date = "yyyy-MM-dd" )  
##SET ARRAY BUT ONLY PULL BACK ROWS WITH DATA IN THE PAST 2 DAYS 
#set( $yesterdaysOrders = [] )  
#foreach( $mcDonaldsOrderSummaryV2_c in $mcDonaldsOrderSummaryV2_cList )  
#set( $calOrderDate = $convert.toCalendar(  
  $convert.parseDate(  
    $mcDonaldsOrderSummaryV2_c.dateOfOrder,   
    $ISO8601Date,   
    $defaultLocale,   
    $defaultTimeZone   
  )  
) )  
#if( $convert.toInteger($date.whenIs($calOrderDate).getDays()).equals(-2) )   
#set( $ret = $yesterdaysOrders.add($mcDonaldsOrderSummaryV2_c) )  
#end  
#end  
#foreach( $mcDonaldsOrderSummaryV2_c in $yesterdaysOrders )