Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 14, 2022 03:36 AM
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 :
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
Dec 14, 2022 05:55 AM
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"
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)
Dec 14, 2022 06:20 AM
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.