Hello,

I am having trouble getting a script to automatically generate records from two lists: bonus plan and staff list into “bonustable”. I was able to set up the automation steps to find the corresponding lists from the bonus plan and staff list. I have tried AI to get the script but it is not iterating each item from the input list. I also want to input the value year-month from the previous step of automation so that the created record already has year-month linked.

Essentially I am looking for the following output:

Jane / Bonus Plan A / 2025-03

Jane / Bonus Plan B / 2025-03

Jane / Bonus Plan C / 2025-03

Ben / Bonus Plan A / 2025-03

Ben / Bonus Plan B / 2025-03

Ben / Bonus Plan C / 2025-03

Script is as follows:

// Access input variables from previous automation steps

const inputConfig = input.config();

const bonusPlanNames = inputConfig.bonusPlanNames; // Array of Bonus Plan names (primary field)

const staffNames = inputConfig.staffNames; // Array of Staff names (primary field)

const yearMonth = inputConfig.yearMonth; // String in "YYYY-MM" format

 

// Get the tables

const bonusPlansTable = base.getTable("bonusplan");

const staffTable = base.getTable("stafflist");

const bonusRecordsTable = base.getTable("bonustable");

 

// Helper function to find a record by its primary field value

async function findRecordByPrimaryField(table, primaryFieldValue) {

const query = await table.selectRecordsAsync();

return query.records.find(record => record.getCellValue(table.primaryField) === primaryFieldValue);

}

 

// Loop through each combination of bonus plan and staff

for (let bonusPlanName of bonusPlanNames) {

// Find the Bonus Plan record by its primary field value

const bonusPlanRecord = await findRecordByPrimaryField(bonusPlansTable, bonusPlanName);

if (!bonusPlanRecord) {

console.error(`Bonus Plan record not found: ${bonusPlanName}`);

continue;

}

 

for (let staffName of staffNames) {

// Find the Staff record by its primary field value

const staffRecord = await findRecordByPrimaryField(staffTable, staffName);

if (!staffRecord) {

console.error(`Staff record not found: ${staffName}`);

continue;

}

 

// Create a new record in the Bonus Records table

await bonusRecordsTable.createRecordAsync({

"bonusplan": #{ id: bonusPlanRecord.id }], // Link to the Bonus Plan record

"stafflist": #{ id: staffRecord.id }], // Link to the Staff record

"Year-Month": yearMonth // Set the Year-Month field

});

 

console.log(`Created record for Bonus Plan: ${bonusPlanName}, Staff: ${staffName}, Year-Month: ${yearMonth}`);

}

}

 

console.log("All records created successfully!");

 

 

I have the input list set as following:

Hope to figure this out, thank you so much!!