Read, Query & Update Field(s) in Other Microsoft CRM Entities Inside OnLoad, OnSave and OnChange Events Using Jscript

It happens often that you would want to access/update fields of other entities using jscript from either OnLoad, OnSave or OnChnage events. Here I am using SOAP calls to make this happen.

As a CRM consultant, recently I had to do a project that required using a read, check and update SOAP calls. I will provide a very simplified version of this project for this blog entry.

Scenario:
Company X sells widgets and not only they want to keep track of who they sell these widgets to but also they want Microsoft CRM to automatically generate a Serial Number each time they create a new widgets record. To keep it simple, we will use a simple Serial number format: WID1000 would be the first Serial number; WID1001 would be the second, WID1002 the third and so on.

Created a new custom entity named “Widget”. Related it to Account entity with a Parental relationship (Account being the parent). Created an attribute for the Widget entity and named it WidgetSerialNum.

Created a new custom entity called Counter to hold the next available counter value. Added three attributes to this entity:

  1. “ItemType” to hold the type of the item we would want to create serial numbers for. In this scenario there is only one; Widget. The value is “Widget”
  2. Prefix set to “WID” to hold the Prefix for the serial number
  3. Countervalue set to 1000 to hold the next available counter value

Placed the following Code in the OnSave event of the Widget entity. All the instructions are part of the comments so you can just cut and past the code.

Jscript code:

/*

In the following SOAP call, replace:

      “new_counter” with the schema name of the external entity having the field that you want to read.

      “new_counterid” with the schema name of that holds the GUID of the external entity.  We will need this to update the counter later

      “new_countervalue” with the schema name of the field you want to read

      “new_itemtype” with the schema name of the field you want to filter on

      FILTER with the value (in this case “Widget”) you want to filter on

*/

 

 

//———————————————————–

//—- Read the latest value of the counter

 

var FILTER = “Widget”;

 

var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);

var xmlPost = “” +

“<?xml version=”1.0″ encoding=”utf-8″?>” +

“<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>” +

” <soap:Body>” +

” <query xmlns:q1=”http://schemas.microsoft.com/crm/2006/Query” xsi:type=”q1:QueryExpression” xmlns=”http://schemas.microsoft.com/crm/2006/WebServices”>” +

” <q1:EntityName>new_counter</q1:EntityName>” +

” <q1:ColumnSet xsi:type=”q1:ColumnSet”>” +

” <q1:Attributes>” +

” <q1:Attribute>new_counterid</q1:Attribute>” +

” <q1:Attribute>new_countervalue</q1:Attribute>” +

” </q1:Attributes>” +

” </q1:ColumnSet>” +

” <q1:Distinct>false</q1:Distinct>” +

” <q1:Criteria>” +

” <q1:FilterOperator>And</q1:FilterOperator>” +

” <q1:Conditions>” +

” <q1:Condition>” +

” <q1:AttributeName>new_itemtype</q1:AttributeName>” +

” <q1:Operator>Equal</q1:Operator>” +

” <q1:Values>” +

” <q1:Value xsi:type=”xsd:string”>” + FILTER + “</q1:Value>” +

” </q1:Values>” +

” </q1:Condition>” +

” </q1:Conditions>” +

” </q1:Criteria>” +

” </query>” +

” </soap:Body>” +

“</soap:Envelope>” +

“”;

xmlHttpRequest.Open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);

xmlHttpRequest.setRequestHeader(“SOAPAction”,“http://schemas.microsoft.com/crm/2006/WebServices/RetrieveMultiple”);

xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);

xmlHttpRequest.setRequestHeader(“Content-Length”, xmlPost.length);

xmlHttpRequest.send(xmlPost);

var doc = xmlHttpRequest.responseXML;

 

// JScript source code

//—————————————————

 

var FILTER = 2;

 

if(crmForm.all.new_widgetsn.DataValue==null)

{

//———————————————–

//— Get the next avaiable SerialNumber Counter

//———————————————–

 

var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);

var xmlPost = “” +

“<?xml version=”1.0″ encoding=”utf-8″?>” +

“<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>” +

” <soap:Body>” +

” <query xmlns:q1=”http://schemas.microsoft.com/crm/2006/Query” xsi:type=”q1:QueryExpression” xmlns=”http://schemas.microsoft.com/crm/2006/WebServices”>” +

” <q1:EntityName>new_counter</q1:EntityName>” +

” <q1:ColumnSet xsi:type=”q1:ColumnSet”>” +

” <q1:Attributes>” +

” <q1:Attribute>new_counterid</q1:Attribute>” +

” <q1:Attribute>new_countervalue</q1:Attribute>” + 

” <q1:Attribute>new_prefix</q1:Attribute>” + 

” </q1:Attributes>” +

” </q1:ColumnSet>” +

” <q1:Distinct>false</q1:Distinct>” +

” <q1:Criteria>” +

” <q1:FilterOperator>And</q1:FilterOperator>” +

” <q1:Conditions>” +

” <q1:Condition>” +

