Apr 22, 2021 01:04 PM
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.
Solved! Go to Solution.
Apr 26, 2021 10:49 AM
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")))
Apr 23, 2021 10:36 AM
Create a Formula field with this formula: DATETIME_FORMAT(CREATED_TIME(), 'YY')
Create an Automation that triggers when a record matches conditions
with the condition being ^that field is not empty
AND {Number} is empty
Add a Run a script
action step with this setup:
Input variables
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)
Update record
action step that inserts the output from the above script into your {Number}
field.Apr 23, 2021 12:10 PM
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
Apr 23, 2021 12:24 PM
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")
Apr 23, 2021 12:35 PM
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.
Apr 26, 2021 10:28 AM
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?
Apr 26, 2021 10:49 AM
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")))
Apr 28, 2021 10:44 AM
You are marvelous. This works perfectly! Thank you so much for your effort on this!