This blog is a bit of a departure in that it centers around a bit of code. I am not a coder. I cannot write jscript, .Net or register a plugin. I know CRM from a functional perspective and where code can be used to augment the functionality. I leave the actual writing of code to others. The advantage of this is it gives me great motivation to come up with unusual ways to solve problems. An example of this was my global search / universal search tool for CRM. Totally codeless, reasonably practical and very easy to maintain and configure.
Today I am looking at a random number generator.
For those of you still adverse to the curly braces and semi-colons, you can skip to the section where I talk about updating the random numbers en masse, which is codeless.
Why Do We Need a Random Number Generator?
It is an excellent question. I personally need it for another blog post I am planning to do on network analysis but another reason might be to create a random sample of contacts for a marketing campaign, or some other business case where a representative sample of records are needed but it would be impractical to use the entire set of records. Another reason might be to populate data for the testing of reports, dashboards or graphs.
Why Did You Abandon the Righteous Path of the Codeless?
I did try to do it without code using workflows (e.g. populating a number field with the current date and time) but this was not possible. With the ability to create custom workflow assemblies with the next major update I imagine a whole new world of workflow fun will open up. Now all we need are synchronous workflows, calculated fields and the dialog DB Query function in workflow and we are set.
How Do We Do It?
The first thing we do is create a field to hold the random number. In my case I have created the imaginatively labelled ‘Random Number’.
For transparency I added this to the form and to the default view.
Now we need the code.
The jscript Code
Thanks to Rhett Clinton, another aussie CRM MVP for the key bit of code:
var num = Math.floor(Math.random()*101);
While an aussie, he lives in the UK, so his quiet demeanour and respectful attitude often mean people confuse him for being English or a New Zealander.
So what is happening in this code line? Well we are taking a variable ‘num’ and setting it to a value. Starting from the inside and working out, Math.random() returns a number BETWEEN 0 and 1 i.e. never equal to them. Multiplying this by 101 gives us a number between 0 and 101. Math.floor rounds the result down and, as the result will always be greater than but not equal to zero and less than but not equal to 101, the result is a number between 0 and 100.
Next we need to assign this value to the field we have added to the form. To do this, I Binged about and came across Hosk’s Javascript Xrm.Page basics. Other than through his active online presence, I do not know Ben Hoskins. His blog suggests he likes a beer and calls it as he sees it which means I like him already.
Combining the gems from Hosk’s site with Rhett’s code and with the input of my colleagues Bhavin Mehta and Rafael Urbano you get the following:
function RandomNumber()
{
var num = Math.floor(Math.random()*101);
Xrm.Page.getAttribute("new_randomnumber").setValue(num);
}
Putting this into a web resource, we then attach it to a form event e.g. OnLoad and we are good to go.
Using OnLoad means every time the form is opened, the random number value is changed. This also means the form wants to be saved, even if nothing else has changed. If this proves impractical you could use the OnSave event instead.
The result is a field whose value changes every time you open the form, although it only triggers on the opening of the form (for OnLoad) or the saving of the form (for OnSave). The bulk edit form or changing values through other methods, such as workflow, do not trigger the script.
Other Approaches
Another approach would be a plugin. This would remove the need to open the form and could be triggered off, for example, the ticking of a box on the form. Therefore we would tick the box via mass editing or workflow and the random number code would be fired.
Mass Updating Values
The biggest problem in using jscript is when you need to update multiple records at the same time. Because jscript will only fire when the form is opened it means that you need to open up the form for every record you want to update. Another approach is to use data enrichment. The advantage of this approach is you can insert a random number onto the records with no code whatsoever.
Firstly hit the ‘Export to Excel’ button on the list of contacts you want to update.
For static exports you will see the option to re-import the data
Once we have it in Excel, we can use the Excel RANDBETWEEN() function to generate our random numbers. The function RANDBETWEEN(0,100) does the trick.
We then import our data with the import wizard and our records have random numbers associated to them.
NB: The numbers in the previous two screenshots are different only because I took the pictures over different runs. Usually they would be the same.
This approach is a little manual but has the advantage of not requiring the jscript to generate the random numbers.
Conclusions
There you have it, the ability to add a random number to a record. Using jscript the number will change every time you access the form or, via re-importing, the number will change every time you export and refresh the random number via Excel functions. Using a plugin allows for more options for updating the field. Enjoy!
