How to get a lead assigned to a random person in Salesforce

There isn’t a function to get a random number in the context of Salesforce formulas. The only way I know of is to base it on time. Specifically, the seconds part of the time.

If you create a formula with the expression below, you will get a random number (*) between 0 and 59, which is from the seconds of the time that the record was created:

VALUE( MID( TEXT( CreatedDate ), 18, 2 ) )

You can apply the MOD function to bring that number down to other ranges.

The expression below will result in a random 0 or 1:

MOD( VALUE( MID( TEXT( CreatedDate ), 18, 2 ) ), 2 )

You can then use that formula field to drive a lead assignment rule:

Rule Entries:

if Lead : Random Number EQUALS 1 Assign To User1

if Lead : Random Number EQUALS 2 Assign To User2

if Lead : Random Number EQUALS 3 Assign To User3

And if you want to assign one of 2 values randomically via a workflow field update, then the formula could be written as follows:

CASE( MOD( VALUE( MID( TEXT( CreatedDate ), 18, 2 ) ), 2 ) , 0, “First Choice”, 1, “Second Choice”, “Error” )

– –

(*) Is it really a random number?

Are we are safe to assume that the second when a person created the record is actually random?

The seconds in the record creation date/time are not strictly random. If you have an integration process creating these records, then many of them will be created at the same second and you will not be able to use the expression.

The alternative below may be better – it shifts the source of randomness to the second the record is queried – but then the problem will be: if you query more than one record at a time, it will generate the same “random” number for all records.

VALUE( MID( TEXT( NOW() ), 18, 2 ) )

So what to do? You can sum the second from both CreatedDate and NOW() and you will probably get a better number between 0 and 118:

VALUE( MID( TEXT( NOW() ), 18, 2 ) ) + VALUE( MID( TEXT( CreatedDate ), 18, 2 ) )

Or even this smaller expression to get a number between 0 and 99. The subtraction of date/time fields results in a number in the format <number of days>.<fraction of a day> So this formula is taking 2 digits from the fraction as the source of randomness.

VALUE( MID( TEXT( NOW() – CreatedDate ), 6, 2 ) )