Generate “Random” Numbers Declaratively in Salesforce

Q: How can I use declarative configuration to generate random numbers in Salesforce?

A: The following field formula generates a number from 1 to 100 based on each record’s ID. It isn’t random, but with enough records created gradually over time I hope it’ll produce a reasonable-ish distribution between 1 and 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 a Stack Overflow post.

Testing it out, I just inserted 5000 Leads using Data Loader, which I thought would produce a uniform distribution of numbers (again, 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

My friend Chris Robertson applied it to a table with 3,906 records and got this:

. . . eh, still not great. Let me know what you get!

Better yet, let me know if you have any luck generating random numbers declaratively, say using other “random” number generators like CreatedDate and/or LastModifiedDate. I had no luck with them.

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 )

Facebook photo

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

Connecting to %s