” <q1:AttributeName>new_itemtype</q1:AttributeName>” +

” <q1:Operator>Equal</q1:Operator>” +

” <q1:Values>” +

” <q1:Value xsi:type=”xsd:string”>” + FILTER + “</q1:Value>” +

” </q1:Values>” +

” </q1:Condition>” +

” </q1:Conditions>” +

” </q1:Criteria>” +

” </query>” +

” </soap:Body>” +

“</soap:Envelope>” +

“”;

xmlHttpRequest.Open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);

xmlHttpRequest.setRequestHeader(“SOAPAction”,“http://schemas.microsoft.com/crm/2006/WebServices/RetrieveMultiple”);

xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);

xmlHttpRequest.setRequestHeader(“Content-Length”, xmlPost.length);

xmlHttpRequest.send(xmlPost);

var doc = xmlHttpRequest.responseXML;

 

if (doc.selectSingleNode(“//BusinessEntity”) != null)

{

var SN = doc.selectSingleNode(“//BusinessEntity”);

if (SN.selectSingleNode(“new_countervalue”).text != null)

{

var GUID = SN.selectSingleNode(“new_counterid”).text;

 

//—-  Set the next Highest Serial number Counter Avaiable.

var HighestSNCounter = SN.selectSingleNode(“new_countervalue”).text;

 

//—- Get the Prefix

var Prefix = SN.selectSingleNode(“new_prefix”).text;

 

//—-  Build the New Serial Number

var result = Prefix+HighestSNCounter.toString();

 

 

//———————————————————-

//   In a while loop call the funtion that checks to see if

//   the newly created serial number exists.  If yes, it will

//   increatment the counter, create a new serial number and

//   checks again until it finds one that is not being used.

//———————————————————-

 

while(SerialNumberExists(result))

{

//—- Newly created serial number exists, Increment the counter and check again

    HighestSNCounter  += 1;

    result = Prefix +HighestSNCounter.toString()+ DateSTR;

}

 

//—- Newly created Serial number is good.  Assign it

crmForm.all.new_widgetsn.DataValue = result;

 

//———————————————————-

//    Increment the Counter to be placed back in the 

//    Counter Entity to be aviable for the next use

//———————————————————-

HighestSNCounter = parseInt(HighestSNCounter) +1;

 

 

 

//———————————————————————

//     This is the SOAP code to update a field in an external entity

//———————————————————————

 

//    Update the Counter field of the Counter entity

//    with the next avaiable Serial Number Counter

//———————————————————-

 

var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);

var xmlPost = ‘<?xml version=”1.0″ encoding=”utf-8″?>’+

    ‘<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” ‘+

    ‘xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>’+

    ‘<soap:Body>’+

    ‘<Request xsi:type=”UpdateRequest” xmlns=”http://schemas.microsoft.com/crm/2006/WebServices”>’+

    ‘<Target xsi:type=”TargetUpdatenew_counter“>’+

    ‘<new_counter>’+

    ‘<new_counterid>’ + GUID + ‘</new_counterid>’+

    ‘<new_countervalue>’ + HighestSNCounter + ‘</new_countervalue>’+

    ‘</new_counter>’+

    ‘</Target>’+

    ‘</Request>’+

    ‘</soap:Body>’+

    ‘</soap:Envelope>’;

xmlHttpRequest.open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);

xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);

xmlHttpRequest.setRequestHeader(“SOAPAction”, “http://schemas.microsoft.com/crm/2006/WebServices/Execute”);

xmlHttpRequest.send(xmlPost);

 

}

}

}

 

//———————————————————————

//     This is the SOAP code to update the a field in an external entity

//———————————————————————

 

//————————————————————–

//     This function when called will check the Widget entity to

//     see if the newly created Serial Number is already in use

//————————————————————–

 

function SerialNumberExists(SerialNumber)

