Hello Community,
I am using this formula to get this condition:
1. If there are no serial numbers within the range of 4999-9999, then the random number should be within the range of 4999-9999.
2. If the table already contains serial numbers within the range of 4999-9999, then generate a random number within the range of 10000-99999.
3. If the table already contains serial numbers within the range of 10000-99999, then generate a random number within the range of 100000-999999.
4. If the table already contains serial numbers within the range of 100000-999999, then generate a random number within the range of 1000000-9999999.
5. If the table already contains serial numbers within the range of 1000000-9999999, then generate a random number within the range of 10000000-99999999.
IF(
NOT(
IN(
[serial],
SELECT(
work_order[serial],
AND(
[serial]>=4999,
[serial]<=9999
)
)
)
),
RANDBETWEEN(4999,9999),
IF(
IN(
[serial],
SELECT(
work_order[serial],
AND(
[serial]>=4999,
[serial]<=9999
)
)
),
RANDBETWEEN(10000,99999),
IF(
IN(
[serial],
SELECT(
work_order[serial],
AND(
[serial]>=10000,
[serial]<=99999
)
)
),
RANDBETWEEN(100000,999999),
IF(
IN(
[serial],
SELECT(
work_order[serial],
AND(
[serial]>=100000,
[serial]<=999999
)
)
),
RANDBETWEEN(1000000,9999999),
IF(
IN(
[serial],
SELECT(
work_order[serial],
AND(
[serial]>=1000000,
[serial]<=9999999
)
)
),
RANDBETWEEN(10000000,99999999),
""
)
)
)
)
)
My questions:
1. Is it possible to get all the conditions above?
2. What formula should I combine so that the resulting number is not the one already in the table?
Thank you so much for your help
LinkedIn
Twitter
