SERIAL NUMBER -- BASED ON JOB DATE

[KathB27]

Hi Appsheet Community,

I am having trouble to configure the formula for the Job No. Column..itshould start with SCW then current year - month when the job will be done then followed by the serial number.

The month should follow the job date month...then if the month changes the serial no. should return to 001 but it just continues . Please need advise on the correct formula.

[franc]

Hi @KathB27 

I suppose that your job number should be unique and therefore you might want to check any possible inconvenience with serial numbers that are described very well here:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Serial-Numbers-If-You-Must/m-p/286300#M1275

Regarding your formula, a few comments first and then, if you need it, I can pass you a formula.

Concatenate(): you don't need to use two concatenates, since you can concatenate as many elements as you want and the second one can be avoided 

Right(): it serves to modify text starting from a position but, to me, it doesn't make much sense the way you are using it now.

If I have understood what you are trying to achieve, I'd check IF() the month of this row is equal to the previous row and, on this base, I'd add a +1 if it is the same or I'd assign a "001" if it is different, to restart the enumeration for the new month. This IF() formula can go in the same CONCATENATE() too

Is this what you want to achieve right?

[dbaum]

We crossed messages, but @franc explained well the premise underlying the expression I drafted for you, @KathB27.

---

@franc wrote:

Concatenate(): you don't need to use two concatenates, since you can concatenate as many elements as you want and the second one can be avoided 

Right(): it serves to modify text starting from a position but, to me, it doesn't make much sense the way you are using it now.

---

FWIW, I think this can be a handy technique where you need to pad a string with leading characters without knowing the precise length of the string--i.e., in this case, add 2 leading zeros to the ID number, which might be 1, 2, or 3 characters itself, and then retain only the 3 rightmost characters.

[KathB27]

Hi @franc 

Exactly!!😁

If I have understood what you are trying to achieve, I'd check IF() the month of this row is equal to the previous row and, on this base, I'd add a +1 if it is the same or I'd assign a "001" if it is different, to restart the enumeration for the new month. This IF() formula can go in the same CONCATENATE() too

Is this what you want to achieve right?

[franc]

To simplify your formula, try:

1. create a virtual column MONTH, whose value is MONTH(TODAY())

2. use a version of this formula: 

CONCATENATE("SCW", TEXT(TODAY(), "YY-MM"), IF([_THISROW].[MONTH]=[_THISROW_BEFORE].[MONTH], NUMBER(RIGHT([_THISROW_BEFORE].[SERIAL], 3))+1, "001"))

it has a few problems though: 

• you don't get a proper enumeration, since it would be 001, 002 but 001, 2, 3... 
• if you delete the previous row, the formula wouldn't work properly

