Summary:

Out of the box, Sage CRM only supports SMS messaging via an SMTP gateway. SMS providers typically support a range of protocols for interacting with their services. These might include SMTP, SMPP, FTP, and RESTful web services APIs.

This article exists as a demonstration of how to integrate Sage CRM with a specific SMS service provider. The example given in this article could be easily adapted to other APIs provided by a service provider, including other RESTful APIs and COM interfaces.


More information:

The example given here is for RouteSms, an Indian messaging company. Among their offerings is an SMS service which can be interacted with via a HTTP interface. Other services are available.

In this example, we'll be sending SMS reminders to users before an appointment is due. The existing SMS functionality (which only works over SMTP) has been disabled, and custom fields have been added to the Communication entity to indicate whether a reminder SMS should be sent out to users that are attendees of an appointment. We then use an escalation rule to pick up the scheduled SMS, and send it out.

First of all, we can create a check box for indicating whether an SMS reminder should be sent to the users:

Send reminder SMS field

Next, we create a selection field to indicate when we want the reminder to be sent:

SMS reminder time selection

We can then add numeric values for the number of minutes before an appointment we want the reminder to be sent (0, 5, 15, 30 and 60). Numeric values are stored in the translation code, as this makes it easier to use them in a trigger clause:

Reminder selection properties

Next, we add a field to indicate that a message has been sent. There's one on the table anyway, for the default SMS functionality, but we'll add a new one for clarity's sake:

Sent SMS reminder

Next, we add all three new fields to CommunicationSchedulingBox:

CommunicationSchedulingBox

This could be made neater – for example, you want to make the Sent check box read-only, and only enable the Sent SMS selection box when a user has checked the Sent reminder SMS field.

We need our escalation rule to trigger some server-side JavaScript, so we need to add a new field that will be changed by the escalation rule. We can then use a table level script to spot that that field has been changed, and send the SMS.

Do SMS reminder

Next, we create an escalation rule on vCommunication with the following trigger SQL clause:

comm_doremindersms = 'Y'
and ISNULL(comm_sentsmsreminder, 'N') = 'N'
and Comm_Type = 'Appointment'
and Comm_Status = 'Pending'
and DATEADD(minute, CAST(('-' + comm_smsremindermins)as int) , comm_datetime) < GETDATE()

This will select all pending appointments within the reminder time, where we have indicated that an SMS should be sent. We then add a single Set Column Value action to the escalation rule, setting comm_doSMSreminder to Y.

Finally, we create a new table level script on the Communication entity:

function UpdateRecord() {

      // RouteSMS parameters

      var SMS_SERVER = "sms6.routesms.com";
      var SMS_USER = "SmsUser";
      var SMS_PASSWORD = "SmsPassword";
      var SENDER_NAME = "SmsUser";
      var SMS_TYPE = 0;                   // plaintext - GSM 3.38 encoding
      var DELIVERY_REPORT = 0;            // 0=false
      var SMS_SOURCE = "SmsUser";

      var doSms = Values("comm_dosmsreminder");

      if (doSms == 'Y') {

            var SMSCommRecords = CRM.FindRecord("Communication,vCommunication", WhereClause);

            while (!SMSCommRecords.Eof) {

                  var method = "GET";
                  var postData = "";

                  var SMSBody = "REMINDER: Meeting @ " + SMSCommRecords.item("Comm_datetime") + '. '
                        + SMSCommRecords.item("comm_subject");

                  CRM.ExecSql("-- Message: " + SMSBody);
                  CRM.ExecSql("-- User: " + SMSCommRecords.item("cmli_comm_userid"))

                  var userRec = CRM.FindRecord("Users", "User_userid=" + SMSCommRecords.item("CmLi_Comm_UserID"));

                  var userPhone = userRec.item("user_mobilephone") + "";

                  if (userPhone.length>5) { // somewhat arbitrary, you can put in a valid phone check here

                        var async = false;

                        var request = new ActiveXObject("Microsoft.XMLHTTP");

                        var smsFullUrl = "http://" + SMS_SERVER + ":8080/bulksms/bulksms?username=" + encodeURIComponent(SMS_USER)
                              + "&password=" + encodeURIComponent(SMS_PASSWORD) + "&type=" + SMS_TYPE + "&dlr=" + DELIVERY_REPORT
                              + "&destination=" + encodeURIComponent(userPhone) + "&source=" + SMS_SOURCE
                              + "&message=" + encodeURIComponent(SMSBody);

                        request.open(method, smsFullUrl, async);
                        request.send();

                        // Additional checking can be put in around the response code
                        // RouteSMS provide additional info in the response body

                        var status = request.status; // HTTP response status, e.g., 200 for "200 OK"
                        var data = request.responseText; // Returned data, e.g., an HTML document.

                        CRM.ExecSql("-- RouteSMS status: " + status); // Ad-hoc logging to the ewaresql.log

                  }
                  SMSCommRecords.NextRecord();
            }
            Values("comm_dosmsreminder") = 'N';
            Values ("comm_sentsmsreminder") = 'Y';
      }
}

This is a basic example – depending on how much SMS functionality you need to use, you may prefer to take another approach. RouteSMS's HTTP endpoint is very well-documented and easy to use; this tends to be the case for many SMS messaging providers.

Should you need a more complex solution, then it may be worth examining one of the SMS solutions available on the Sage CRM Community.



References:


 Download: RouteSms SmsPlus - Bulk Http API Specification