{

    var xmlhttp = new ActiveXObject(“Microsoft.XMLHTTP”);

    var xmlPost = ‘<?xml version=”1.0″ encoding=”utf-8″?>’+

        ‘<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” ‘+

        ‘xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>’+

        ‘<soap:Body>’+

        ‘<query xmlns:q1=”http://schemas.microsoft.com/crm/2006/Query” xsi:type=”q1:QueryExpression” ‘+

        ‘xmlns=”http://schemas.microsoft.com/crm/2006/WebServices”>’+

        ‘<q1:EntityName>new_widget</q1:EntityName>’+

        ‘<q1:ColumnSet xsi:type=”q1:ColumnSet”>’+

        ‘<q1:Attributes>’+

        ‘<q1:Attribute>new_widgetsn</q1:Attribute>’+

        ‘</q1:Attributes>’+

        ‘</q1:ColumnSet>’+

        ‘<q1:Distinct>false</q1:Distinct>’+

        ‘<q1:Criteria>’+

        ‘<q1:FilterOperator>Or</q1:FilterOperator>’+

        ‘<q1:Conditions>’+

        ‘<q1:Condition>’+

        ‘<q1:AttributeName>new_widgetsn</q1:AttributeName>’+

        ‘<q1:Operator>Equal</q1:Operator>’+

        ‘<q1:Values>’+

        ‘<q1:Value xsi:type=”xsd:string”>’ + SerialNumber + ‘</q1:Value>’+

        ‘</q1:Values>’+

        ‘</q1:Condition>’+

        ‘</q1:Conditions>’+

        ‘</q1:Criteria>’+

        ‘</query>’+

        ‘</soap:Body>’+

        ‘</soap:Envelope>’;

    xmlhttp.open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);

    xmlhttp.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);

    xmlhttp.setRequestHeader(“SOAPAction”, “http://schemas.microsoft.com/crm/2006/WebServices/RetrieveMultiple”);

    xmlhttp.send(xmlPost);

    xmlResponse = xmlhttp.responseXML;

    var nodes = xmlResponse.selectNodes(“//BusinessEntity/accountnumber”);

    if (nodes.length == 0)

    {

        return false;

    }

    else

    {

        return true;

    }

}

RM
Microsoft CRM Consultant
Unitek Microsoft CRM Services

11 replies
  1. Jeremy
    Jeremy says:

    I have a field in the opportunity entity called serial number. I want to increase this by 1 every time a new opportunity is created. Is this the method I would use? Tried getting this code to work with no avail…

    Thanks!

  2. Roya
    Roya says:

    Hi Jeremy,
    Yes, this is what you would do. Did you create the custom entity? Did you make sure to replace all the field names and variables with your field names?

    There is a lot of code here and it is easy to miss something.

    If you have done all this, I suggest putting some alert statements and see where your problem is.

    RM, Microsoft CRM Consultant
    Unitek Microsoft CRM Services

  3. Jeremy
    Jeremy says:

    Is there anyway you could post screen shots of the forms in customization. I have to be missing something somehwhere. I am trying to mimic exactly what you have. I created an entity labeled Widget (schema: new_widget) with two custom fields Widget Serial Number (schema: new_widgetsn ‘nvarchar’) and Counter ID (schema new_counterid ‘lookup’). I put the code in the onSave of the Widget form. I then created an entity labeled Counter (schema: new_counter) with custom fields Counter Value (schema: new_countervalue ‘nvarchar’) and Item Type (schema: new_itemtype ‘nvarchar’). But yet no avail =(

  4. Jeremy
    Jeremy says:

    I think I am having trouble understanding:

    “Created a new custom entity called Counter to hold the next available counter value. Added three attributes to this entity:

    “ItemType” to hold the type of the item we would want to create serial numbers for. In this scenario there is only one; Widget. The value is “Widget”
    Prefix set to “WID” to hold the Prefix for the serial number
    Countervalue set to 1000 to hold the next available counter value”

    So I am to create 3 attributes in the new counter customer enitity called ItemType, Prefix, and CounterValue. I put those 3 fields on my Counter form, enter the values and save them as a single record?

  5. Aaron
    Aaron says:

    Hello.

    I’m trying something very similar. What I want to do is to check in an onLoad-script in an opportunity, if the opportunityid of a particular opportunity appears in any quote. I tried to use your code as a schema, but my code doesn’t work. The browser throws an “object expected”-error.

    Since I’m very new to SOAP-requests, it may be a basic error. I tested the SOAP-request with a testing tool. It works. My guess is that it’s something around the request or a namespace-issue or stuff like that. How can I debug this?

  6. Stanislav
    Stanislav says:

    Hello, this code is based on your example. Thank you for idea. It’s a really elegant solution. But …. it doesn’t work. Script works (no syntax errors) but no result. I think the problem with web service (method). Would you please check it?

    var test_value=”test”; // значение для наглядности примера
    var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);
    var xmlPost = ”+
    ”+
    ”+
    ”+
    ”+
    ”+
    ” + ‘{5C583880-D02D-DD11-9B54-000C2909D8F5}’ + ”+
    ” + test_value + ”+
    ”+
    ”+
    ”+
    ”+
    ”;
    xmlHttpRequest.open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);
    xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);
    xmlHttpRequest.setRequestHeader(“SOAPAction”, “http://schemas.microsoft.com/crm/2006/WebServices/Execute”);
    //alert(xmlPost);
    xmlHttpRequest.send(xmlPost);

  7. Stanislav
    Stanislav says:

    var specialid=2; // значение для наглядности примера
    var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);
    var xmlPost = ”+
    ”+
    ”+
    ”+
    ”+
    ”+
    ‘{5C583880-D02D-DD11-9B54-000C2909D8F5}’+
    ‘specialid’+
    ”+
    ”+
    ”+
    ”+
    ”;
    xmlHttpRequest.open(“POST”, “/mscrmservices/2006/CrmService.asmx”, false);
    xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);
    xmlHttpRequest.setRequestHeader(“SOAPAction”, “http://schemas.microsoft.com/crm/2006/WebServices/Execute”);
    //alert(xmlPost);
    xmlHttpRequest.send(xmlPost);

Comments are closed.