Count entries by month and create Entrycount this month/YY/MM

[Gerald]

Good morning.
I have a litte issue here, which i am not sure if it can be solved in a single formula:

I have a Date column and a task number column, which should have the following format:
“Number of Entries for this month”/YY/MM/
So in essence, if I have the first new task for, lets say August 2023 given by the date column, the tasknumber should look like 0001/08/23.
The second Task for August would then be 0002/08/23 etc.

So I have this part: year({datum}) &“/”&month({datum})

But yeah, that is still very far from what I need to do.
Any suggestions?
Thank you.

[AkDk7]

Hi.

The biggest problem you’ll have it the monthly rotating consecutively number. As far as i know there is no such mechanism in SeaTable yet, that resets the sequence for each month.
You would have to write a script, that orders all existing rows by it’s creation date, find the highest number of that sequence and increase it for each new row. You could achive that with a python script and automatism. The script should be started for each new row.

[Gerald]

Solved it like this:

const bse = base;
var rowid = bse.context.currentRow["_id"];
var vDate = bse.context.currentRow["v_Datum"];
var jsDate = new Date(vDate);
var month = jsDate.getMonth()+1; // JS gives numbers from 0 to 11
var year = jsDate.getFullYear()-2000; //need 2 digits, most simple solution is to subtract
var sql = 'select v_Monat from Auftrag where v_Monat =';
sql = sql + month+' AND Planung = "Genehmigt";';
//console.log(sql);
var count = await bse.query(sql);
count = count.length;
var table = bse.getTableByName('Auftrag');
var row = bse.getRowById(table, bse.context.currentRow['_id']);
if(month <10)
  month="0"+month;
var nr = count+"/"+ month+"/"+year;
sql= 'Update Auftrag Set FA_Nummer="'+nr+'" where _id ="'+rowid+'";'
var result = await bse.query(sql);