1. 
   

   Create a Formula field with this formula: DATETIME_FORMAT(CREATED_TIME(), 'YY')

   
   


2. 
   

   Create an Automation that triggers when a record matches conditions with the condition being ^that field is not empty AND {Number} is empty

   
   


3. 
   

   Add a Run a script action step with this setup:

   
   

   Input variables

   
   
   
   
   • Name: year, Value: the value of the year created formula field
     
   
   
   
   

   Script

   
   

    // Retrieve the year the new record was created

  const { year } = input.config()



  // Load all the records which were created the same year

  const table = base.getTable("Table 1")

  const query = await table.selectRecordsAsync()

  const records = query.records.filter(record => record.getCellValue("Year Created") === year)



  // Construct a new "autonumber" in the format: YY-###

  const newNumber = year + "-" + String(records.length).padStart(3, "0")



  // Output the autonumber for use in the next steps

  output.set("newNumber", newNumber)

4. Add a Update record action step that inserts the output from the above script into your {Number} field.

    // Retrieve the year the new record was created

  const { year } = input.config()



  // Load all the records which were created the same year

  const table = base.getTable("Table 1")

  const query = await table.selectRecordsAsync()

  const records = query.records.filter(record => record.getCellValue("Year Created") === year)



  // Construct a new "autonumber" in the format: YY-###

  const newNumber = year + "-" + String(records.length).padStart(3, "0")



  // Output the autonumber for use in the next steps

  output.set("newNumber", newNumber)

Hi Kamille. I really appreciate you digging into this! It seems to be close to working but it’s not finding the greatest existing inquiry number available (21-846) and instead generated 21-108.

I would expect the next number to be 21-847

What its doing is finding the count of records made in the appropriate year, which in this instance would be 108. If you instead need to add 1 to the maximum number found, try doing the following:

Delete the const newNumber... line, and replace it with the following:

const allNumbers = records.map(x => Number(x.getCellValue("Number") ? x.getCellValue("Number").substring(3) : 0))

const maxNumber = allNumbers.length > 0 ? allNumbers.reduce((a, b) => {

   return Math.max(a, b);

}) : 0

const newNumber = year + "-" + String(maxNumber + 1).padStart(3, "0")

Ah, that makes sense. We don’t actually number inquiries like that, however.

They are a continual list of unique numbers disconnected from the year.

const allNumbers = records.map(x => Number(x.getCellValue("Number") ? x.getCellValue("Number").substring(3) : 0))

const maxNumber = allNumbers.length > 0 ? allNumbers.reduce((a, b) => {

   return Math.max(a, b);

}) : 0

const newNumber = year + "-" + String(maxNumber + 1).padStart(3, "0")

Hi Kamille. I’m sorry I misread your last post. I really appreciate your help with this. I did attempt this as you suggested but I’m getting a 21-NaN as the result instead of 21-847 as expected. This is what I have in the Code field of the script for the automation:

 // Retrieve the year the new record was created

  const { year } = input.config()



  // Load all the records which were created the same year

  const table = base.getTable("RFQ")

  const query = await table.selectRecordsAsync()

  const records = query.records.filter(record => record.getCellValue("Year Created") === year)



  // Construct a new "autonumber" in the format: YY-###

  const allNumbers = records.map(x => Number(x.getCellValue("Number") ? x.getCellValue("Number").substring(3) : 0))

  const maxNumber = allNumbers.length > 0 ? allNumbers.reduce((a, b) => {

   return Math.max(a, b);

  }) : 0

  const newNumber = year + "-" + String(maxNumber + 1).padStart(3, "0")



  // Output the autonumber for use in the next steps

  output.set("newNumber", newNumber)

Could that be true if there are non-numeric numbers in the “Numbers” field?

Yes. The first post said the syntax was YY-###, but apparently that is not the case. The solution to fix the script is going to be much more complicated considering how inconsistent the pattern can be.

The easiest alternative I can think of is to make a Formula-type field called {Suffix} that does something like this to get the three numbers after the first hyphen:

IF(FIND('-', {Number}), MID({Number}, FIND('-', {Number}) + 1, 3)

Then adjusting the line below as follows:

const allNumbers = records.map(x => Number(!x.getCellValue("Suffix") || isNaN(x.getCellValue("Suffix")) ? 0 : x.getCellValue("Suffix")))

IF(FIND('-', {Number}), MID({Number}, FIND('-', {Number}) + 1, 3)

const allNumbers = records.map(x => Number(!x.getCellValue("Suffix") || isNaN(x.getCellValue("Suffix")) ? 0 : x.getCellValue("Suffix")))

You are marvelous. This works perfectly! Thank you so much for your effort on this!