Repost of List based on updated date

Forum|Forum|6 years ago
March 18, 2020
1 reply
4175 views

Starting a new thread for this question. With a lot of help I have a Velocity script that will list all the activities a member has taken and add up the points that they have earned. The code is here:

## filter sorted list into new list
#set( $completedActivityList = [] )
#foreach( $activity in $sorter.sort($sPAdActivity_cList, ["updatedAt:desc"]) )
#if( $activity.activityStatus.equals("Completed") )
#set( $void = $completedActivityList.add($activity) )
#end
#end
#if( !$completedActivityList.isEmpty() )
#set( $alternateRowColors = ["#e6eff8", "#ffffff"] )
#set( $totalPoints = $math.getTotal( $completedActivityList, "points" ) )
## open <table>
<table width="600" border="0" cellspacing="0" cellpadding="0" style="border:1px solid #000">
<tr>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#fff; padding-top:5px; padding-bottom:5px;"><strong>Activity</strong></td>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#FFF; padding-top:5px; padding-bottom:5px;"><strong>Points Earned</strong></td>
</tr>
## iterate filtered list
#foreach( $activity in $completedActivityList )
#set( $rowColor = $alternateRowColors[$math.mod($foreach.index,2)] )
<tr>
 <td bgcolor="${rowColor}" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#000; padding-top:5px; padding-bottom:5px;">${activity.description}</td>
 <td bgcolor="${rowColor}" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#000; padding-top:5px; padding-bottom:5px;">${activity.points}</td>
</tr>
#end
## close </table>
<tr>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; padding-top:5px; padding-bottom:5px; color:#fff;"><strong>Total Points Earned</strong></td>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#fff; padding-top:5px; padding-bottom:5px;"><strong>$totalPoints</strong></td>
</tr>
</table>
#end

I need make some additions to the code and have tried and failed to do the following.

Only lists the activities completed in the past month or the past week. It would be preferable if this could be done programmatically since this would preferably be an automated email.
There is an issue off quotation marks around the title of the activity. It’s listed that way in the CRM, I do not know why. I need to find a way to take those quotation marks out. Is there a trim command in Velocity script?

If anyone could help, I would appreciate it.

Marketo

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

SanfordWhiteman

Level 10

Only lists the activities completed in the past month or the past week. It would be preferable if this could be done programmatically since this would preferably be an automated email.

All the datetime math things you need are at https://blog.teknkl.com/velocity-days-and-weeks/

There is an issue off quotation marks around the title of the activity. It’s listed that way in the CRM, I do not know why. I need to find a way to take those quotation marks out. Is there a trim command in Velocity script?

"trim" means whitespace, so that isn't the way I would put it.

You can remove any characters using a a regexp.

#set( $yourFieldWithoutWrappedDQ = $yourField.replaceAll("^${esc.q}|${esc.q}$","") )

Michael_McGowa1Author

Level 3

Been working on this all day and not making any progress.

First, trying to get rid of the quotes, I have tried different versions of the following. It is still putting quotes around the description.

## filter sorted list into new list
#set( $completedActivityList = [] )
#foreach( $activity in $sorter.sort($sPAdActivity_cList, ["updatedAt:desc"]) )
#if( $activity.activityStatus.equals("Completed") )
#set( $void = $completedActivityList.add($activity) )
#end
#end
#if( !$completedActivityList.isEmpty() )
#set( $alternateRowColors = ["#e6eff8", "#ffffff"] )
#set( $totalPoints = $math.getTotal( $completedActivityList, "points" ) )
## open <table>
<table width="600" border="0" cellspacing="0" cellpadding="0" style="border:1px solid #000">
<tr>
 <td bgcolor="#005daa" valign="middle" style="font-family:Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#fff; padding-top:5px; padding-bottom:5px;"><strong>Activity</strong></td>
 <td bgcolor="#005daa" valign="middle" style="font-family:Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#FFF; padding-top:5px; padding-bottom:5px;"><strong>Points Earned</strong></td>
</tr>
## iterate filtered list
#foreach( $activity in $completedActivityList )
#set( $sPAdActivity_cList.description = $myDescription.replaceAll("^${esc.q}|${esc.q}$","") )
#set( $rowColor = $alternateRowColors[$math.mod($foreach.index,2)] )
<tr>
 <td bgcolor="${rowColor}" valign="middle" style="font-family:Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#000; padding-top:5px; padding-bottom:5px;">${activity.description}</td>
 <td bgcolor="${rowColor}" valign="middle" style="font-family:Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#000; padding-top:5px; padding-bottom:5px;">${activity.points}</td>
