Automation to create records from a schedule

Hi ​@ChrisHollywood , hard to say exactly what is causing it without seeing the script, but duplicate records in this kind of setup usually come from one of two things. Either the script is not checking whether a ticket already exists before creating a new one, or the automation itself is being triggered more than once a day for some reason.

Could you share your current script? That would make it a lot easier to figure out what is going wrong.

Leo from UseEfficiently

Hm, for stuff like that I usually recommend either:

1. Using Record Templates: https://support.airtable.com/docs/using-record-templates-in-airtable
2. Creating a table called ‘Task Templates’ where each record represents a single task, and creating an automation that’ll help you create those by doing a ‘Find Record’ followed by a ‘Repeating Group’

I also do free half hour calls and would be happy to jump on a call to get you sorted if you’d like, and you can schedule one here!

---

I’ve set up a base here for you to check out using both of these so that you can see how one would set it up, and here’s how they look in action:

Using the Task Templates table:

Using Record Templates:

​@leok This is the script: 

const recurringTable = base.getTable('Recurring Opportunities'); const opportunitiesTable = base.getTable('Opportunities');

const recurringRecords = await recurringTable.selectRecordsAsync({ fields: [ 'Opportunity Name', 'Description', 'Client', 'Location', 'Facility Manager', 'Amount', 'Notes', 'Frequency', 'Start Date', 'Active', 'Generated Opportunities' ] });

const opportunityRecords = await opportunitiesTable.selectRecordsAsync({ fields: ['Due Date', 'Recurring Opportunity'] });

const today = new Date(); today.setHours(0, 0, 0, 0);

const twelveMonthsFromNow = new Date(today); twelveMonthsFromNow.setMonth(twelveMonthsFromNow.getMonth() + 12);

for (let i = 0; i < recurringRecords.records.length; i++) { let record = recurringRecords.records[i];

let isActive = record.getCellValue('Active');

let startDate = record.getCellValue('Start Date');

let frequency = record.getCellValue('Frequency');

if (!isActive || !startDate || !frequency) {

    continue;

}

let freqName = frequency.name;

let intervalDays = 30;

if (freqName === 'Weekly') {

    intervalDays = 7;

} else if (freqName === 'Monthly') {

    intervalDays = 30;

} else if (freqName === 'Quarterly') {

    intervalDays = 90;

} else if (freqName === 'Bi-Annually' || freqName === 'Semi- Annual') {

    intervalDays = 182;

} else if (freqName === 'Annually') {

    intervalDays = 365;

}

let existingDueDates = [];

let generatedOpps = record.getCellValue('Generated Opportunities');

if (generatedOpps && generatedOpps.length > 0) {

    for (let j = 0; j < generatedOpps.length; j++) {

        let oppRecord = opportunityRecords.getRecord(generatedOpps[j].id);

        if (oppRecord) {

            let dueDate = oppRecord.getCellValue('Due Date');

            if (dueDate) {

                let dateStr = new Date(dueDate).toISOString().split('T')[0];

                existingDueDates.push(dateStr);

            }

        }

    }

}

let nextDate = new Date(startDate);

while (nextDate <= twelveMonthsFromNow) {

    if (nextDate > today) {

        let nextDateStr = nextDate.toISOString().split('T')[0];

        if (existingDueDates.indexOf(nextDateStr) === -1) {

            let oppName = record.getCellValue('Opportunity Name');

            let client = record.getCellValue('Client');

            let location = record.getCellValue('Location');

            let facilityManager = record.getCellValue('Facility Manager');

            let amount = record.getCellValue('Amount');

            let description = record.getCellValue('Description');

            let notes = record.getCellValue('Notes');

            let newFields = {

                'Opportunity Name': oppName + ' - ' + nextDate.toLocaleDateString(),

                'Due Date': new Date(nextDate),

                'Type': {name: 'From Recurring'},

                'Status': {name: 'Approved Pending Work Order'},

                'Recurring Opportunity': [{id: record.id}]

            };

            if (description) {

                newFields['Description'] = description;

            }

            if (amount) {

                newFields['Amount'] = amount;

            }

            if (notes) {

                newFields['Notes'] = notes;

            }

            if (client && client.length > 0) {

                newFields['Client'] = [{id: client[0].id}];

            }

            if (location && location.length > 0) {

                newFields['Location'] = [{id: location[0].id}];

            }

            if (facilityManager && facilityManager.length > 0) {

                newFields['Facility Manager'] = [{id: facilityManager[0].id}];

            }

            await opportunitiesTable.createRecordAsync(newFields);

            existingDueDates.push(nextDateStr);

            console.log('Created: ' + oppName + ' - ' + nextDate.toLocaleDateString());

        }

    }

    nextDate.setDate(nextDate.getDate() + intervalDays);

}

}

console.log('Done');

Hi ​@ChrisHollywood, I had a look at your script and I think I found the issue.

Try swapping this line:

let oppRecord = opportunityRecords.getRecord(generatedOpps[j].id);

with this:

let oppRecord = opportunityRecords.records.find(r => r.id === generatedOpps[j].id);

This searches through the loaded records directly which should be more reliable. Give it a try and let us know if the duplicates stop.

Leo from UseEfficiently

Thanks Leo. That didn’t seem to do it, I booked a call with you on Tuesday to see if we can work through it.