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?
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.
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!
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |