Custom Auto-Incrementing Values in CRM

Inspired by Joel's post on Automatic Account Numbering, I decided to put together a solution that duplicates the built-in auto-numbering functionality that is found in the Case entity. I've seen numerous requests on the web on how to do something like this in CRM.

Before we dive right in, I should explain that many of the techniques I'm using here are built upon methods laid out in two previous posts: Calling the CRM Web Service with JavaScript and Custom Application Settings in CRM. It would be a good idea to get familiar with the concepts of those blog posts before continuing because I'm going to skip some stuff that's already been covered earlier.

Scenario

I want to have MSCRM automatically generate an account number for a new account with the following format: PRExxxxxx. The number should increment so the values returned would be PRE000001, PRE000002, PRE000003, and so-on.

Setup

First, we're going to create three new records in our Custom Settings entity:

• Setting Name: Custom_Prefix, Value: PRE
• Setting Name: Custom_Increment, Value: 0
• Setting Name: Custom_Pad, Value: 6

What we're doing with the Custom_Prefix setting is specifying what prefix our custom counter should use. We have set it to be "PRE". The Custom_Increment setting will store the last used value of the counter. I set it to 0 because I want the first occurrence to be 1. I've also created a setting named Custom_Pad that specifies how long we want the number to be. Here, I've set it to 6, so the first number returned will be 000001.

You may name these settings to whatever makes the most sense to you.

The Code

To pull this off, we've got to build some JavaScript that performs the following:

1. Call the CRM web service to get the custom number.
2. Check to make sure the number isn't already in use. If it is, we find another one.
3. Save the incremented value so we can start from there next time.

So let's get right down to business.

Get the Number

First, we call the CRM web service to pull the values of the three settings we specified above and build the number. Remember, we only want to do this when a form is loaded to create a record, so make sure to put the code in an If statement that checks for crmForm.FormType == 1:

// SPECIFY SERVER CONNECTION
    var serverUrl = "http://crmserver/mscrmservices/2006/crmservice.asmx";
    var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
    // BUILD THE XML REQUEST STRING
    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_customsetting</q1:EntityName>'+
        '<q1:ColumnSet xsi:type="q1:ColumnSet">'+
        '<q1:Attributes>'+
        '<q1:Attribute>new_customsettingid</q1:Attribute>'+
        '<q1:Attribute>new_name</q1:Attribute>'+
        '<q1:Attribute>new_value</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_name</q1:AttributeName>'+
        '<q1:Operator>Equal</q1:Operator>'+
        '<q1:Values>'+
        '<q1:Value xsi:type="xsd:string">Custom_Prefix</q1:Value>'+
        '</q1:Values>'+
        '</q1:Condition>'+
        '<q1:Condition>'+
        '<q1:AttributeName>new_name</q1:AttributeName>'+
        '<q1:Operator>Equal</q1:Operator>'+
        '<q1:Values>'+
        '<q1:Value xsi:type="xsd:string">Custom_Increment</q1:Value>'+
        '</q1:Values>'+
        '</q1:Condition>'+
        '<q1:Condition>'+
        '<q1:AttributeName>new_name</q1:AttributeName>'+
        '<q1:Operator>Equal</q1:Operator>'+
        '<q1:Values>'+
        '<q1:Value xsi:type="xsd:string">Custom_Pad</q1:Value>'+
        '</q1:Values>'+
        '</q1:Condition>'+
        '</q1:Conditions>'+
        '</q1:Criteria>'+
        '</query>'+
        '</soap:Body>'+
        '</soap:Envelope>';

    // PERFORM THE REQUEST
    xmlhttp.open("POST", serverUrl, false);
    xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2006/WebServices/RetrieveMultiple");
    xmlhttp.send(xmlPost);

    // GET THE RESPONSE
    var xmlResponse = xmlhttp.responseXML;
    //alert(xmlResponse.xml);

    // PARSE THE XML RESPONSE
    var guid = xmlResponse.selectNodes("//BusinessEntity/new_customsettingid");
    var setting = xmlResponse.selectNodes("//BusinessEntity/new_name");
    var value = xmlResponse.selectNodes("//BusinessEntity/new_value");
    //var returnValues = new Array();

    var prefix;
    var pad;
    var counter;
    var new_increment;
    var inc_guid;

    // LOOP THROUGH VALUES AND BUILD NUMBER
    for(var i = 0; i < setting.length; i++)
    {
        switch (setting[i].text)
        {
            case "Custom_Prefix":
                prefix = value[i].text;
                break;
            case "Custom_Increment":
                // INCREMENT OUR NUMBER, CONVERT TO TEXT AND PAD
                counter = parseInt(value[i].text) + 1;
                // GRAB THE GUID OF THE RECORD SO WE CAN UPDATE IT LATER
                inc_guid = guid[i].text;
                break;
            case "Custom_Pad":
                pad = parseInt(value[i].text);
                break;
        }
    }

    // DECLARE THE VARIABLE TO HOLD THE NUMBER
    var paddedNumber = counter.toString();
    var result;
    // PAD
    while(paddedNumber.length < pad)
    {
        paddedNumber = '0' + paddedNumber;
    }
    // PIECE TOGETHER
    result = prefix + paddedNumber;

