Help

Unexpected results for script looking for highest number in a field.

Topic Labels: Automations
473 2
cancel
Showing results for 
Search instead for 
Did you mean: 
MrEchoEchoEcho
4 - Data Explorer
4 - Data Explorer

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")}`;
  }
})();

 

 

2 Replies 2
MrEchoEchoEcho
4 - Data Explorer
4 - Data Explorer
// 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")}`;
  }
})();
Sho
11 - Venus
11 - Venus

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