Table Scripting

This sample shows you how to check if the field has been changed from one value to another

 function UpdateRecord()
 {
   if (Values("case_AssignedUserId")+""=="undefined")
     return;  //bad update from crm..or field not changed
   var CaseRec=CRM.CreateQueryObj("SELECT * FROM Cases WITH (NOLOCK) WHERE " + WhereClause);
   CaseRec.SelectSQL();
   if (CaseRec("case_assigneduserid")!=Values("Case_AssignedUserId"))
   {
     ErrorStr="field value has changed from "+CaseRec("case_assigneduserid") +" to "+Values("Case_AssignedUserId");
   }
 }

---

Altering CRM dates in Table level scripts

 var mydate = new Date();
 mydate.setDate(mydate.getDate() +7);
 Values("comp_customerstartdate") = mydate.getVarDate();

Ref: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2011/03/07/setting-or-updating-date-fields-using-a-table-level-script.aspx

---

Comparing previous values (in cases where you might want to run some code only when a field value has changed)

Within the UpdateRecord method stub you would first query the current record using

 var crec=CRM.FindRecord("agreement",WhereClause);

You would then compare values as in

 if (crec("comp_name")!=Values("comp_name"))...

For date fields you have to create a Date object

 var c_comp_renewaldate = new Date(crec("comp_renewaldate"));
 var v_comp_renewaldate = new Date(Values("comp_renewaldate"));

and then call the getDate() method of the Date object to do the comparison. You cannot compare the date objects as it will never match

 if (c_comp_renewaldate.getDate() != v_comp_renewaldate.getDate())...

---

Useful learning tool if your trying to work out when each of the TLS functions are called.

Table level script will log all events to the SQL logs

 function log(message, error) {try{CRM.CreateQueryObj('/* '+((error)?message:message+' */')).SelectSql();}catch(e){}}
 function InsertRecord() { log("InsertRecord called",1); }
 function PostInsertRecord() { log("PostInsertRecord called",1); }
 function UpdateRecord() { log("UpdateRecord called.  WHERECLAUSE=" + WHERECLAUSE,1); }
 function DeleteRecord() { log("DeleteRecord called.  WHERECLAUSE=" + WHERECLAUSE,1); }

Ref: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2011/08/19/how-do-you-know-if-your-table-level-scripts-are-being-executed.aspx

---

Function to get the SID from within a table level script - you may want to do this to call an asp page via ajax for example

Based off of code at: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2008/03/25/how-can-i-get-the-sid-in-serverside-scripting.aspx

 function getsid()
 {
   var strPath = new String(CRM.URL(200));
   var arrayFullKeys = strPath.split("?");
   var arrayKeys = arrayFullKeys[1].split("&");
   for (var i=0;i<arrayKeys.length;i++)
   {
      var arrayValue = arrayKeys[i].split("=");
      if (arrayValue[0].toLowerCase()== "sid")
      {
           return arrayValue[1];
      }
  }
  return "";

}

---

To Make an Ajax GET request call from within a table script

 function MakeAjaxPostRequest(RequestPath)
 {
   var XmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
   XmlHttp.open('GET',RequestPath,false);
   XmlHttp.send();
   XmlHttp=null; // always clear the XmlHttp object when you are done to avoid memory leaks
 }

---

function Ajax() {

   try {
       if(window.XMLHttpRequest) {
           return new XMLHttpRequest();
       } else if(window.ActiveXObject) {
           try {
               return new ActiveXObject('Msxml2.XMLHTTP');
           } catch(try_again) {
               return new ActiveXObject('Microsoft.XMLHTTP');
           }
       }
   } catch(fail) {
       return null;
   }

}

function sendMail(to, subject) {

    var rq;
    if(rq = new Ajax()) {
        // Success; attempt to use an Ajax request to a PHP script to send the e-mail
        try {
            rq.open('GET', 'sendmail.php?to=' + encodeURIComponent(to) + '&subject=' + encodeURIComponent(subject) + '&d=' + new Date().getTime().toString(), true);
            rq.onreadystatechange = function() {
                if(this.readyState === 4) {
                    if(this.status < 200 || this.status >= 400) {
                        // The request failed; fall back to e-mail client
                        window.open('mailto:' + to + '?subject=' + encodeURIComponent(subject));
                    }
                }
            }
            rq.send(null);
        } catch(fail) {
            // Failed to open the request; fall back to e-mail client
            window.open('mailto:' + to + '?subject=' + encodeURIComponent(subject));
        }
    } else {
        // Failed to create the request; fall back to e-mail client
        window.open('mailto:' + to + '?subject=' + encodeURIComponent(subject));
    }

}

==

set config = CreateObject("CDO.Configuration") sch = "http://schemas.microsoft.com/cdo/configuration/" with config.Fields

.item(sch & "sendusing") = 2 ' cdoSendUsingPort
.item(sch & "smtpserver") = application("smtpserver")
.item(sch & "smtpserverport") = application("smtpserverport")
.item(sch & "smtpauthenticate") = 1 'basic auth
.item(sch & "sendusername") = application("sendusername")
.item(sch & "sendpassword") = application("sendpassword")
.update

end with

with CreateObject("CDO.Message")

 .configuration = config
 .to = ...
 .from = ...
 .subject = ....
 .HTMLBody = ....
 call .send()

end with