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:
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.