Count counting

[Jafleu]

Hi all,
how do I generate a number with letters as a quote number?
the counter needs to add by 1.
ex:
JB-000001
next one will be
JB-000002
and so on.

thanks in advance

[Steve]

First, read this:

help.appsheet.com

Sequential Keys

A technical rationale for why AppSheet can't support sequential keys.

[Steve]

Second:

(
  "JB-"
  & RIGHT(
    (
    "000000"
    & (
      NUMBER(
        RIGHT(
          INDEX(
            SORT(
              QuotesTable[QuoteNumber],
              TRUE
            ),
            1
          ),
          6
        )
      )
      + 1
      )
    ),
    6
  )
)

1. QuotesTable[QuoteNumber] gets a list of all existing quote numbers.

2. SORT(..., TRUE) sorts the list from (1) in descending order so the highest quote number is first.

3. INDEX(..., 1) gets the first item in the list from (2), which is the highest quote number.

4. RIGHT(..., 6) gets the rightmost 6 characters from the quote number from (3). According to your examples, the rightmost 6 characters of the quote number are the numeric part.

5. NUMBER(...) converts the Text value from (4) to a Number value.

6. (... + 1) increments the numeric value from (5) by 1.

7. ("000000" & ...) creates a Text value consisting of six zeros and the number from (6). This ensures the quote number will have leading zeros if the number itself is fewer than six digits long.

8. RIGHT(..., 6) gets the rightmost 6 digits from the text from (7). This trims any excess leading zeros from the quote number, leaving a 6-digit quote number.

9. ("JB-" & ...) prefixes the number from (8) with “JB-”.