Increment unique form of ID automatically

[tvinci]

Hi AppSheet Community,

I have a list of IDs that look like this that I want to increment automatically:

So technically it is a “text” type. Is casting allowed in AppSheet so I can split the numbers by hyphen and increment the numbers every time I reach past the letter C?

Thanks

[Steve]

Why???

[tvinci]

These IDs correspond to positions within an object used to conduct a biological test on horses.

[Steve · Accepted solution]

Untested:

concatenate(
  (
    number(index(split(any(sort(My Table[ID], true)), "-"), 1))
    + switch(
      index(split(any(sort(My Table[ID], true)), "-"), 2),
      "4c", 1,
      0
    )
  ),
  "-",
  switch(
    index(split(any(sort(My Table[ID], true)), "-"), 2),
    "1a", "1b",
    "1b", "1c",
    "1c", "2a",
    "2a", "2b",
    "2b", "2c",
    "2c", "3a",
    "3a", "3b",
    "3b", "3c",
    "3c", "4a",
    "4a", "4b",
    "4b", "4c",
    "4c", "1a",
    ""
  )
)

[tvinci]

Wow @Steve, a million thanks to you Helpful as always!

[Steve]

Not really. It’ll fail with the transition from single- to double-digits, then from double- to triple-, and so on. Really need the number before the dash separate.