View source for 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
  }


----

PostInsertRecord

https://community.sagecrm.com/user_community/f/84/t/10447.aspx