Hey guys, super new to airtable and JS, still trying to figure out how i can embed my script in this first post of mine.
I wrote a script that finds the largest number in a field, increments it by one and does some other formatting stuff (add a 'Q-' +(last two digits of the current year)+(current quote #))) then puts it in that same field for the record that triggered the automation. It sorts all records in Ref Max No(formula field to extrapolate a number from our quotes. The formula is as follows:
IF({Ref No}, VALUE(RIGHT({Ref No}, 3)), 0) e.g. Q-23089 turns into 89
For some reason it keeps finding the largest number as 572 even though the largest number is 89. everything else in my script is working as expected besides finding the largest existing number.
Here Is my code
// Script configuration
const SALES_PIPELINE = "Sales Pipeline";
const REF_NO = "Ref No";
const MAX_REF_NO = "Max Ref No";
let inputConfig = input.config();
console.log(`The value of Record_ID is ${inputConfig.Record_ID}`);
(async () => {
// Get the table
let table = base.getTable(SALES_PIPELINE);
// Retrieve the records sorted by "Ref No" field in descending order
const queryResult = await table.selectRecordsAsync({
fields: [REF_NO, MAX_REF_NO],
sort: [{ field: REF_NO, direction: "desc" }]
});
// Extract the highest number from "Max Ref No" formula field
let highestNumber = 0;
for (let record of queryResult.records) {
const maxRefNo = record.getCellValueAsString(MAX_REF_NO);
const sequentialNumber = parseInt(maxRefNo);
if (!isNaN(sequentialNumber) && sequentialNumber > highestNumber) {
highestNumber = sequentialNumber;
}
}
// Log the highest number in "Max Ref No" field
console.log(`Highest number in Max Ref No: ${highestNumber}`);
// Update the "Ref No" field of the triggered record
await table.updateRecordAsync(inputConfig.Record_ID, {
[REF_NO]: await generateQuoteNumber()
});
const updatedRecord = await table.selectRecordAsync(inputConfig.Record_ID);
const assignedNumber = updatedRecord.getCellValueAsString(REF_NO);
output.set("assignedNumber", assignedNumber);
// Helper function to generate the quote number
async function generateQuoteNumber() {
const currentYear = new Date().getFullYear() % 100; // Get the last two digits of the current year
// Calculate the new sequential number
const newNumber = highestNumber + 1;
// Generate the new quote number
return `Q-${currentYear.toString().padStart(2, "0")}${String(newNumber).padStart(3, "0")}`;
}
})();