The above snippet pulls the information from our 3 custom setting records, creates the number and stores it in the result variable. Notice we built a QueryExpression to post to the web service. These are a little easier to work with when dealing with multiple criteria.

Also, we pulled the GUID of the Custom_Increment setting record. We will need this bit of information later on when we have to update that record with a new setting value.

Make Sure Our Number Isn't Already in Use

Now we have to run a check to make sure this number isn't already in use. I do this by creating a function that calls the web service and returns true if the number is already in use and false if it is not:

// FUNCTION TO SEE IF NUMBER ALREADY EXISTS (RE-USE THE WEB SERVICE VARIABLES)
function numberExists(number)
{
    var serverUrl = "http://crmserver/mscrmservices/2006/crmservice.asmx";
    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>account</q1:EntityName>'+
        '<q1:ColumnSet xsi:type="q1:ColumnSet">'+
        '<q1:Attributes>'+
        '<q1:Attribute>accountnumber</q1:Attribute>'+
        '</q1:Attributes>'+
        '</q1:ColumnSet>'+
        '<q1:Distinct>false</q1:Distinct>'+
        '<q1:Criteria>'+
        '<q1:FilterOperator>Or</q1:FilterOperator>'+
        '<q1:Conditions>'+
        '<q1:Condition>'+
        '<q1:AttributeName>accountnumber</q1:AttributeName>'+
        '<q1:Operator>Equal</q1:Operator>'+
        '<q1:Values>'+
        '<q1:Value xsi:type="xsd:string">' + number + '</q1:Value>'+
        '</q1:Values>'+
        '</q1:Condition>'+
        '</q1:Conditions>'+
        '</q1:Criteria>'+
        '</query>'+
        '</soap:Body>'+
        '</soap:Envelope>';
    xmlhttp.open("POST", serverUrl, 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;
    }
}

What I want the script to do is call the above function to check to see if the number is in use already and if it is, increment our counter until it finds a number that isn't in use:

// SEE IF NUMBER EXISTS
while(numberExists(result))
{
    // NUMBER EXISTS, LET'S INCREMENT AND TRY AGAIN
    counter += 1;
    paddedNumber = counter.toString();
    while(paddedNumber.length < pad)
    {
        paddedNumber = '0' + paddedNumber;
    }
    result = prefix + paddedNumber;
}

Save the Increment Setting Value

So we're going to assume that we now have a number that is unique and we are going to use it. Now we need to adjust our Custom_Increment setting value to reflect our current increment level. In theory, we could skip this step, since the code tries new numbers until it finds one it can use, but after you've used several numbers, it bogs down things if you have to keep looping up from 1 to whatever. This method is much more efficient.

To save the value back into CRM, we call the web service again and post a properly constructed xml string. Here is where we use the GUID of the Custom_Increment setting.

// SAVE INCREMENT SETTING VALUE
    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_CustomSetting">'+
        '<New_CustomSetting>'+
        '<new_customsettingid>' + inc_guid + '</new_customsettingid>'+
        '<new_value>' + counter + '</new_value>'+
        '</New_CustomSetting>'+
        '</Target>'+
        '</Request>'+
        '</soap:Body>'+
        '</soap:Envelope>';
    xmlhttp.open("POST", serverUrl, false);
    xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2006/WebServices/Execute");
    xmlhttp.send(xmlPost);

This code submits an UpdateRequest to the CRM web service to update the Custom Setting record with the specified GUID to the specified value - in this case, our counter variable.

Populate the Account Number Field

The easy part of the whole thing is actually placing the number in the Account Number field on the Account Form:

crmForm.all.accountnumber.DataValue = result;

That's pretty much all there is to it.

Version 4.0 Compatibility

The above code works with version 3.0 pretty well - you'll need to make sure your entities are correct and that you specify the correct server in the web service URL to suit your environment.

For 4.0 (Titan), you'll need to make some adjustments. Interacting with the web service is different in 4.0, mainly because of the new multi-tenancy features. What this means is that our XML SOAP messages have to be constructed a little differently. For instance, the SOAP Request for the custom settings would need to be this:

