Velocity Script Error - 5000 lexical error

[SanfordWhiteman]

Pretty sure a $150MM company considered HIPAA concerns when they were founded ~15 years ago. 🙂

[Miguel_Board1]

Hey Sanford,

You are correct. There is HIPAA to consider, however, none of the data being used by the velocity script or email appointment reminders violates PHI (Patient Health Information). This discussion has been through the rounds of our organization and the email reminders have been approved as within bounds. With Marketo being a HIPAA compliant platform, we'd really like to leverage Marketo for the reminders.

[SanfordWhiteman]

---

You are correct. There is HIPAA to consider, however, none of the data being used by the velocity script or email appointment reminders violates PHI (Patient Health Information). This discussion has been through the rounds of our organization and the email reminders have been approved as within bounds. With Marketo being a HIPAA compliant platform, we'd really like to leverage Marketo for the reminders.

---

Yep, you definitely couldn't switch to an entirely different app now, anyway. Sounds like you're down to the wire.

[SanfordWhiteman]

---

We are currently adjusting the appointment time we send to Marketo (via the API) by the difference between the practice’s timezone and Central time

---

I was going to ask about timezones, so I'm glad you mentioned this, but are you saying the time stored in Marketo is already the correct time, if you pretend all your practices are in Central time? This isn't clear from your wording.

N.B. If the practice's IANA timezone ("America/New_York", "America/Los_Angeles") is stored in a field in Marketo — a field on the lead or on the CO would work — then you wouldn't adjust the stored time at all. In fact one should never change stored time in any database, it should be stored in UTC/Z, and then you can output that time in whatever is the appropriate timezone.

[Miguel_Board1]

Yes, the appointment times being stored in Marketo are being changed in the data load process to reflect the time zone of the end record. I had never previously heard of using the IANA timezone with Marketo as a solution. There was no solution offered by Marketo for incorrect times showing in emails, so we improvised. Your solution certainly sounds like a much more clean way of doing things.

[SanfordWhiteman · Accepted solution]

---

There was no solution offered by Marketo for incorrect times showing in emails, so we improvised. Your solution certainly sounds like a much more clean way of doing things.

---

How bizarre. I wrote a much-visited post on dates & datetimes in Velocity, showing how to include timezones for input and output.

In any case, incorporating the additional assumption that your converted-to-CST datetimes are in fact Strings with the format "yyyy-MM-ddHH:mm" (ex. "2021-01-0712:34") as suggested somewhere above... it sounds like you want this, or something much like it:

#set( $defaultTimeZone = $date.getTimeZone() )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )
## your corporate datetime formats for both input (parsing) and output 
#set( $audigyDateTimeInputFormat = "yyyy-MM-ddHH:mm" )
#set( $audigyDateTimeOutputFormat = "MM/dd/yyyy' at 'hh:mma" )
#set( $excludableAppointmentStatuses = ["Reschedule","Cancelled","Rescheduled" ] )
#set( $excludableAppointmentTypes = ["Benefit Renewal","Clean and Check","Change of Insurance Consult","Complimentary Screening","Complimentary Screening/Demo","Demo Follow Up","Diagnostic and Warranty","Diagnostic Eval and Demo 3yrs +","Diagnostic Eval/HAC(Hearing Test)& Hearing Aid Check","Diagnostic Evaluation and Hearing Aid Evaluation","Diagnostic Evaluation(Hearing Test)","Earmold Fitting","Earmold Impression","Empire Plan Follow up","Final Denial Consult","Functional Gain Testing","HA Exchange","HAC 45 day trial","HAC+D-Hearing Aid Check & Demo 3 yrs +","HAC-Hearing Aid Check less than 3 yrs.","HAE-Hearing Aid Evaluation","HAE/HAF -Hearing Aid Evaluation & Fitting","HAF--Hearing Aid Fitting","Hearing Aid Adoption","Hearing Aid Consult Follow-Up","Hearing Aid Evaluation","Home Delivery/Dispense","Home Visit","Insurance Benefit Renewal Follow Up.","Insurance change follow up","Insurance consult f/u needs Demo","Insurance Consult Follow Up/Fitting Upon Authorization","Insurance Consult Initial W/WO Diagnostics","Insurance Final Denial Follow-Up","Insurance follow up after Dispense","Insurance Resubmit","Lost Device","New Patient Adult CAE","New Patient Pediatric CAE","Office Visit/Referral Consult","Payment","Replacement Pick up","Service/Repair","Tinnitus Evaluation","Warranty","Warranty and Demo over 3 years","Warranty and HAC less than 3 years"] )
#foreach( $appointment in $sorter.sort($appointment_cList,"appointmentDateTime:asc") )
#set( $dtAppointment = $convert.parseDate($appointment.appointmentDateTime, $audigyDateTimeInputFormat, $defaultLocale, $defaultTimeZone) )
#set( $calAppointment = $convert.toCalendar($dtAppointment) )
#set( $hoursInFuture = $date.difference($calNow,$calAppointment).getHours() )
#set( $isExcludedStatus = $excludableAppointmentStatuses.contains($appointment.appointmentStatus) )
#set( $isExcludedType = $excludableAppointmentTypes.contains($appointment.appointmentType) )
#if( 
  $hoursInFuture >= 24 && 
  $hoursInFuture <= 48 &&
  !$isExcludedStatus && 
  !$isExcludedType
)
#set( $nextQualifiedAppointment = $dtAppointment )
#break
#end
#end
<p>$date.format($audigyDateTimeOutputFormat,$nextQualifiedAppointment)<br><br>

