Display Due Date Custom Token in proper format

LCENTENO · ‎06-12-2023

Greetings Community,

I feel like I am circling around the airport on this one, and I would love to bring this baby in for a landing.

Part 1:

We want to let our members know when their next payment is due. I have checked off the Product and NextDueDate checkboxes in the CCUAccount Custom object.

I put this code below into Velocity script which is supposed to show the Next Due Date based on their product code:

#set( $interestingProductCodes = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProductCodes.contains($account.productCode) )
${account.getNextDueDate}
#end
#end

However, when I test the token in an email using contacts, nothing shows up. Is there any code I am missing here?

Part Two: The "NextDueDate" a date and time field displays the due date in this format: YYYY-MM-DDThh:mm:ssTZD. What code can I apply to the above to change it to show the date as MM-DD-YYYY?

Thanks so much in advance.

Darshil_Shah1 · ‎06-20-2023

Ahaa- you don't have the "T" in the unformatted nextDueDate output, you have a space instead! Could you try the below script:

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateOnlyMonthFirst = "MM-dd-yyyy" )
#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.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )

#set( $interestingProduct = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProduct.contains($account.product) )
#set( $nextDueDateFormatted = $convert.toCalendar(
  $convert.parseDate(
    $account.nextDueDate,
    $ISO8601DateTimeWithSpace, 
    $defaultLocale, 
    $defaultTimeZone
  )
))
${date.format(
  $ISO8601DateOnlyMonthFirst,
  $nextDueDateFormatted,
  $defaultLocale,
  $defaultTimeZone
)}
#end
#end

View solution in original post

Darshil_Shah1 · ‎06-12-2023

You gotta show us the output of the CO list for the person record first to be able to troubleshoot(as Sandy says). That's the first step you gotta follow when the output isn't something that you hoped it'd have been. Additionally, do you have duplicate records in your database? It's worth mentioning that Preview By Person de-duplicate records by email address. This can lead to you seeing data for a different person record in the person profile page, and expecting a particular output, whereas the Email Preview running the script on a different person record altogether (this is one of the reasons why Preview By List is preferred instead). Re- your Part 2 question, you'd have to fomat the calender object to be able to display the output in the desired format. There are plenty of threads on the community already where we've discussed that.

SanfordWhiteman · ‎06-12-2023

I put this code below into Velocity script which is supposed to show the Next Due Date based on their product code:

Hmm, how is it “based on” their product code? Do you mean “if their product code is in the list of interesting codes”? Always best to be explicit.

Is getNextDueDate the Velocity name of the field whose friendly name is NextDueDate? How have you determined that Velocity name? More important what does the whole $cCUAccount_cList list look like when you output it (this is an absolutely key part of troubleshooting)?

LCENTENO · ‎06-13-2023

Hi @SanfordWhiteman , yes I am referring to if their product code is in the interesting codes list that I provided. Yes, NextDueDate is the field name within the CCUAccount CO.

As far as your last question "More important what does the whole $cCUAccount_cList list look like when you output it (this is an absolutely key part of troubleshooting)?" I am not sure what you are asking.

Darshil_Shah1 · ‎06-13-2023

What output do you get when you just add ${cCUAccount_cList} in your email script token? This will display the list of all the custom object records associated with that person record.

LCENTENO · ‎06-13-2023

Hi Darshill,

Do you mean adding it to the code I already displayed previously?

#set( $interestingProductCodes = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProductCodes.contains($account.productCode) )
${account.getNextDueDate}
#end
#end

If so, would I add it after the ${account.getNextDueDate} line?

Darshil_Shah1 · ‎06-13-2023

You can add a line to display the list with the existing script token content but to get away from clutter, just have a script token with ${cCUAccount_cList}, and show us the output of it (of course, after masking sensitive info) for one of the person records you don't seem to get the correct results for your original script (as you've mentioned in your original question).

LCENTENO · ‎06-14-2023

Hi Darshil,

The output that shows with the ${cCUAccount_cList} script token is below (used zeroes to mask sensitive info displayed):

[{product=VISA Signature Cash Rebate, nextDueDate=null}, {product=NEW AUTO CE12, nextDueDate=0000-00-00 00:00:00}, {product=Membership Savings, nextDueDate=null}, {product=Rewards Checking, nextDueDate=null}, {product=Rewards Checking, nextDueDate=null}, {product=Rewards Checking, nextDueDate=null}, {product=Holiday Club Savings, nextDueDate=null}, {product=Membership Savings, nextDueDate=null}, {product=Membership Savings, nextDueDate=null}, {product=USED AUTO CE 12, nextDueDate=null}]

Darshil_Shah1 · ‎06-14-2023

Agreed with Sandy! For reference, pull the field into the editor from the object tree on the right (snapshot below) to get the correct field name instead of typing it out. I hope this helps.

LCENTENO · ‎06-15-2023

Hey Darshill,

I think I am on the right track here. I applied your advice along with Sandford's advice about naming the fields correctly. The code I placed is below:

#set( $interestingProduct = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProduct.contains($account.product) )
${cCUAccount_cList.get(0).nextDueDate}
#end
#end

For line 4, I pulled the NextDueDate field from the object tree on the right as you instructed. When I place the token in the email this is what displayed in preview mode.

Is there a reason why it's displaying the name of the field pulled in and not the actual due date?

Darshil_Shah1 · ‎06-15-2023

You should reference the loop variable $account, and not the $cCUAccount_cList.get(0) while displaying the nextDueDate field. Also, ensure that the nextDueDate custom object field is ticked in the object tree. I recommended pulling the field from the object tree to ensure that you have the correct velocity field name (in your case, nextDueDate) in the script.

LCENTENO · ‎06-16-2023

Hi Darshil,

