Randbetween()

[alhazen]

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

[Aurelien]

Hi @alhazen 

You may want to use the IFS() expression.

---

@alhazen wrote:

Is it possible to get all the conditions above?

---

You probably got it already, but you will need to get it reverse:

IFS(
  IN([serial],SELECT(work_order[serial],AND([serial]>=1000000,[serial]<=9999999))),
  RANDBETWEEN(10000000,99999999),

  IN([serial],SELECT(work_order[serial],AND([serial]>=10000,[serial]<=99999))),
  RANDBETWEEN(100000,999999),
  
  IN([serial],SELECT(work_order[serial],AND([serial]>=4999,[serial]<=9999))),
  RANDBETWEEN(10000,99999),

  NOT(IN([serial],SELECT(work_order[serial],AND([serial]>=4999,[serial]<=9999)))), 
  RANDBETWEEN(4999,9999),

  TRUE,
  ""
)

---

@alhazen wrote:

What formula should I combine so that the resulting number is not the one already in the table?

---

That looks pretty impossible to me with AppSheet. If it was a script, I would go with a recursive function. You may want to try that instead?

[Marc_Dillon]

---

@Aurelien wrote:

That looks pretty impossible to me with AppSheet. If it was a script, I would go with a recursive function.

---

Action loops are basically tail-recursion. I think you could definitely create an action loop to generate a random number, check if it exists in the table already, if so then repeat.

Kind of silly though, what's the point of these random numbers, and why are they in a column named "serial"?

https://community.appsheet.com/t/serial-numbers-if-you-must/19325

[Aurelien]

Good catch @Marc_Dillon , I forgot about this 😅

Thanks for correcting me!