Calculate number of years based on a date field | Community
Skip to main content
A
Anonymous
March 20, 2018
Solved

Calculate number of years based on a date field

  • March 20, 2018
  • 3 replies
  • 7807 views

We are looking to create an automation in which the client has a date field called Client Anniversary that basically shows the joining date.

The automation sends out an email on the anniversary which is fine (we have built) but inside the email there is a part where it says:

Can you believe it has been X year(s) since you joined our family?

That X field needs to basically come from calculating This year - Client Anniversary year and give us a number (ie. 1, 2, or 3 ...)

Has anyone ever worked with something like this and know how it can be achieved?

Thanks.

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

Use a Velocity (Email Script) token:

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $calJoinDate = $convert.toCalendar(
  $convert.parseDate(
    $lead.JoinDate, 
    $ISO8601DateOnly, 
    $defaultLocale, 
    $defaultTimeZone 
  )
) )
#set( $differenceInYears = $date.difference($calJoinDate,$calNow ).getYears() )
#set( $friendlyLabel = $display.plural($convert.toInteger($differenceInYears),"year") )
You joined us ${differenceInYears} ${friendlyLabel} ago!

Where $lead.JoinDate is the joining date.

More at http://blog.teknkl.com/velocity-days-and-weeks/

3 replies

SanfordWhiteman
SanfordWhitemanAccepted solution
Level 10
March 20, 2018

Use a Velocity (Email Script) token:

#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( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $calJoinDate = $convert.toCalendar(
  $convert.parseDate(
    $lead.JoinDate, 
    $ISO8601DateOnly, 
    $defaultLocale, 
    $defaultTimeZone 
  )
) )
#set( $differenceInYears = $date.difference($calJoinDate,$calNow ).getYears() )
#set( $friendlyLabel = $display.plural($convert.toInteger($differenceInYears),"year") )
You joined us ${differenceInYears} ${friendlyLabel} ago!

Where $lead.JoinDate is the joining date.

More at http://blog.teknkl.com/velocity-days-and-weeks/

    A
    Anonymous
    March 20, 2018

    Thank you. I am not exactly sure where I need to place/use this code actually.

    Grégoire_Miche2
    Grégoire_Miche2
    Level 10
    March 20, 2018

    Hi Hossein,

    Go the program containing the email, and click the "tokens" tab, then drag & drop a script token. Name it as you want and paste Sanford's code there.

    -Greg

    Victor_Herrero
    Victor_Herrero
    Level 5
    March 26, 2019

    Hi @Sanford Whiteman​,

    Thanks for sharing this.

    I managed to modify it to work (on a test email) with our system but I am having trouble passing the calculated value into an integer field.

    Is there any way to get around this? I take it the problem is the "email script token" is understood as a "text" input type.

    Thanks!

    SanfordWhiteman
    SanfordWhiteman
    Level 10
    March 26, 2019

    You can't use Velocity output anywhere but in the email assembly context. It's never calculated at the flow point you're showing, so even if the red squiggly didn't show there wouldn't be any data.

    If you need complex calculations like you can do in Velocity, but need to persist the result permanently to a lead field, you need a webhook solution.

    Victor_Herrero
    Victor_Herrero
    Level 5
    March 27, 2019

    Thank you fr confirming that. I was having a look at a formula field, but it seems they can't be used in smart campaigns and will not be exported. Those would be the main uses I would have for this field...

    S
    samsellars
    Level 1
    May 16, 2022

    Hello Marketo Experts:

     

    In the original post the author says, "The automation sends out an email on the anniversary which is fine (we have built) . . ." 

    We are struggling to find a way to populate or using a computed column that holds a date inside Marketo itself.  Not populating the date ourselves with the REST API.  It is nearly the same scenario as this - we need have an anniversary date (birthday in this case) in a column that we can use in a campaign run (trigger).  Would it be possible to run some kind of trigger that would populate the field of all records with the month, day, and current year (birthday)?  Ideally, this would happen on a custom object.

     

    Best regards,
    Sam

    SanfordWhiteman
    SanfordWhiteman
    Level 10
    May 16, 2022
    Would it be possible to run some kind of trigger that would populate the field of all records with the month, day, and current year (birthday)?  Ideally, this would happen on a custom object.

    Not seeing why you‘d want a Custom Object to store the anniversary of a field (Date of Birth) that itself is a flat field on a person. In any case there’s no way to update COs from flows.

     

    You’ll find a couple of options here: https://nation.marketo.com/t5/product-discussions/building-a-birthday-anniversary-email-without-using-wait-step-gt/m-p/321037

    Terms & ConditionsAccessibility statement

    Loading footer...