Commission automation

[Saml8015]

Have a deal with a company to lower their costs on segments and get a cut per segment. The commission is based on costs cut which vary by segment. As I’m working through them one by one, the commission start dates are different per account.

How can I set up a system that will take a commission start date and make a new record for every month from start date up until current month.

Would like for this to work retroactively, meaning if the date was April 2024 it should automatically create records from April up until today, and a new record per segment for every future month automatically. 

[Dimitris_Goudis]

Hey @Saml8015, to do this you need a script that will be executed when an automation is triggered. 
Please check the following script, you can use it in your base, I tested it and it worked.

// Get all tables 
let commissionsTable = base.getTable('Commissions'); //add your table name
let monthlyRecordsTable = base.getTable('Monthly records');

// Get the triggered record
let inputConfig = input.config();
let triggeredRecordId = inputConfig['recordId']; // ID of the triggered record

// Fetch the triggered record to access its fields
let triggeredRecord = await commissionsTable.selectRecordAsync(triggeredRecordId);
if (!triggeredRecord) {
    console.log('Triggered record not found.');
    return;
}

// Get the {Start date} from the triggered record
let startDate = triggeredRecord.getCellValue('Start date');
if (!startDate) {
    console.log('Start date is missing.');
    return;
}

// Convert start date to a Date object and get the current date
startDate = new Date(startDate);
let currentDate = new Date();

// Ensure the time components are zeroed out for consistent comparison
startDate.setHours(0, 0, 0, 0);
currentDate.setHours(0, 0, 0, 0);

// Loop through each month from the start date to the current date
let recordsToCreate = [];
let tempDate = new Date(startDate);

while (tempDate <= currentDate) {
    if (tempDate > startDate) {
        recordsToCreate.push({
            fields: {
                'Date': tempDate.toISOString().split('T')[0], // Format as YYYY-MM-DD
                'Commission': [{ id: triggeredRecordId }] // Link the triggered record
            }
        });
    }

    // Move to the next month
    tempDate.setMonth(tempDate.getMonth() + 1);
}

// Create the records in batches of 50
while (recordsToCreate.length > 0) {
    await monthlyRecordsTable.createRecordsAsync(recordsToCreate.slice(0, 50));
    recordsToCreate = recordsToCreate.slice(50);
}

console.log(`Successfully created ${recordsToCreate.length} monthly records.`);