Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // Airtable Auto-Number that resets every year script (Ver. 2.0, Added reset monthly and daily)
- // If you have any questions about this, reach me at devon[-at-]playfrey.tech
- // Add the following Inputs:
- // createdRecord: The record ID, trimmed, of the record that was created
- // recordDate: The date of the record that was in the creation trigger
- // For detailed instructions on how to use this script, check out this post here: https://community.airtable.com/t5/show-tell/auto-number-that-resets-every-year/td-p/49934
- //----Configuration
- let configTable = "Projects";//The name of the table that contains the fields
- let configDateField = "Created On";//Enter the field name, needs to be the date created
- let configYearAutonumberField = "Year ID";//Enter the field name, This is the single line text that shows the numeric project number for that period
- let configNumberOfDigits = 3;//The number of digits the output number needs to be. ( ex: "001" instead of just "1" )
- let configResetTime = "Y";//Y = Yearly, M = Monthly, D = Daily
- //===========================================
- //----That's It! Don't Edit Past Here--------
- //===========================================
- //----Variables
- let inputConfig = input.config();
- let createdRecord = inputConfig.createdRecord;
- let createdDate = inputConfig.recordDate;
- let createdYear = new Date(createdDate);
- createdYear = createdYear.getFullYear();
- let createdMonth = new Date(createdDate);
- createdMonth = createdMonth.getMonth() + 1;
- let createdDay = new Date(createdDate);
- createdDay = createdDay.getDate();
- //----Grab our project records
- let table = base.getTable(configTable);
- let queryResult = await table.selectRecordsAsync({
- sorts: [
- {field: configDateField, direction: "desc"},
- {field: configYearAutonumberField, direction: "desc"}
- ]
- });
- //----Create our list for creating our next number, filter out other years/months/days
- let checkRecords = new Array();
- let projectYear = "";
- let projectID = "";
- let projectMonth = "";
- let projectDay = "";
- for(let record of queryResult.records){
- projectYear = record.getCellValueAsString(configDateField)
- projectDate = new Date(projectYear);
- projectYear = projectDate.getFullYear();
- projectMonth = projectDate.getMonth() + 1;
- projectDay = projectDate.getDate();
- projectID = record.getCellValueAsString(configYearAutonumberField);
- if( configResetTime == "Y" ){
- if( projectYear == createdYear ){
- if( projectID !== "" ){
- checkRecords.push(projectID);
- }
- }
- }else if( configResetTime == "M" ){
- if( projectMonth == createdMonth ){
- if( projectID !== "" ){
- checkRecords.push(projectID);
- }
- }
- }else if( configResetTime == "D" ){
- if( projectDay == createdDay ){
- if( projectID !== "" ){
- checkRecords.push(projectID);
- }
- }
- }
- }
- //----Generate our new number
- let newNumber = 1;
- if( !(checkRecords === undefined || checkRecords.length == 0) ){
- newNumber = parseInt(checkRecords[0]) + 1;
- }
- newNumber = newNumber.toString();
- while( newNumber.length !== configNumberOfDigits ){
- newNumber = "0" + newNumber
- }
- //Update our record
- await table.updateRecordAsync(createdRecord, {
- [configYearAutonumberField]: newNumber,
- })
Advertisement
Comments
-
- Hi, I used your script on airtable to try and generate a new set of running number that refreshes everyday. However, each new record created was assigned the number '001'. The same goes for a monthly reset. For yearly reset, it works fine. Was there something that I have missed? I would really appreciate it if you could assist. Thanks
-
- I just updated the script to include a link to my post detailing how to implement it. Give this a look over to make sure you're not missing anything: https://community.airtable.com/t5/show-tell/auto-number-that-resets-every-year/td-p/49934
-
- for me its creating 001 for all records for all types of reset
- Not working for daily, yearly, monthly
-
- Make sure to follow the instructions on the airtable community post.
-
- Hi,
- the script was running fine for a period of time before it starts to duplicate sometimes and then it works again on and off. Below are test error codes but no clue where it go wrong as the recorddate it says 2025-04-06 but base have no such data (starts from 2025-04-30)
- I cannot email you but comment here. hopefully i can get your reply on this thank you.
- Regards,
- Jack
Add Comment
Please, Sign In to add comment