Can you compare two date fields in a smart list or smart campaign

[Anonymous]

For Example - If you have an email invalid date field could you compare that to another date field like last email opened date or last email clicked date?  If the last opened or clicked date happened after the email invalid date you could then create a flow action to mark the lead invalid = F and make the invalid date field = NULL.

Thoughts?  Any Idea of how this might be accomplished?

[Anonymous]

You could potentially do this using email scripting.  I don;t believe it is possible in the drag and drop editor but I could be Wrong.

[Stijn_Heijthuij]

Hi Eric,

You can't use tokens in the Smart List (yet). This is an idea in the community, but is a lot trickier than it seems.

The best bet would be to use the two date fields in an 'on or before' and 'after' constraint. Although you would either have to set it to Today or a specific date.

[Anonymous]

Hi Jamie and Stijin - thanks for the awesome answers. 

[Anonymous]

I had to work through this problem recently and I wanted to leave an artifact for people in my situation in the future.  Here is a crude but functional example of comparing two dates using Marketo's Velocity templates:

#set($next_upcoming = "")

next upcoming: $next_upcoming<br/>

#foreach($object in $openTableReservation_cList)

  #set($fake_now = $convert.parseDate("2016-05-13", "yyyy-MM-dd"))

  fake now: $fake_now<br/>

  #set($reservation_date = $object.reservationDate)

  #set($parsed_reservation_date = $convert.parseDate($reservation_date, "yyyy-MM-dd"))

  reservation date: $parsed_reservation_date</br>

  #set($is_upcoming = $date.difference($fake_now, $parsed_reservation_date).getSeconds())

  is_upcoming: $is_upcoming</br>

  #if($is_upcoming >= 0)

    UPCOMING<br/>

    #set($is_next = $date.difference($parsed_reservation_date, $parsed_next_upcoming_reservation_date).getSeconds())

    is_next: $is_next</br>

    #if(($next_upcoming == "") || ($is_next > 0))

      NEXT<br/>

      #set($next_upcoming = $object)

      #set($next_upcoming_reservation_date = $next_upcoming.reservationDate)

      #set($parsed_next_upcoming_reservation_date = $convert.parseDate($next_upcoming_reservation_date, "yyyy-MM-dd"))

      next upcoming reservation date: $parsed_next_upcoming_reservation_date</br>

    #end

  #end

  <br/>

#end

[Anonymous]

Hi Eric Price​,

That's a very good use case. This can be best achieved by using a web hook. Other options (though a bit cumbersome) would be custom javascript on a page.and usual export->>Process in Excel -->> Import back.

Bad News:

Unfortunately, it is not possible to be done using Velocity Scripting at all. Velocity scripting does not allow to 'modify' any value in the Marketo database. Velocity scripting is for email rendering only. That means, you can 'show' processed value in an email but would not be able to 'store' that processed data in a Marketo field for future use as you need here.

Good News:

This is totally doable using a web hook.

You just need to code a web hook that accepts 2 dates, and returns the comparison result back to Marketo. Then in Marketo, you can set the appropriate field to T or F as needed.

Efforts:

You will need a server and IT support to set one up.

You will also need some code to be developed to actually compare and communicate with Marketo.

And then you need to test it out. We are talking about a couple of days work for sure.

Another option is to use a ready made web hook library. That will enable you to implement it pretty fast (in minutes) with no coding and server set up. This requirement looks similar to a use case that was discussed here.

Marketo Magic: Localize date tokens based on country or language | Rajesh Talele | LinkedIn

Your exact case is solved using the web hook available at Web Hook Library

Hope this helps. Please feel free to connect if I can answer any other question.

Rajesh Talele