Solved: Expression for radom number - Page 2

vacina_nesf · 08-16-2021 05:05 AM

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

Steve

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