using a formula field to generate random numbers in Salesforce

Standard

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 50th 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 y2 = 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 because42 = (−4)2 = 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!

Advertisements

13 thoughts on “using a formula field to generate random numbers in Salesforce

  1. Daniel Rich

    This is a fascinating solution to a major SFDC omission. I have a need for a random number generator, based upon set criteria (sort of an if-then proposition). Care to help explore it? The business problem is for parts inventory cycle counts, which occur 48 times a year, plus an EOY inventory. There are 1300 parts in the storeroom. Each part is assigned a usage code, A,B,C. All parts are counted EOY. C parts are counted once a year in the 48 period cycle, B parts are counted twice annually, once in the first two quarters, once in the last two quarters. A parts are counted randomly, once each quarter. This isn’t bad in Excel, using the random function described in your article. Without a random feature in SFDC it’s a real bear. Thanks for the help.

  2. Peter Lyons

    I finally found a tighter solution. In my case, I needed to roll dice. Datetime fields don’t record milliseconds, but the now() function in Apex stores it. If we accept the second and milliseconds when a user clicks a button is sufficiently random, this can be achieved declaratively with flow. If we do (now()-datetimevalue(today())*86400) we’ll get an integer with 3 decimals indicating how many seconds/milliseconds it’s been since midnight. Next, we truncate to the second, subtract that from the original, and multiply by 10k. (trunc((now()-datetimevalue(today())*86400) ,2) – (now()-datetimevalue(today())*86400)) *10000). This gives a 5 digit number with 100k random combinations reset every 10 seconds. Combine with modulus to get any random number you need. This avoids quasi-random elements such as record ids which are sequential. and yields a really even distribution for any random integer less than 5k or so.

    • Hi Peter, Thanks for commenting! This has to be one of the most looked at pages on my blog ever and I am glad it still generates conversation!

      Very cool stuff – I am glad you shared this and hopefully, you have a blog where you can expand out further. The neat thing with the idea of using apex is that you could call it within a flow whenever you need it rather than it sitting on objects.

      Again, thanks for sharing! Comments like these inspire me to keep finding the oddball stuff to blog about…which, I know I need to do more of.

      • Peter Lyons

        We don’t actually need to write Apex, that’s the beauty of flow — it IS Apex. We leverage this fact to return the milliseconds in the form of a number field. There are plenty of options out there for using stuff like @invokablemethod and such to get crypto.randomnumber() for a flow, but for me this was all about winning one for clicks not code. I don’t currently blog, but I’m most visible on the Wave Analytics success community (which I guess they’re calling Einstein now).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s