Back in the day, when this was just blog was just a baby blog, I wrote a post on generating random numbers in Salesforce.

Even though I wrote this function as a learning experience, I have received quite a few comments from folks who actually have a work need for this function.

The most recent was from Daniel, who wrote that his organization has three inventory groups, A, B and C. All groups get audited once per year. C Parts are counted once per year, B parts are counted twice per year. A parts are different though. A parts are selected at random for counting once per month.

So, there you have it! A real life example of why the random function should be included in the Salesforce formula library! <<VOTE HERE>>

So, I now dedicate the rest of this blog post to Daniel!

First things first, I created a new object called “Spacely Sprockets”. I have a text box for inventory grouping, where I have noted if the record is in A, B or C.

Next, I created three formula fields, “String Build”, “Dice Roll” and “Build Audit Grouping”.

The “String Build” formula is the same as my last post, and uses the “now” function to build out a string.

sqrt(

(value(

(left(right(text(now()),6),2))&

text(DAY(DATEVALUE(now())))&

text(Month(DATEVALUE(now())))&

text(Year(DATEVALUE(now())))&

(left(right(text(now()),9),2)))

)/100
)

The “Dice Roll” formula is a bit different than before. I found a couple of neat posts written after my original post that used the MOD function. This one is a bit deep, but check it out!

if(right(Name,2)=”00″,1,mod(mod(value(right(Name,2))*3,101)-1,10)+1)

 

I am using the right two digits of the autonumber of the record as my seed, with a correction on for AutoNumbers that end with “00”. Basically, I dump those to the dice roll of 1. This was done for the sake of time, but you could get fancier by moving these orphans to perhaps the month? Up to you!

The last formula is the “Build Audit Group” formula. What this one is going to do is use the MID function to locate a single number out of the string. To find where that number is, it is going to use the dice roll value ( 1 to 10) + the right digit of the autonumber (1 to 9) to determine which group (0 to 9) the record belongs. The reason I am using the autonumber as well is to further randomize things.

mid(text(String_Build__c),(Dice_Roll__c+value(right(Name,1))),1)

The results look promising, but I really need to emphasize that you more than likely, you will need to tweek this method! Even as I was building the function for this blog, I was finding little ways in which to improve things…but, since I have not found a good need for this yet (beside my rock / paper / scissors / lizard / spock game) I didn’t plan on spending a bunch of time refining it.

OK…is everyone still with me? Alrighty, let us take a look at some results. I have been running a simple matrix report on my Spacely Sprockets data that shows the audit group with the dice roll. I have run it 9 times over the last couple of days. As mentioned, the results are promising…not perfect yet for a robust random audit feature, but not bad.

The chart below definetely shows some preference, but a large part of this is related to the bug where the audit grouping is a “.” (Period) instead of a number. My guess is that if that bug was eliminated, the numbers would continue to flatten.

Here is a table with the selection totals by audit grouping:

This further firmed up my belief that if the period bug was eliminated the findings would be closer.

Hope this helps!

 

Andrew