Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Script to autonumber secondary key by group

Topic Labels: Automations Data
123 0
cancel
Showing results for 
Search instead for 
Did you mean: 
JeffJohnVol
7 - App Architect
7 - App Architect

I've gotten a lot of help from the community, and I'd like to offer something in return.  I was able to author a script that autonumbers a counter field for my detail records based on the group field of the parent record.  This is my first working script and I'm sure it has issues, so use carefully.  I'm sure better versions of this exist on marketplace or someplace, but I couldn't find.  I've submitted it to the marketplace, not sure if it's worthy of them listing it though.  It was a painful learning process to get working, but it does do what I need now. 🙂

PS, I know airtable doesn't have a concept of "secondary key" (as in subject), but I think people like me that come from a SQL background might search on that

The high level of this use case is I have a board meeting each month (Meeting is primary key of parent record). People can submit new agenda items for that meeting, and as they do, the "Agenda Item" counter will number them 1,2,3, etc based on the max number for that counter plus one.  If a line gets deleted (e.g. it's 1,2,4,5 when 3 gets deleted) then it leaves a hole, it doesn't make the next one "3", which is fine for my needs.

If you have suggestions on what to improve on, I'm happy to hear. 🙂

 

 

// Specify the table and view where you want to apply the autonumbering

let table = base.getTable("Board Meeting Agenda Items");
let inputConfig = input.config();
let recordId = inputConfig['Rec_ID'];


let groupField = "Meeting"; // Replace with your group field name
let autoNumberField = "Agenda Item"; // Replace with your autonumber field name
let currentGroupValue = inputConfig.Meeting_ID;
console.log(`The value of Meeting_ID is ${inputConfig.Meeting_ID}`);
console.log(`currentGroupValue is ` + currentGroupValue);
// Load the records from the specified view
let query = await table.selectRecordsAsync({fields: table.fields});

// Initialize variables to track the current group and the count within that group
let currentCount = 0;
let autoNumberValue = 0;
let maxautoNumberValue = 0;
// Iterate over each record in the view
for (let record of query.records) {
    // Get the value of the group field
    let groupValue = record.getCellValueAsString(groupField);
    console.log(`groupValue is ` + groupValue);

    if (groupValue == currentGroupValue ) {
        let autoNumberValue=parseInt(record.getCellValueAsString(autoNumberField));
    console.log("autoNumberValue", autoNumberValue);
    console.log("maxautoNumberValue", maxautoNumberValue);

          if(autoNumberValue>maxautoNumberValue) {
  console.log("Bam");
             maxautoNumberValue = autoNumberValue;
    console.log("maxautoNumberValue", maxautoNumberValue);
          }
    }
    
     //  await table.updateRecordAsync(recordId, {
     //           [autoNumberField]: currentCount.toString()
   // });
}
let autoNumberVal = maxautoNumberValue+1;
       await table.updateRecordAsync(recordId, {
                [autoNumberField]: autoNumberVal.toString()  });​



 

Jeff Johnson
President, ChattLab Makerspace
0 Replies 0