APPSHEET + APPSCRIPT

[DjLeiiii]

Hi all, as you can see the column Deposit No is auto updated with value everytime a user add new data. but i found out my formula on google sheets are wrong as you can see today DATE is AUG. So i need help on this problem whether i should use appscript to runt the formula for the value or is there any better solution for my problem which i am facing right now?

[WillowMobileSys]

But the Month is August and you are using the TODAY() function.  Why is the Date incorrect?

[Steve]

This is not an AppSheet problem.

[DjLeiiii]

I am trying to get column "Deposit No" to be like "DP08(month)22(Year)000(running number based on month if next month it will reset from 1 again)"

[WillowMobileSys · Accepted solution]

To @Steve 's point, if this is an AppSheet app, then I would recommend to move this formula into AppSheet as an expression.  You will have better, well easier, control over how to construct that value.  You really want your sheet to act solely as the data source.

There isn't really a super easy way to reset the number.   But it's not too difficult.

One option is to inspect the existing Deposit No column values and derive the next value by doing something like this (not a real expression - just a psuedo-expression):

IF(<<no existing [Deposit No] for current Month>>,
     then <<set number to zero-0001>>,
     else <<find max number for current Month and add 1>>
)

 Another option is to create a Scheduled Automation that runs on the last day of each Month at 11:59:59 PM and resets a number column to 0001.  This number is then just used in the creation of the Deposit No.  Where this might run into trouble is if you need to back-date or forward-date a transaction.