Expression for radom number

vacina_nesf
Participant II

hi I’m running an app that makes a list of dogs, for the dog ID I need it to be “INCREASING NUMBER/ACTUAL YEAR” like 0001/2021, and i need that for every new year it restarts to 0 again

Solved Solved
0 9 270
1 ACCEPTED SOLUTION

Steve
Participant V

This one’s a challenge! Try this (it’s a long one; you’ll need to scroll to see it all):

CONCATENATE(
  RIGHT(
    CONCATENATE(
      "0000",
      (
        NUMBER(
          ANY(
            TOP(
              SORT(
                SPLIT(
                  SUBSTITUTE(
                    CONCATENATE(
                      SELECT(
                        table[column],
                        ENDSWITH([column], ("/" & YEAR(TODAY())))
                      )
                    ),
                    ("/" & YEAR(TODAY())),
                    ""
                  ),
                  " , "
                ),
                TRUE
              ),
              1
            )
            + LIST("-1")
            - LIST("")
          )
        )
        + 1
      )
    ),
    4
  ),
  ("/" & YEAR(TODAY()))
)

View solution in original post

9 REPLIES 9

Lavanya
Participant I

HI,
Better to use UNIQUEID() in initial value column

Refer this URL for more information

vacina_nesf
Participant II

i’m using uniqueid but i need a increasing id with a year, and i need that for every new year it restarts to 0 again

It’s just regular id or key column too?
I have the similar idea for key, but have to change it because of if two users create new row(form) at the same time you going to get the same id for both

Kindly refer this URL:

Steve
Participant V

Also reference:

Steve
Participant V

This one’s a challenge! Try this (it’s a long one; you’ll need to scroll to see it all):

CONCATENATE(
  RIGHT(
    CONCATENATE(
      "0000",
      (
        NUMBER(
          ANY(
            TOP(
              SORT(
                SPLIT(
                  SUBSTITUTE(
                    CONCATENATE(
                      SELECT(
                        table[column],
                        ENDSWITH([column], ("/" & YEAR(TODAY())))
                      )
                    ),
                    ("/" & YEAR(TODAY())),
                    ""
                  ),
                  " , "
                ),
                TRUE
              ),
              1
            )
            + LIST("-1")
            - LIST("")
          )
        )
        + 1
      )
    ),
    4
  ),
  ("/" & YEAR(TODAY()))
)

You are the best thanks

hi steve i’m getting a problem, it is stuck on 0002 now i don’t know why
3X_a_6_a6c1659d952f42f623bd4b5dcdcf3b87c26d4228.png
3X_9_6_96dbfb14f87fbc8c57ed5ab7baffed999633e2a1.png

vacina_nesf
Participant II

it’s only adding up the first record and not the last

Top Labels in this Space