Auto-Number New Records with Prefix

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?

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!