Is there a way to keep sequential numbers of items in a column that resets on Jan 1st?

AppSheet AppSheet Q&A
1

I have a list of consecutive jobs numbers that start every year.

The format is as follows:
2k19xxx
2k20xxx
2k21xxx

Description:
2k = code added in the year 2000
19, 20, or 21 - the year the job was started.
xxx = consecutive number from 001 to whatever number jobs are started that year

Right now, the “Jobs Schedule” is kept in a google sheet, and every year is kept in a separate tab - so creating the consecutive number codes is not a problem using a cell formula.

However, when using AppSheet I find it is more convenient to have all the jobs in a single sheet.

I am trying to use the following formula to auto create the job number:

“2K”&(Year(Today())) = 2k2021

Problems:

  1. I need the year to be only 2 digits - and I am getting 4 .
  2. I need to get a sequential number in 000 format after the year.

Do you have any advice on how to approach these two problems?

2

Felix.P_Tong:

I am trying to use the following formula to auto create the job number:> > “2K”&(Year(Today())) = 2k2021

Try:

("2K" & RIGHT(("" & YEAR(TODAY())), 2))

Or:

("2K" & TEXT(TODAY(), "YY"))

See also:

2 Likes
3

Thank you for the prompt response…

PS: I do not know how you all do it… but you are on top of it… Good Job…

1 Like
4

Felix.P_Tong:

I do not know how you all do it…

Literally years of practice.

6 Likes
5

Steve,

I have a follow up question related to the one above.

We have job numbers that are sequential using the following format:

2KYY000

2K = fix code.
YY= Year job was created.
000= sequential number: starts with 001 every Jan 1st.

Is there an easy way to create this with Appsheet?

PS: I thought there was a counter function I could use (Count()) in AppSheet. It turns out it does not work the way I expected…

6

First, read this:

[Serial Numbers, If You Must](https://community.appsheet.com/t/serial-numbers-if-you-must/19325) Tips & Tricks ?

Danger Ahead! In general, sequential numeric identifiers (i.e., serial numbers) are risky in AppSheet: if two users happen to add rows at the same time, both rows could be assigned the same serial number, which could lead to confusion (at the least) or data loss (at the worst). For this reason, serial numbers are strongly discouraged! Basic Serial Numbers One way to implement serial numbers is with a normal (not virtual) column named (e.g.) Serial of type Number and an Initial value expression …

Let us know if you don’t find an answer there.

1 Like
7

Steve,

Thank you for your response.

I think I understand the reason for the sequential number warning.
However, in this case only one person will be creating the job numbers.

Questions: (Regarding serial numbers using)

1-How can an initial value expression re-start every Jan 1st?
(That day is a holiday and there will not be any activity, or it may fall in the middle of a weekend )

2-Perhaps checking the value of the row above and the value of Today()?
(For some reason this does not sound very Appsheet like…)

Any suggestions?

8

Felix.P_Tong:

How can an initial value expression re-start every Jan 1st?

Maybe this?

CONCATENATE(
  ("2K" & TEXT(TODAY(), "YY")),
  RIGHT(
    CONCATENATE(
      "000",
      (
        NUMBER(
          RIGHT(
            ANY(
              SORT(
                SELECT(
                  table[column],
                  STARTSWITH(
                    [column],
                    ("2K" & TEXT(TODAY(), "YY"))
                  )
                ),
                TRUE
              )
            ),
            3
          )
        )
        + 1
      )
    ),
    3
  )
)

Replace table with the name of the table that contains these values; and replace column (twice) with the name of the column that contains these values in that table.

  1. SELECT(table[column], ...) gets the values in the column of the table from those rows that match the given condition (...; see (2)). See also SELECT()

  2. STARTSWITH([column], ...) matches those rows with a column value that starts with the given text (...) that should match only the current year. See also STARTSWITH()

  3. SORT(..., TRUE) sorts the results of (1) (...) in descending order (per TRUE), putting the most-recent-dated value first in the list. See also SORT()

  4. ANY(...) extracts the first item from the list produced by (3) (...). See also ANY()

  5. RIGHT(..., 3) extracts the three rightmost characters from the value given by (4) (...). See also RIGHT()

  6. NUMBER(...) + 1 converts the value given by (5) (...) to a Number value and adds one to that. If (5) gave a blank result (because no value for this year has been used), NUMBER() will give 0, to which 1 will be added. See also NUMBER()

  7. CONCATENATE("000", ...) creates a Text value from 000 followed by the result of (6) (...). This adds leading zeros to the numeric value from (6). See also CONCATENATE()

  8. RIGHT(..., 3) gets the three rightmost characters from the value given by (7) (...). This ensures the value has only three digits by removing any excess leading zeros.

  9. CONCATENATE(..., ...) produces the serial number of the desired format, consisting of the year in 2KYY format (the first ...), followed by a three-digit serial number distinct within that year from (8) (the second ...).

3 Likes