Skip to main content
Anonymous
Mar 20, 2018 6:54 AM UTC
Solved

Calculate number of years based on a date field

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.
SanfordWhiteman
SanfordWhitemanAccepted solution
User Group Leader
Mar 20, 2018 7:36 AM UTC

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/

View in thread

18 replies

SanfordWhiteman
User Group Leader
SanfordWhitemanUser Group LeaderAccepted solution
User Group Leader
Mar 20, 2018 7:36 AM UTC

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/

Translate
    Anonymous
    Mar 20, 2018 10:20 AM UTC

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

    Translate
    Grégoire_Miche2
    Grégoire_Miche2
    Level 10
    Mar 20, 2018 11:01 AM UTC

    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

    Translate
    Victor_Herrero
    Victor_Herrero
    Level 5
    Mar 26, 2019 1:36 PM UTC

    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!

    Translate
    SanfordWhiteman
    User Group Leader
    SanfordWhitemanUser Group Leader
    User Group Leader
    Mar 26, 2019 4:10 PM UTC

    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.

    Translate
    Victor_Herrero
    Victor_Herrero
    Level 5
    Mar 27, 2019 7:52 AM UTC

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

    Translate
    samsellars
    Level 1
    May 16, 2022 10:28 PM UTC

    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

    Translate
    SanfordWhiteman
    User Group Leader
    SanfordWhitemanUser Group Leader
    User Group Leader
    May 16, 2022 10:36 PM UTC
    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

    Translate
    Terms & ConditionsAccessibility statement

    Loading footer...