function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
fifedogfifedog 

Random formula generator?

Has anyone have a nice way of creating a random number using a formula field? Here's our business case.

We're creating certification records in salesforce to track who are certified on our product. A benifit of this is they can call into our Support system and type in their Certification Number to be placed in front of the queue. However we don't want people to guess what the numbers may be from a sequence.
RBIRBI
Maybe unique, obscure, and un-guessable is as good as random.
You could use a function [right(contact_id,6)] and be pretty unique.
You could go 8 characters. Or you could concatenate the first couple characters of another field, like city, with it. Maybe even peel the day or month off create_date and concatenate that. Lots of obscure possibilities.
I'm assuming that you're going to have a custom field that stores the secret number on the contact record.
Ron HessRon Hess
What object will you locate this "random" field on?
standard or custom?
can the "number" contain letters?

Message Edited by Ron Hess on 12-07-2005 09:23 PM
fifedogfifedog
Right I forgot to add this one requirement... it needs to be a number of 4-6 characters.

It will live in a custom object (Certifications) which is a correlation table between Contacts and another custom Object (Registrations).

This number is going to be used for the Certification number we give out to people who have been certified on our product. This number then will be used when they call into our phone system to enable able them to jump to the begining of the queue.

Yes I've started thinking about grabing the created date / time fields doing some type of math on them while using the base number salesforce provides for the certification record it's self. However it would be better if there was a math function called "Random" :D
Ron HessRon Hess
i don't think you want a fucntion random() for this, each time you open the record the function would evaluate, changing the number...

so your not going to get far if you give out a number and then see it change next time you open the object.

so, here is what i came up with
create 2 auto number fields on your custom object
field1 = {MM}{DD}{0}
and
field2 = {0}{DD}{MM}

then a third formula field to multiply them and mod the result into your desired number of digits

mod( field1 * field2 , 100000)

i've not tried this, so let me know if it works...

Message Edited by Ron Hess on 12-08-2005 11:30 PM
fifedogfifedog
Ron,
Your right I wouldn't want a true random number every time. I was trying to do what you were suggesting however I was using only one seed value to come up with the number. I came close a couple of times but out of my 150 data set I always had one or two which were dups.

The largest problem I was having was the datatype. While trying to do calculations you need of type number but the Create Date is of Date/Time type. I could not find a way to convert to string/number. I do have my autonumber that is DDYY## to which is of type string and I was going nuts doing Value and Text convertions.

Ron thanks and I'll post again if it works.
Puneet MishraPuneet Mishra

Its 2013 but, no this won't work
Ezra Kenigsberg @ BCEzra Kenigsberg @ BC

In trying to declaratively generate numbers from 1 to 100 using Salesforce Record IDs I came up with the formula below.

I just inserted 5000 Leads using Data Loader, so I thought I'd get a uniform distribution of numbers (not random, but uniform).

As you can see, something (in how Salesforce processed the insert job, I guess) led to a doubly-high bulge between 62 and 95, and half the number of 1s and 100s:

Distribution of numbers between 1 and 100 driven by generated Salesforce IDs
I bet it has to do with low-level database operations. In any case, if you're interested, here's the formula I'm using to generate numbers from 1 to 100:

(
    (
        (
            (FIND(MID(Id, 14, 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz") - 1) * 62 +
            (FIND(MID(Id, 15, 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz") - 1)
        ) * 99
    ) / 3843
) + 1
What's going on here?
  • The two FIND() functions in the heart of the formula take the 14th and 15th characters of the Salesforce ID and convert them into a number between 0 and 3843 (which is the highest two-digit number in base62).
  • The three outer operations -- the * 99, / 3843, and +1 -- are what's used to convert
    • from a number that ranges from 0 to 3843
    • into a number that ranges from 1 to 100.
  • The conversion logic is cribbed from Stack Overflow (https://stackoverflow.com/questions/929103/convert-a-number-range-to-another-range-maintaining-ratio).
Like I said, not the uniform distribution I was expecting. But interesting!