devonhg

Airtable: Auto number that resets regularly

Oct 30th, 2021 (edited)
3,350
0
Never
5
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // Airtable Auto-Number that resets every year script (Ver. 2.0, Added reset monthly and daily)
  2.  
  3. // If you have any questions about this, reach me at devon[-at-]playfrey.tech
  4.  
  5. // Add the following Inputs:
  6. // createdRecord: The record ID, trimmed, of the record that was created
  7. // recordDate: The date of the record that was in the creation trigger
  8.  
  9. // 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
  10.  
  11.  
  12. //----Configuration
  13.     let configTable = "Projects";//The name of the table that contains the fields
  14.     let configDateField = "Created On";//Enter the field name, needs to be the date created
  15.     let configYearAutonumberField = "Year ID";//Enter the field name, This is the single line text that shows the numeric project number for that period
  16.     let configNumberOfDigits = 3;//The number of digits the output number needs to be. ( ex: "001" instead of just "1" )
  17.     let configResetTime = "Y";//Y = Yearly, M = Monthly, D = Daily
  18.  
  19. //===========================================
  20. //----That's It! Don't Edit Past Here--------
  21. //===========================================
  22.  
  23. //----Variables
  24.     let inputConfig = input.config();
  25.     let createdRecord = inputConfig.createdRecord;
  26.     let createdDate = inputConfig.recordDate;
  27.  
  28.     let createdYear = new Date(createdDate);
  29.     createdYear = createdYear.getFullYear();
  30.  
  31.     let createdMonth = new Date(createdDate);
  32.     createdMonth = createdMonth.getMonth() + 1;
  33.  
  34.     let createdDay = new Date(createdDate);
  35.     createdDay = createdDay.getDate();
  36.  
  37. //----Grab our project records
  38.     let table = base.getTable(configTable);
  39.     let queryResult = await table.selectRecordsAsync({
  40.         sorts: [
  41.         {field: configDateField, direction: "desc"},
  42.         {field: configYearAutonumberField, direction: "desc"}
  43.         ]
  44.     });
  45.  
  46. //----Create our list for creating our next number, filter out other years/months/days
  47.     let checkRecords = new Array();
  48.     let projectYear = "";
  49.     let projectID = "";
  50.     let projectMonth = "";
  51.     let projectDay = "";
  52.  
  53.     for(let record of queryResult.records){
  54.         projectYear = record.getCellValueAsString(configDateField)
  55.         projectDate = new Date(projectYear);
  56.  
  57.         projectYear = projectDate.getFullYear();
  58.         projectMonth = projectDate.getMonth() + 1;
  59.         projectDay = projectDate.getDate();
  60.  
  61.         projectID = record.getCellValueAsString(configYearAutonumberField);
  62.  
  63.         if( configResetTime == "Y" ){
  64.             if( projectYear == createdYear ){
  65.                 if( projectID !== "" ){
  66.                     checkRecords.push(projectID);
  67.                 }
  68.             }
  69.         }else if( configResetTime == "M"  ){
  70.             if( projectMonth == createdMonth ){
  71.                 if( projectID !== "" ){
  72.                     checkRecords.push(projectID);
  73.                 }
  74.             }            
  75.         }else if( configResetTime == "D"  ){
  76.             if( projectDay == createdDay ){
  77.                 if( projectID !== "" ){
  78.                     checkRecords.push(projectID);
  79.                 }
  80.             }            
  81.         }
  82.     }
  83.  
  84. //----Generate our new number
  85.     let newNumber = 1;
  86.     if( !(checkRecords === undefined || checkRecords.length == 0) ){
  87.         newNumber = parseInt(checkRecords[0]) + 1;
  88.     }
  89.  
  90.     newNumber = newNumber.toString();
  91.  
  92.     while( newNumber.length !== configNumberOfDigits ){
  93.         newNumber = "0" + newNumber
  94.     }
  95.  
  96.  
  97. //Update our record
  98.     await table.updateRecordAsync(createdRecord, {
  99.         [configYearAutonumberField]: newNumber,
  100.     })
  101.  
Advertisement
Comments
  • zweizag
    3 years
    # text 0.33 KB | 1 0
    1. 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
    • devonhg
      3 years
      # text 0.23 KB | 0 0
      1. 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
  • ashish4114
    3 years (edited)
    # text 0.10 KB | 0 0
    1. for me its creating 001 for all records for all types of reset
    2. Not working for daily, yearly, monthly
  • Jack_Lim
    1 year
    # text 0.38 KB | 0 0
    1. Hi,
    2.  
    3. 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)
    4.  
    5. I cannot email you but comment here. hopefully i can get your reply on this thank you.
    6.  
    7. Regards,
    8. Jack
Add Comment
Please, Sign In to add comment