Looks like your suggestion worked.

#set( $interestingProduct = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProduct.contains($account.product) )
${account.nextDueDate}
#end
#end

One last thing, the date and time displays differently due to the Next Due Date field being in a Date format, which Marketo displays by default as 2010-05-07T15:41:32-05:00. However, I was playing with the code below and I am thinking this is right to display it as mm-dd-yyyy (I hope).

$date.format('MMM dd, yyyy hh:mm a', ${convert.parseDate(${lead.nextDueDate}, 'MM-dd-yyyy')}

First and foremost, is this right? Second, would I simply place this after line 4 in the full code above?

Darshil_Shah1 · ‎06-16-2023

You should include the date-time boilerplate in your code. Could you try the below script and let us know if it works?

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateOnlyMonthFirst = "MM-dd-yyyy" )
#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.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )

#set( $interestingProduct = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProduct.contains($account.product) )
#set( $nextDueDateFormatted = $convert.toCalendar(
  $convert.parseDate(
    $account.nextDueDate,
    $ISO8601DateTime, 
    $defaultLocale, 
    $defaultTimeZone
  )
))
${date.format(
  $ISO8601DateOnlyMonthFirst,
  $nextDueDateFormatted,
  $defaultLocale,
  $defaultTimeZone
)}
#end
#end

This is a great place to learn about using date time in velocity.

LCENTENO · ‎06-16-2023

Hi Darshil,

I applied the code and this was the result:

Darshil_Shah1 · ‎06-16-2023

Yeah- this has to do with the input format. Basically, the input time format isn't matching the time format we have in the $convert.parseDate function. Can you try the below script?

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateOnlyMonthFirst = "MM-dd-yyyy" )
#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.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
#set( $ISO8601DateTimeTZ = "yyyy-MM-dd'T'HH:mm" )
#set( $nextDueDateFormatted = $convert.toCalendar(
  $convert.parseDate(
    $account.nextDueDate,
    $ISO8601DateTimeTZ, 
    $defaultLocale, 
    $defaultTimeZone
  )
))
${date.format(
  $ISO8601DateOnlyMonthFirst,
  $nextDueDateFormatted,
  $defaultLocale,
  $defaultTimeZone
)}

LCENTENO · ‎06-16-2023

Hi Darshil,

I applied the code below:

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateOnlyMonthFirst = "MM-dd-yyyy" )
#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.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )

#set( $interestingProduct = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProduct.contains($account.product) )
#set( $nextDueDateFormatted = $convert.toCalendar(
  $convert.parseDate(
    $lead.FirstName,
    $ISO8601DateTimeTZ, 
    $defaultLocale, 
    $defaultTimeZone
  )
))
${date.format(
  $ISO8601DateOnlyMonthFirst,
  $nextDueDateFormatted,
  $defaultLocale,
  $defaultTimeZone
)}
#end
#end

The result looks to be the same.

Darshil_Shah1 · ‎06-16-2023

You haven't defined the $ISO8601DateTimeTZ variable before using it in the $convert.parse function. Also, please use the $account.nextDueDate as the input date field. Could you please try the below script:

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateOnlyMonthFirst = "MM-dd-yyyy" )
#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.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
#set( $ISO8601DateTimeTZ = "yyyy-MM-dd'T'HH:mm" )
#set( $nextDueDateFormatted = $convert.toCalendar(
  $convert.parseDate(
    $account.nextDueDate,
    $ISO8601DateTimeTZ, 
    $defaultLocale, 
    $defaultTimeZone
  )
))
${date.format(
  $ISO8601DateOnlyMonthFirst,
  $nextDueDateFormatted,
  $defaultLocale,
  $defaultTimeZone
)}

LCENTENO · ‎06-16-2023

Hi Darshil,

I placed the code in

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateOnlyMonthFirst = "MM-dd-yyyy" )
#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.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
#set( $ISO8601DateTimeTZ = "yyyy-MM-dd'T'HH:mm" )

#set( $interestingProduct = ["NEW AUTO CE12","SECURED OTHER GOODS CE12","REC VEHICLE OE","SECURED OTHER GOODS OE","PCU USED VEHICLE","PCU USED VEHICLE JR MEETING","PCU NEW VEHICLE","NEW AUTO ALTERN IL","USED AUTO ALTERN IL","USED AUTO ALTERN WI","USED AUTO OE","NEW AUTO OE","MOTORCYCLE CE12","REC VEHICLE CE12"] )
#foreach( $account in $cCUAccount_cList )
#if( $interestingProduct.contains($account.product) )
#set( $nextDueDateFormatted = $convert.toCalendar(
  $convert.parseDate(
    $account.nextDueDate,
    $ISO8601DateTimeTZ, 
    $defaultLocale, 
    $defaultTimeZone
  )
))
${date.format(
  $ISO8601DateOnlyMonthFirst,
  $nextDueDateFormatted,
  $defaultLocale,
  $defaultTimeZone
)}
#end
#end

It still looks to be the same result:

Darshil_Shah1 · ‎06-16-2023

Well, the format "yyyy-MM-dd'T'HH:mm" works for me when I tested it with the input "2010-05-07T15:41:32-05:00" (see the snapshot below). Additionally, see this very similar post where the user also wanted to parse the exact datetime format. Do you want to try the "yyyy-MM-dd'T'HH:mm:ss" format?

LCENTENO · ‎06-16-2023

Hi Darshil,

Not sure if this is a factor, but I looked at how our member next due date fields are displayed and this is how it shows in the field:

Do you think this may have a factor in why the code isn't working?

SanfordWhiteman · ‎06-16-2023

The way it shows in the UI isn’t relevant (unless it’s a String type field). What matters is the actual value seen by Velocity.