</tr>
#end
## close </table>
<tr>
 <td bgcolor="#005daa" valign="middle" style="font-family:Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; padding-top:5px; padding-bottom:5px; color:#fff;"><strong>Total Points Earned</strong></td>
 <td bgcolor="#005daa" valign="middle" style="font-family:Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#fff; padding-top:5px; padding-bottom:5px;"><strong>$totalPoints</strong></td>
</tr>
</table>
#end

Second, taking the original code and trying to get the completed activities from last month or last week, I looked over the article but it keeps giving me errors. Here is that code:

#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( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )

#set( $LastWeek = 
${date.format( "yyyy-MM",
  $calNow.subtract($calConst.DATE,7),
  $defaultLocale,
  $defaultTimeZone


#set( $LastMonth = 
${date.format( "yyyy-MM",
  $calNow.subtract($calConst.MONTH,1),
  $defaultLocale,
  $defaultTimeZone


#set( $theCurrentMonth = 
${date.format( "yyyy-MM",
  $calNow,
  $defaultLocale,
  $defaultTimeZone
)})
#set( $dateOptions = {    
  "formats" : {    
    "userin" : "yyyy-MM-dd HH:mm:ss",    
    "userout" : "yyyy-MM"    
  },    
  "timezones" : {    
    "userin" : "America/New_York",    
    "userout" : "America/New_York"    
  },    
  "locale" : $date.getLocale()    
} )

#set( $updatedDate = ${sPAdActivity_cList.get(0).updatedAt} )
#set ( $formattedUpdatedDateIn = $convert.parseDate(
$updatedDate,
  $dateOptions.formats.userin,    
  $dateOptions.locale,    
  $date.getTimeZone().getTimeZone($dateOptions.timezones.userin)    
) ) 

#set( $UpdatedDate_formatted = $date.format(    
  $dateOptions.formats.userout,    
  $$formattedUpdatedDateIn,    
  $dateOptions.locale,    
  $date.getTimeZone().getTimeZone($dateOptions.timezones.userout)    
) ) 


## filter sorted list into new list
#set( $completedActivityList = [] )
#foreach( $activity in $sorter.sort($sPAdActivity_cList, ["updatedAt:desc"]) )
#if( $activity.activityStatus.equals("Completed") && $UpdatedDate_formatted.equals("${LastMonth}") )
#set( $void = $completedActivityList.add($activity) )
#end
#end
#if( !$completedActivityList.isEmpty() )
#set( $alternateRowColors = ["#e6eff8", "#ffffff"] )
#set( $totalPoints = $math.getTotal( $completedActivityList, "points" ) )
## open <table>
<table width="600" border="0" cellspacing="0" cellpadding="0" style="border:1px solid #000">
<tr>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#fff; padding-top:5px; padding-bottom:5px;"><strong>Activity</strong></td>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#FFF; padding-top:5px; padding-bottom:5px;"><strong>Points Earned</strong></td>
</tr>
## iterate filtered list
#foreach( $activity in $completedActivityList )
#set( $rowColor = $alternateRowColors[$math.mod($foreach.index,2)] )
<tr>
 <td bgcolor="${rowColor}" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#000; padding-top:5px; padding-bottom:5px;">${activity.description}</td>
 <td bgcolor="${rowColor}" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#000; padding-top:5px; padding-bottom:5px;">${activity.points}</td>
</tr>
#end
## close </table>
<tr>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; padding-top:5px; padding-bottom:5px; color:#fff;"><strong>Total Points Earned</strong></td>
 <td bgcolor="#005daa" valign="middle" style="font-family:Arial, Helvetica, sans-serif; font-size:13px; padding-left:5px; padding-right:5px; color:#fff; padding-top:5px; padding-bottom:5px;"><strong>$totalPoints</strong></td>
</tr>
</table>
#end

Don't know what I doing wrong. Hitting my ceiling which admittedly is pretty low.

SanfordWhiteman

Level 10

First, trying to get rid of the quotes, I have tried different versions of the following. It is still putting quotes around the description.

#foreach( $activity in $completedActivityList )
#set( $sPAdActivity_cList.description = $myDescription.replaceAll("^${esc.q}|${esc.q}$","")

You're assigning a property on the whole List, which doesn't actually have properties, and even if it did, you never try to access it again.

As you iterate over the list, set each $activity.description to $activity.description.replaceAll(pattern,replacement).

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded