In the course of getting “Project X” on the road to AMAZING I had to figure out how to get a random number out of salesforce. Since I am not a coder (Clicks not Code FTW!) I went through the formula documentation on success.salesforce and could not find anything like the excel function “random”. The google machine had suggestions, but they were all code driven. This is an interesting post as well. (http://blogs.developerforce.com/developer-relations/2013/07/selecting-random-numbers-and-records-on-the-force-com-platform-part-1.html)

So, what’s a poor admin to do? Well, if option A doesn’t work and option B is not going to work, this admin gets out his thinking cap (Seattle Seahawks throwback, btw) and gets to work.

First, I had to think about what I was trying to do. Am I really trying to make a random number? Do random numbers exist at all? The truth is, I can give you random numbers all day long and more than likely, you will find (or think you have found) a pattern. Humans are exceptional at doing this, and it has been theorized this helps with the whole Fight or Flight instinct (Is that the bushes or is there really a tiger there?).The lesson here is that as long as the user thinks it is “random” it can be called random.

Thus, properly aligned on this concept, I go off on my merry and random way and cooked up a solution.

Now, before I go into pure SFDC formula awesomeness mode, I want to call out that this is probably my 50^th iteration (maybe slight exaggeration) of this functionality. BUT, I want to also call out that Salesforce is probably the best platform I know of for this type of work. Quite literally, I would have the tab view up on the screen and in a new chrome tab the formula field for constant tweek / refresh. So, back to the awesomeness.

I decided that I will need two things to get my random number:

1)      A pool of numbers

2)      A “dice” function

To generate the pool of numbers, I decided to use the NOW() function along with the square root function. The NOW() function gives me an ever changing value and the square root function gives me a way to expand on that changing value. At first, I tried using just the minute function to get my pool, but ran into issues because the numbers were quite large enough. That is not to say it wouldn’t have worked, but it just didn’t make me happy. Then, around PI day (3/14) I had an inspiration! I remembered that there were certain sequential dates that only occur once in a millennia like “11/12/13”. I already had a really REALLY good seed, the date itself. This is because NOW() returns Month/ Day/ Year Hour / Minutes / AM PM. That might be a bunch of text, but if you break it down a bit more, you will see where I am headed:

NOW() = 3/19/2014 9:25 AM = 3192014925 = 31920141625

That last value might look a little funny and that is because it is. I noticed that when I ran a formula to derive hour from now <(left(right(text(now()),9),2)))> the return result was actually not matching with what I saw. Turns out, the hour value was being formatted into 24 hour UTC.

Deep Breath! With all this said, what I have is this great formula that returns a number that WILL NEVER HAPPEN AGAIN (Kerplew, Mind Blown and it is only Thursday) and can now work my random magic on it, and by magic, I mean the square root function.

The reason I use this function is because, well, it generates a gnarly set of digits. According to the Wikipedia page, “In mathematics, a square root of a number a is a number y such that y² = a, in other words, a number y whose square (the result of multiplying the number by itself, or y × y) is a.^[1] For example, 4 and −4 are square roots of 16 because4² = (−4)² = 16.”, in other words, for every number, this is another number that when multiplied against itself will return the first number.

Thus, I get the following number formula with 9 digits after the decimal:

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

)

There are a couple things I need to call out:

1)      I put the “/100” in there because the returned number was so large and the differences (minutes) so minute (ha) that the square root function was not returning really good results. Once I threw that “/100” in there, I started to get much more amazing data.

2)      The order of operation starts with the minute function. This is to keep things more random. Basically, if you started with the Date values, the leading digits would be the same for 24 hours. This way, they are refreshed every minute.

Now that you have a pool of numbers, you would need to generate your “dice” criteria.

The dice is what selects from the pool the actual number. In excel, this is similar to the “RANDBETWEEN” function. I put together a quick dice criteria in excel so that I could demonstrate the randomness. It uses a combination of the length of the user’s first name & the numerical position of the first letter of the first name. What this means is that ANDREW = 6 digits and A = 1, so my “dice” number is 6-1. IF my first name was BART, then the “dice” formula would be 3 (BART = 5 digits and B = 2). I used this “dice” formula on my pool results, and for it being a thrown together criteria, I did see some very nice and random results. Unfortunately, chatter posts are not very nice with formatting that data, so you would need to look at the attached docs!

And thus, with this knowledge, you will be on your way to generating your own take on this, which is what will truly make your number random! Now, was this all just for fun? Nope! One possible application for this could be for the assignment of incoming records like leads or cases where you might not want a round robin or first in / first out rule setup.

 

Thanks for playing along! If you like this, be sure and share. If you have questions, well, let me know!