Calculate number of years based on a date field

[Anonymous]

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.

[SanfordWhiteman · Accepted solution]

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/

[Anonymous]

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

[Grégoire_Miche2]

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

[SanfordWhiteman]

And make sure to check off your JoinDate field in the tree on the right-hand side of the Script Editor. And change the name of that variable accordingly in the script.

[Anonymous]

Thanks. I have done as mentioned. I have a small concern. The Client_Anniversary__c is under Account not Lead, however when I drag and drop it puts the lead. in front, would that be an issue?

Thanks.

[SanfordWhiteman]

Is the data there? That's the question.

Also, you can take out the {} from the variable name in this context, it just makes it harder to read.

[Sarah_Greig2]

I was looking for something based on date e.g if x field = system date then trigger x. Would this need velocity scripting? I tried to use if field = system date token but it wouldn't accept it.

[SanfordWhiteman]

You can't use Velocity to trigger campaigns. Velocity executes when an email is being assembled, i.e. after leads have been qualified for a send.

If you want to compare an arbitrary field to today's date you'd need a webhook.

[Sarah_Greig2]

So I have a renewal date stored in a Marketo field and when it's that date I want to send an email. I can't find any examples of this in the community?