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:
Do you have any advice on how to approach these two problems?
Try:
("2K" & RIGHT(("" & YEAR(TODAY())), 2))
Or:
("2K" & TEXT(TODAY(), "YY"))
See also:
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โฆ
Literally years of practice.
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โฆ
First, read this:
Let us know if you donโt find an answer there.
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?
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.
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()
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()
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()
ANY(...)
extracts the first item from the list produced by (3) (...
). See also ANY()
RIGHT(..., 3)
extracts the three rightmost characters from the value given by (4) (...
). See also RIGHT()
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()
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()
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.
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 ...
).
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |