Document number

AppSheet AppSheet Q&A
1

Hi, i want to make a document number

KKM/Year/Month/1

but if new month coming, it will reset to 1 back

for example,

for january, there are 10 document no,

KKM/2024/1/10

then 1 february it will reset to

KKM/2024/2/1

how can i do it?

2

You will need to make Document Number a column with an expression to set it by querying previously entered documents using the Year and Month of the current row. Something like:

Assign this to the Document Number column:


If (COUNT(Documents[Document Number], AND([Year] = [_THISROW].[Year], [Month] = [_THISROW].[Month])) = 0,
     1,
     MAX(SELECT(Documents[Document Number], AND([Year] = [_THISROW].[Year], [Month] = [_THISROW].[Month]))) + 1
)

Basically, check for exiting doc rows for this Year and Month. If none, then use 1. Otherwise, grab the maximum number for this Year and Month and ADD 1 to it.

NOTE: I typed this expression in fresh and it is not tested. You will need to adjuste it for YOUR table columns and Its possible you may need to correct syntax errors.