Autonumber - reset each day to 1?

[Andrew_Davies]

Hello all

I have a "maintenance log" and have created an ID field with date / autonumber (ie - 14th December #1)

The autonumber I am doing with a simple autonumber field, but I am wondering if there is a way of resetting the number to 1 each day just to keep it clean.  It would also be a good way of seeing how many issues are created each day.

I have tried the following with no success :

• Add a "Next ID" field - which is the current ID plus 1
• Create an automation which searches records created today only

Intention was to find the most recent record - then use the "Next ID" field to populate the "Current ID" field - but I can't limit my search to just 1, most recent record.

Trying to avoid using Make / Integromat if I can.

Any thoughts?
Andrew

[Marielle_Gueis1]

Hi there @Andrew_Davies , 

You could indeed go in the direction of an automation (at least, that's what I would do). Nevertheless, the ID would be a formula field, with today's date, and a number field that would be populated as follows: 

First, you create a formula field with the formula CREATED_TIME(), let's call it Created At
Second, you create a new view in your table, where you'll see only the issues created today. To do so, set the filters to: "Created at is today"
Third, you sort the records so that the latest is at the bottom, by setting "Created at 1->9"

Then you create an automation, with:

- Trigger: When record created
- Step 2: create a script

let table = base.getTabe('YOUR_TABLE_NAME');
let view = table.getView('YOUR_VIEW_NAME');
let viewQuery = await view.selectRecordsAsync();
let newNumber = 1

if (view_query.records.length > 0 ) {
    let lastRecord = view_query.records[(view_query.records.length)-1]
    let lastNumber = lastRecord.getCellValue('YOUR_FIELD_NAME')
    newNumber = lastNumber + 1
} 

output.set('New Number', newNumber)
 

 Step 3: Update record
Select your table, and in Record ID, insert the record ID from the trigger step, select your Number field and insert the number calculated in step 2. 

I hope that helps! 

[kuovonne]

There are various ways to have an automation fill in an editable number field. If you will always have less than 100 records per day and there will be at least several seconds between the creation of records, you do not need scripting.

Have a formula field that formats the created time as a date string that also takes into account your current timezone. Have an automation that runs when a record is created that finds records with a matching created date string. Then update the record with the number of found records.

You could do something similar with a filtered view that shows only the records for the current day, but you may run into timezone issues that throws off your count. I also like to limit the number of helper views and fields in a base. 

If you want to do data analysis on the records per day, you may also want to consider having a “dates” table with one record per date. Link the new log records to their own date record, and the. You can do rollups to see things like the total number of logs versus logs of different types.