How do I generate UNIQUE random numbers within a range?

[Lalit_Datta]

Let’s say for every new entry I make in my app, I want to generate a random 6 digit number for a column.

So for example, if I create one entry, I would get a number like 568392.

HOWEVER, I don’t want 568392 to show up again in that column on the table. I want all the numbers generated to be unique. I tried RANDBETWEEN(100000, 999999) but I don’t think that prevents duplicates. Any help?

[Steve]

Since there is no iteration functionality within expressions, you’ll have to use best-effort to hope to guess an available ID:

ANY(
  LIST(
    RANDBETWEEN(100000, 999999),
    RANDBETWEEN(100000, 999999),
    ...
    RANDBETWEEN(100000, 999999)
  )
  - table[column]
)

Replace table and column with the names of the table and column that contain the existing IDs.

The idea here is to generate a small set of random IDs (LIST(...)) and remove any that are already in use (- table[column]), hopefully leaving at least one available IDs.

[philippevg]

Thanks Steve, I was looking for an approach to this as well. Now in 2023, is this still the way to go? Just a thought: would it be possible to run an action/automation, triggered on submission, that generates a unique number by a script (a loop that looks up a random number until it doesn't find a match?). Just thinking out loud here, not a developer!

Thanks!