Generate Automated Index Numbers (AIN) (but NOT as Key replacement)

AppSheet AppSheet Q&A
1

Hi,

is it possible to autogenerate Automated Index Numbers (AIN)? I am the only user and the AIN are not the KEY. I need them to look like this:

W#0001
W#0002
W#0003
…
..
.

Is this possible?

Thanks
OldNo1

2

Yep. In Initial value:

CONCATENATE(
  "W#",
  RIGHT(
    CONCATENATE(
      "0000",
      (
        NUMBER(
          INDEX(
            SPLIT(
              ANY(
                SORT(
                  (table[column] - LIST("")),
                  TRUE
                )
              ),
              "W#"
            ),
            2
          )
        )
        + 1
      )
    ),
    4
  )
)

  1. table[column] gives a list of current values of the column. Replace table with the name of your table and column with the name of the column that has the AIN values. See also Construct a list from table-column references.

  2. (... - LIST("")) removes blank ("") and duplicate values from the list of all current AIN values (..., from (1)). Duplicates are removed automatically by list subtraction. See also Subtract values from a list.

  3. SORT(..., TRUE) sorts the list of all current AIN values (..., from (2)) in descending order (per TRUE), so that larger values come before smaller ones (e.g., W#0003 come before W#0001). See also SORT().

  4. ANY(...) gives the first value of a list, which, in this case, is the highest of the current AIN values (..., from (3)). See also ANY().

  5. SPLIT(..., "W#") splits the highest current AIN value (..., from (4)) around its expected prefix, W#. The resulting list should consist of two items: a blank (because nothing should occur before the prefix), and the numeric portion (e.g., 0003). See also SPLIT().

  6. INDEX(..., 2) gets the second item from the list (..., from (5)), which should be the numeric portion of the highest-numbered AIN. See also INDEX().

  7. NUMBER(...) converts the numeric portion of the highest-numbered AIN (..., from (6)) from a Text value to a Number so we can perform math on it below. See also NUMBER().

  8. (... + 1) adds 1 to the highest AIN number (..., from (7)), giving a new highest AIN value.

  9. CONCATENATE("0000", ...) converts the new highest AIN value (..., from (8)) from Number to Text and prefixes it with 0000. For instance, 234 becomes 0000234. See also CONCATENATE().

  10. RIGHT(..., 4) gets the rightmost 4 characters from the new highest AIN value (..., from (9)). This gives us the new highest value as a four-digit value with leading zeros. For instance, 0000234 becomes 0234. See also RIGHT().

  11. CONCATENATE("W#", ...) produces the complete new highest AIN value by prefixing the four-digit numeric portion (..., from (10)) with W#.

5 Likes
3

Thank you Steve! Works perfect! Thank you also for the explanation!

1 Like
4

A typical Steve solution. A neat, concise, very well formatted expression and equally succinct explanation.:+1:

2 Likes
5

Steve’s suggestion is perfect!!

I just want to STRONGLY impress upon you that this solution ONLY works because you are the sole user. Should your app progress and other users begin to use it - specifically the feature that generates these AIN numbers - the app WILL end up with duplicates.

It is impossible to internally generate sequential based numbers on the fly with a distributed app when there are multiple users involved.

Many have tried to solve this problem and spent hours, days and even weeks attempting to do so.

Just keep this in mind for the future!!

3 Likes