<?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:Header>
<CrmAuthenticationToken xmlns="http://schemas.microsoft.com/crm/2007/WebServices">
<AuthenticationType xmlns="http://schemas.microsoft.com/crm/2007/CoreTypes">0</AuthenticationType>
<OrganizationName xmlns="http://schemas.microsoft.com/crm/2007/CoreTypes">Acme</OrganizationName>
<CallerId xmlns="http://schemas.microsoft.com/crm/2007/CoreTypes">00000000-0000-0000-0000-000000000000</CallerId>
</CrmAuthenticationToken>
</soap:Header>
<soap:Body>
<RetrieveMultiple xmlns="http://schemas.microsoft.com/crm/2007/WebServices">
<query xmlns:q1="http://schemas.microsoft.com/crm/2006/Query" xsi:type="q1:QueryExpression">
<q1:EntityName>new_customsetting</q1:EntityName>
<q1:ColumnSet xsi:type="q1:ColumnSet">
<q1:Attributes>
<q1:Attribute>new_customsettingid</q1:Attribute>
<q1:Attribute>new_name</q1:Attribute>
<q1:Attribute>new_value</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_name</q1:AttributeName>
<q1:Operator>Equal</q1:Operator>
<q1:Values>
<q1:Value xsi:type="xsd:string">Custom_Prefix</q1:Value>
</q1:Values>
</q1:Condition>
<q1:Condition>
<q1:AttributeName>new_name</q1:AttributeName>
<q1:Operator>Equal</q1:Operator>
<q1:Values>
<q1:Value xsi:type="xsd:string">Custom_Increment</q1:Value>
</q1:Values>
</q1:Condition>
<q1:Condition>
<q1:AttributeName>new_name</q1:AttributeName>
<q1:Operator>Equal</q1:Operator>
<q1:Values>
<q1:Value xsi:type="xsd:string">Custom_Pad</q1:Value>
</q1:Values>
</q1:Condition>
</q1:Conditions>
</q1:Criteria>
</query>
</RetrieveMultiple>
</soap:Body>
</soap:Envelope>

Notice that we now have to specify the new CrmAuthenticationToken settings in the soap:Header section. This is where we would set the authentication type and also the CRM Organization that we are performing the request against.

Also, the XML that is returned by the web service is a little different. Instead of the Business Entity nodes being named after CRM attributes, they are named "q1:<attributename>". So in order to parse the XML, we have to alter the selectNodes to this:

var value = xmlResponse.selectNodes("//BusinessEntity/q1:new_value");

Those are pretty much the only changes you'll need to make to get this to work in 4.0.

Wrap-Up

The beauty of this solution is that you can pop the JavaScript code anywhere in your CRM environment and either use the same number in multiple areas or easily create new auto-incrementing fields just by adding a new set of Custom Settings and updating your JavaScript to reflect those setting names. I have not noticed any real performance hit by running all the logic when opening the form, but your mileage may vary.

It is important to note that this only works when adding records through the CRM interface. Any method of data input that bypasses the entity form (like a Scribe data load or any other data import) renders this pretty useless. Here are a few more items to consider:

• You'll need to give your CRM users the ability to read/update the Custom Setting entity.
• Consider disabling the Account Number (or which ever attribute you implement this on) on the form. This would prevent any tampering.
• Don't use a prefix or other numbering scheme that is too similar to the tracking token feature in CRM. This could potentially cause some havoc if you ever embed the number in the subject of an email, etc.

Since I've done the bulk of the work in building this solution, I'll leave it to you to piece it together properly in the form's onLoad() event. Enjoy.

Comments

[Joel Lindstrom]

Thanks Will--this looks great. I think for the purposes that I originally asked the question, a sequential number will probably work best.

[Will Wilson]

That's a nice thing about this solution. You could easily leave out the prefix part and you've got a sequential number genrator right there!

In cases where business rules dictate a number based on Date, etc (almost like a serial number where individual digits have meaning), your solution presented earlier would be a much more effective way to solve the problem.

I think both ideas are pretty good, just depends on what your target application is.

[Joel Lindstrom]

that's what I'm saying--I think your solution is a better fit for the one that I posted for my situation. Now the next level--make it work server side or workflow based so it works for accounts not created via the front end (example: Scribe).

[Jeremy]

Hi,

I have read several informative blogs about auto-incrementing in CRM. The one thing I do not understand is not within the code but in the beginning where you state:

"What we're doing with the Custom_Prefix setting is specifying what prefix our custom counter should use. We have set it to be "PRE"."

How do I set it? Do I create 1 record and fill in the text with "PRE"? If so does the field have to be a certain type? E.g. nvarchar?