+ 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