Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 11, 2023 02:35 PM - edited Jul 11, 2023 03:35 PM
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:
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")}`;
}
})();
Jul 11, 2023 03:17 PM
// 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")}`;
}
})();
Jul 11, 2023 09:17 PM
My first post was a mistake.
The max values are sorted in reverse order, so you only have to read the field for the first record.
let highestNumber = queryResult.records[0].getCellValue(REF_NO);