so you need to adapt to something like this, that would work in a spreadsheet but you need to adapt for Appsheet ( sorry, I am in a hurry and I can't finish either try these formulas)

CONCATENATE("SCW", TEXT(TODAY(), "YY-MM"), IF([_THISROW].[MONTH]=IFERROR([_THISROW_BEFORE].[MONTH], ""), IF(LEN(RIGHT([_THISROW_BEFORE].[COLUMN_NAME], 3)+1)=3, LEN(RIGHT([_THISROW_BEFORE].[COLUMN_NAME], 3)+1), IF(LEN(RIGHT([_THISROW_BEFORE].[COLUMN_NAME], 3)+1)=2, CONCATENATE("0", RIGHT(C3, 3)+1), CONCATENATE("00",RIGHT([_THISROW_BEFORE].[COLUMN_NAME], 3)+1))), "001"))

In Google Sheet would be 

the initial value doesn't change, SCW22-08, since I maintained TODAY() to make it quickly

CONCATENATE("SCW", TEXT(TODAY(), "YY-MM"), IF(MONTH(D4)=IFERROR(MONTH(D3),""), IF(LEN(RIGHT(C3, 3)+1)=3, LEN(RIGHT(C3, 3)+1), IF(LEN(RIGHT(C3, 3)+1)=2, CONCATENATE("0", RIGHT(C3, 3)+1), CONCATENATE("00",RIGHT(C3, 3)+1))), "001"))

[franc]

just a quick comment on my "quick and dirty" formula 🙂 : you can shorten it by using the RIGHT + CONCATENATE expressions, the same way you are already doing in your original formula. Something like this:

CONCATENATE("SCW", TEXT(TODAY(), "YY-MM"), IF([_THISROW].[MONTH]=[_THISROW_BEFORE].[MONTH], RIGHT(CONCATENATE("000", NUMBER(RIGHT([_THISROW_BEFORE].[SERIAL], 3))+1), 3), "001"))

but I prefer @Steve's formula because it is elegant and it also uses SORT() which should avoid some problems in case a row is deleted, even though it probably wouldn't cover all the different possible cases. 

[KathB27]

Hi @franc ,

Thank you so much for your time and for being generous to share your knowlege. Your formula worked in changing the serial no. back to 001 when i entered the job no to September....However, August and September stays in 001...it does not continue anymore...

Btw, the month in the job no. SCW22-MM001 is based on the job date.

[franc]

@KathB27 if the number keeps at 001 means that this part of the full formula is ALWAYS equal to false: [_THISROW].[MONTH]=[_THISROW_BEFORE].[MONTH]

so first check if you have created the new virtual column correctly, as indicated in my previous reply. Then check the result of this full formula, and each of its parts, through the Expression Assistant and finally, if you haven`t spotted any other problem, try changing

CONCATENATE("SCW", TEXT(TODAY(), "YY-MM"), IF([_THISROW_BEFORE].[MONTH]=[_THISROW_AFTER].[MONTH], RIGHT(CONCATENATE("000", NUMBER(RIGHT([_THISROW_BEFORE].[SERIAL], 3))+1), 3), "001"))

[KathB27]

Hi @franc 

I did create the virtual column MONTH...the formula didnt accept it as DATE so i need to change it to NUMBER.

Maybe it has something to do with the [SERIAL] in the formula because i do not have that column so i changed it to [JOB NO] however i still get the same result.

CONCATENATE("SCW", TEXT(TODAY(), "YY-MM"), IF([_THISROW_BEFORE].[MONTH]=[_THISROW_AFTER].[MONTH], RIGHT(CONCATENATE("000", NUMBER(RIGHT([_THISROW_BEFORE].[SERIAL], 3))+1), 3), "001"))

[franc]

can you share a spreadsheet with no confidential information in it? just to try the formulas and try to understand where the problem is

[KathB27 · Accepted solution]

Hi @franc / @Steve / @Suvrutt_Gurjar / @dbaum 

Below formula worked😁...thank you all so much for your untiring help!! 😍

CONCATENATE(
"SCW",
TEXT([Job Date /Time], "YY-MM"),RIGHT("00"&
(COUNT(FILTER(TRANSPORT TICKET,MONTH([Job Date /Time])=MONTH([_THISROW].[Job Date /Time])))+1),3
)
)

[Suvrutt_Gurjar]

@KathB27 ,

If you have used the formula from my post that I deleted shortly after posting , then please note the following:

I retracted back the post because the formula will create duplicate job nos if you delete a job number previously created. Because the expression solely relies on number of count records in the said month.

[KathB27]

Hi @Suvrutt_Gurjar ,

I have tried that as well so i just created another action button to just CANCEL the trip instead of deleting it. 

[Suvrutt_Gurjar]

Okay, great. Nice to know that.

[dbaum]

If you have multiple users creating rows in your Jobs table--especially if they're not syncing immediately--, then it's not dependable to rely on sequential serial numbers. See Serial Numbers, If You Must - Google Cloud Community .

If you need to try to retain your current syntax for Job No values, in your expression try replacing "[_RowNumber] - 1" with the following:

IF(

CONTAINS(LOOKUP([_THISROW].[_RowNumber] - 1, "Jobs Table Name", "[_RowNumber]", "Job No"), TEXT(TODAY(), "YY-MM")), 

  NUMBER(RIGHT(LOOKUP([_THISROW].[_RowNumber] - 1, "Jobs Table Name", "[_RowNumber]", "Job No"), 3)), 

  0

  ) +

1

[KathB27]

Hi @dbaum / @franc,

Thank you very much for your response. I really really appreciate it!🙏

I have tried your advise but it seems it's still giving me the same result or maybe i used the formula wrong. 

My first photo below is the list of all the Job No for the month of August with the last number ending in 005.

While the 2nd photo is a test...i entered a job for the month of September but the last 3 numbers still continued the serial number from August to 006

The correct job no should be SCW22-09001 since it's a new month

CONCATENATE("SCW",TEXT([Job Date / Time],"YY-MM")&RIGHT(CONCATENATE("00",IF(

CONTAINS(LOOKUP([_THISROW].[_RowNumber] - 1, "TRANSPORT TICKET", "_RowNumber", "Job No"), TEXT(TODAY(), "YY-MM")),

NUMBER(RIGHT(LOOKUP([_THISROW].[_RowNumber] - 1, "TRANSPORT TICKET", "_RowNumber", "Job No"), 3)),

0

) +
1),3))

My table:

[Steve]

CONCATENATE(
  "SCW",
  TEXT([Job Date / Time], "YYMM"),
  RIGHT(
    CONCATENATE(
      "000",
      (
        NUMBER(
          RIGHT(
            ANY(
              SORT(
                SELECT(
                  table[Job No],
                  STARTSWITH([Job No], LEFT([_THISROW].[Job No], 7))
                ),
                TRUE
              )
            ),
            3
          )
        )
        + 1
      )
    ),
    3
  )
)

[KathB27]

Hi @Steve 

Thanks sooo much for taking the time and for sharing your knowledge.

I have tried your formula, however i still got the same result....or maybe i did something wrong again. The serial no. is still continuous...

CONCATENATE(
"SCW",
TEXT([Job Date /Time], "YY-MM"),
RIGHT(
CONCATENATE(
"000",
(
NUMBER(
RIGHT(
ANY(
SORT(
SELECT(
TRANSPORT TICKET[Job No],
STARTSWITH([Job No], LEFT([_THISROW].[Job No], 7))
),
TRUE
)
),
3
)
)
+ 1
)
),
3
)
)

[Steve]

Note that the values for pre-existing columns will not be automatically updated--only new rows will be given job IDs using the new method. Did you try adding new rows?

[KathB27]

Hi @Steve 

Yes, i deleted the previous records and created a new one after i changed the formula but it still gives the same result.

CONCATENATE(
"SCW",
TEXT([Job Date /Time], "YY-MM"),
RIGHT(
CONCATENATE(
"000",
(
NUMBER(
RIGHT(
ANY(
SORT(
SELECT(
TRANSPORT TICKET[Job No],
STARTSWITH([Job No], LEFT([_THISROW].[Job No], 7))
),
TRUE
)
),
3
)
)
+ 1
)
),
3
))

[dbaum]

You're in expert hands with @Steve, and he may correct me when he has a chance to reply, but fwiw my diagnosis of the problem is that STARTSWITH([Job No], LEFT([_THISROW].[Job No], 7)) depends on referencing a value that hasn't yet been created--namely, the [Job No] column itself that you're trying to generate in the new row.

If I'm misapprehending the issue with your expression, my next guess is that your LEFT function needs the first 8 characters, not the first 7 characters. 

Anyway, I'd try replacing your STARTSWITH function with CONTAINS and explicitly re-create the year-month reference rather than trying to dynamically reference it from the value for which you're writing this expression. If you prefer to stick with STARTSWITH instead of CONTAINS, then prepend the date in the following expression with "SCW".

CONTAINS([Job No], TEXT(TODAY(), "YY-MM"))