Serial Number

[alhazen]

Hello guys,

I have a document number with this format CS/UM/2022/11/001. I want the last 3 digits of this number to have to continue to the next series when the user adds new data. Let's say CS/UM/2022/11/002. Is it possible?

Thank you for your help.

[alhazen]

I am using this formula for initial value:

"CS"&"/"&"UM"&"/"&YEAR([date])&"/"&MONTH([date])&"/"

[Aurelien]

Hi @alhazen 

Basically anything is possible, but serial numbers should be avoided.

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

For your question: you may need a column [number]

Your initial value expression would be:

"CS"&"/"&"UM"&"/"&YEAR([date])&"/"&MONTH([date])&"/" & (MAX(yourTable[Number])+1)

 For reference:

MAX() - AppSheet Help

[alhazen]

Hello @Aurelien Thank you for responding.

I am using this formula MAX( LIST( 253 ) + claim[Serial] ) + 1. 

Is it possible to reset LIST(253) to the LIST(001) when the years end?

[Aurelien]

---

@alhazen wrote:

Is it possible to reset LIST(253) to the LIST(001) when the years end?

---

Probably. It's up to you to define the mechanism for resetting it.

I would suggest to try this then:

"CS"&"/"&"UM"&"/"&YEAR([date])&"/"&MONTH([date])&"/" & 
(
MAX(
  SELECT(yourTable[Number],
    YEAR(TODAY())=YEAR([date])
  )
)+1
)

[alhazen]

Does the number column need an initial value?

[Aurelien]

you can set 1. It's really up to you actually.

[alhazen]

The formula you suggested doesn't work properly.

So I revised my formula to look like this: 

"CS"&"/"&"UM"&"/"&YEAR([date])&"/"&MONTH([date])&"/" & [serial]

I put this formula in serial column an initial value 

MAX(
SELECT(
claim[serial],
AND(
MONTH([date])=[_THISROW].[_month],
YEAR([date])=[_THISROW].[_year]
)
)
+
claim[serial])+1 

But I am confused how to auto reset when today is new month. Let's say I have a collection of the current month's numbers:

• CS/UM/2022/11/001
• CS/UM/2022/11/002
• est...So I want to be reset when today is a new month. It will be like this:

• CS/UM/2022/12/001
• CS/UM/2022/12/002
• est...

Is that doable? Do you have any suggestions? @Steve 

[Aurelien]

---

@alhazen wrote:

But I am confused how to auto reset when today is new month.

---

initial value for [serial] column:

IF(
  ISBLANK(
    SELECT(claim[serial],
      AND(
        MONTH([date])=[_THISROW].[_month],
        YEAR([date])=[_THISROW].[_year],
        [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
   ))),
  1,
  MAX(
    SELECT(claim[serial],
      AND(
        MONTH([date])=[_THISROW].[_month],
        YEAR([date])=[_THISROW].[_year],
        [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
      )))+1
)

[alhazen]

Hey @Aurelien 

Check out this for what I found

IF(
AND(
MONTH([date])=[_THISROW].[_month],
YEAR([date])=[_THISROW].[_year]),
MAX(
SELECT(
claim[serial],
AND(
MONTH([date])=[_THISROW].[_month],
YEAR([date])=[_THISROW].[_year]
),
)
)+1,
MAX(
SELECT(
claim[serial],
AND(
[_month]=[_THISROW].[_month],
[_year]=[_THISROW].[_year]),
)
)+1
)

[Aurelien]

Hi @alhazen 

this:

IF(
AND(
MONTH([date])=[_THISROW].[_month],
YEAR([date])=[_THISROW].[_year]),

Does not really make sense, as it is equivalent to:

IF(
AND(
MONTH([date])=[_month],
YEAR([date])=[_year]),

Hence your expression (that I indented for reading purpose - I encourage you to do the same in the future) would be like:

IF(
  AND(
   MONTH([date])=[_month],
   YEAR([date])=[_year]
  ),
  MAX(
    SELECT(claim[serial],
      AND(
        MONTH([date])=[_THISROW].[_month],
        YEAR([date])=[_THISROW].[_year]
      ),
    )
  )+1,
  MAX(
    SELECT(claim[serial],
      AND(
        [_month]=[_THISROW].[_month],
        [_year]=[_THISROW].[_year]
      ),
    )
  )+1
)

I assume [_month] and [_year] are virtual columns.

If these are MONTH([date]) and YEAR([date]), then you would want to reconsider this expression.

[alhazen]

Hey @Aurelien Glad you are still responding to this discussion

It works properly as my expectations.

correct, [_month] and [year] are virtual columns

Let me check for you suggestions