Auto-Number New Records with Prefix

[wjb]

Hey all. I’ve searched a handful of times but can’t seem to find another example of what I’m trying to accomplish. I’m looking for some help with an automation or script that can automatically add the correct inquiry number. We do this manually and it’s slowing down our response time and ability to, well automate processes.

Our inquiry numbers syntax:

YY-NNN or an example: 21-846

The next inquiry would be 21-847. This being the two-digit current year as a prefix and then just an integer of the number of inquiries. As others have noted, the “Auto-Number” field is not sufficient.

The field name is Number in Table RFQ where we’d like this new number to go automatically upon new record creation.

Our inquiries mostly come from an airtable embedded form. Sometimes we manually enter them too.

[Kamille_Parks]

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.

[wjb]

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

[Kamille_Parks]

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")

[wjb]

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.

[wjb]

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?

[Kamille_Parks · Accepted solution]

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")))

[wjb]

Kamille_Parks:

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!