Views:

Scenario Overview

In this business scenario we need to generate an Outlook appointment (.ics) file as an attachment on an email when ever the 'Subject' field of the the email contains the word 'Doctor' & the regarding field is set to a record of type 'new_doctorvisit'.

The screenshot below shows the generated DoctorVisit.ics file attachment.

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • A formula of type 'Save - Perform Action' is created & set to execute whenever an Email is created 
  • When an email gets created the formula first checks the subject line to see if it contains the word 'Doctor'
  • Next it checks to see if there is a regarding value set & that it is of type 'new_doctorvisit'
  • If all these are true then it will generate the ICS file
  • And finally it will attach it to the Email              

N52 Formula Manager Steps

The following set of steps outline how to create this Formula

  • Create a new formula of type 'Save - Perform Action'
  • Set the Source Entity to Email
  • Set the Event to 'Create'
  • Copy & paste the formula below into the formula 
  • Click save & test

Formula

if( Contains([email.subject], 'Doctor')  and ContainsData([email.regardingobjectid]) and [email.regardingobjectidtype] = 'new_doctorvisit',  

          CreateNote('Doctor Visit',
                              'email',
                              [email.activityid],
                              'Doctor Visit',
                              'DoctorVisit.ics', 
                              'text/calendar',
                               Base64Encode(
                                      'BEGIN:VCALENDAR' + '\r\n' +
                                      'VERSION:2.0' + '\r\n' +
                                      'CALSCALE:GREGORIAN' + '\r\n' +
                                      'BEGIN:VEVENT' + '\r\n' +
                                      'DTSTART:' + ToString(FindValue('new_doctorvisit', 
                                                                      'new_doctorvisitid', 
                                                                      [email.regardingobjectid] ,
                                                                      'starttime', 
                                                                        '', 
                                                                        true),
                                                            'O' )  + '\r\n' +
                                      'DTEND:' + ToString(FindValue('new_doctorvisit', 
                                                                    'new_doctorvisitid',
                                                                    [email.regardingobjectid] , 
                                                                   'endtime', 
                                                                    '',
                                                                    true),
                                                           'O' )  + '\r\n' +
                                      'SUMMARY;ENCODING=QUOTED-PRINTABLE:' + 
                                       [email.subject]  + '\r\n' +
                                      'DESCRIPTION;ENCODING=QUOTED-PRINTABLE:' + 
                                       Left(StripHtml([email.description]), 200)  + '\r\n' +
                                      'LOCATION;ENCODING=QUOTED-PRINTABLE:New York (Office)' + '\r\n' +
                                      'UID:' + [email.activityid]  + '\r\n' +
                                      'PRIORITY:3' + '\r\n' +
                                      'END:VEVENT' + '\r\n' +
                                      'END:VCALENDAR'
                              )
                   ) ,

'NoOp' )