SOLVED

Re: Calculate number of years based on a date field

Go to solution
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

Re: Calculate number of years based on a date field

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 solution in original post

18 REPLIES 18
SanfordWhiteman
Level 10 - Community Moderator

Re: Calculate number of years based on a date field

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
Not applicable

Re: Calculate number of years based on a date field

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

Grégoire_Miche2
Level 10

Re: Calculate number of years based on a date field

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
Level 10 - Community Moderator

Re: Calculate number of years based on a date field

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
Not applicable

Re: Calculate number of years based on a date field

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?

Screen Shot 2018-03-21 at 9.38.31 AM.png

Thanks.

SanfordWhiteman
Level 10 - Community Moderator

Re: Calculate number of years based on a date field

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
Level 3

Re: Calculate number of years based on a date field

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
Level 10 - Community Moderator

Re: Calculate number of years based on a date field

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
Level 3

Re: Calculate number of years based on a date field

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?