[dsh_community]

This is an interesting discussion and it's always good to see people trying to help each other out. Sanford - you particularly have a strong reputation for going out of your way to help other people solve real business problems, and you do so in a humble way expecting nothing in return.

Regardless of HIPAA or privacy concerns, I still don't understand why Miguel and his organization decided to reinvent the wheel here instead of using something other than Marketo to help their audiologists do appointment scheduling. I don't like it when people try to make Marketo do everything instead of employing other technology that's specifically designed for that purpose. An example would be using Marketo for your enterprise event management instead of a dedicated tool. The classic conversation in this context is "Hey system administrators, can we add a dedicated Marketo/CRM field for desired t-shirt size for this one time user summit that we're having next month?"

We should remind ourselves that Marketo is designed for promotional marketing purposes, inbound lead collection, and platform integrations. It's not designed to be a substitute for other software that does automated tasks such as appointment scheduling. My hairdresser uses https://www.schedulista.com/ and I guarantee you she didn't need a year or any professional services to get it up and running.

I still wish you guys the best as it's clear you've put a lot of time and effort into this. Thanks.

[SanfordWhiteman]

You seem to mistake what's happening here. Marketo itself isn't "doing" any scheduling. Custom Objects are being created by another purpose-built app and then synced into Marketo. Miguel's company specializes in such software, that's what they do!

No different from syncing Calendly-booked appointments to Marketo COs, or syncing customers' current Products or Warranties, or syncing ON24 webinar attendance details... or any of the myriad ways in which objects managed by specialized platforms are synced into Marketo to be actioned in the MOPS ecosystem. Far from being outside the realm, these are things Marketo is every bit designed for.

[Miguel_Board1]

Howdy,

Our company provides professional services to Audiologists. One of the services is Marketing Automation. We are leveraging Marketo to allow our members to better engage with their patients. We are not using Marketo to schedule appointments, as this is done by other means. We are simply sending out reminders based on data that we already have in Marketo. The challenge has been manipulating the velocity script in the email to meet the requirements we have set. Essentially, I feel our Marketo Consultant was more focused on being more of a 'break/fix' specialist than in providing feedback/solutions and truly understanding what we are trying to do and why. 

[Miguel_Board1]

I've finally been able to get a few rounds of testing in and this script is the golden ticket. The appointment time is pulling in just as we wanted. This script is soooo much more clean and much easier to manipulate.

Sanford, your contributions to this community are nothing short of amazing. Your insight and knowledge are rocks that help keep house that Marketo built from crumbling away. Thanks again for helping me work through this!