Re: Repost of List based on updated date

Michael_McGowa1 · ‎03-18-2020

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.

SanfordWhiteman · ‎03-18-2020

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_McGowa1 · ‎03-19-2020

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 · ‎03-19-2020

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

Michael_McGowa1 · ‎03-20-2020

That worked, thanks. Now, for the second bit. For the code below, I added the timezones and the necessary elements mentioned in your article. Then I set the parameter for Last Month, Last Week, and then tried to format them along with the "updatedDate" in the sPAdActivity_cList table.

Then in the first foreach loop, I am having the completed date matching either Last Week or in this case, last month.

However, now it not working again.

#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

SanfordWhiteman · ‎03-21-2020

Start by fixing the fatal syntax errors at the top, these are missing closing braces and parentheses:

#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

However you shouldn't be using braces (formal references) here at all, so reduce to only parentheses:

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

Michael_McGowa1 · ‎03-23-2020

That didn't work for me. I took out the braces and replaced them with parentheses as you instructed. I have tried variations but here is where I began.

If I could ask you to produce the code that would give me the results for last week (S-S) or Last Month I would appreciate it. Below is the 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( $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.replaceAll("^${esc.q}|${esc.q}$","")}</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

SanfordWhiteman · ‎03-23-2020

Syntax error on line 26, extra dangling {.

Syntax error on lines 33-43, an object literal needs to be wrapped in {}, not (). Same for the inner object literals inside that object, those are always denoted by {}.

Correct the extra $ on line 55 as well, that's technically not a syntax error but Velocity will most certainly not behave as desired with this syntax.

I'd rather not just write the whole thing for you, that's not a good way to learn the precision necessary to maintain this code.

Michael_McGowa1 · ‎03-24-2020

The dates are working correctly and being formatted the way I would like them. It seems that the last sticking point is the this line

#if( $activity.activityStatus.equals("Completed") && ($UpdatedDate_formatted == $myLastMonth) )

Seems straight forward but when I test it, the page comes out blank

SanfordWhiteman · ‎03-24-2020

Can you post the most current code?