Create recurring orders

[Kasra]

Over the weekend I was helping a non-profit that’s organizing food deliveries from restaurants to hospitals as part of their COVID response efforts.

They have a table of Orders. An order specifies the restaurant providing the food, the hospital receiving the food, a start and end date, and the days of the week to repeat the order:

We wrote a script that goes through these recurring orders and creates a delivery record for each day that the order should repeat:

Here’s the script:

const ordersTable = base.getTable('Recurring orders');
const deliveriesTable = base.getTable('Deliveries');

const orders = await ordersTable.selectRecordsAsync();

const dayOfWeekToNumber = {
    Sun: 0,
    Mon: 1,
    Tue: 2,
    Wed: 3,
    Thu: 4,
    Fri: 5,
    Sat: 6,
};

for (const order of orders.records) {
    if (order.getCellValue('Deliveries')) {
        // Skip it, the deliveries were already created.
        continue;
    }

    const name = order.name;
    const startDateString = order.getCellValue('Start Date');
    const endDateString = order.getCellValue('End Date');
    const daysOfWeek = order.getCellValue('Days of week');

    if (!startDateString) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have a start date.`);
        continue;
    }
    if (!endDateString) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have an end date.`);
        continue;
    }
    if (!daysOfWeek) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have any 'Days of week' to repeat.`);
        continue;
    }

    const daysOfWeekSet = new Set();
    for (const dayOfWeek of daysOfWeek) {
        if (!dayOfWeekToNumber.hasOwnProperty(dayOfWeek.name)) {
            throw new Error(`Unexpected day of week: ${dayOfWeek.name}`);
        }
        daysOfWeekSet.add(dayOfWeekToNumber[dayOfWeek.name]);
    }

    const endDate = getDateFromString(endDateString);
    let deliveriesToCreate = [];
    for (
        let currentDate = getDateFromString(startDateString);
        currentDate <= endDate;
        currentDate.setDate(currentDate.getDate() + 1)
    ) {
        if (daysOfWeekSet.has(currentDate.getDay())) {
            deliveriesToCreate.push({
                fields: {
                    Date: getStringFromDate(currentDate),
                    Restaurant: order.getCellValue('Restaurant'),
                    Hospital: order.getCellValue('Hospital'),
                    "Recurring order": [{id: order.id}],
                },
            });
        }
    }

    output.text(`Creating ${deliveriesToCreate.length} deliveries for "${name}".`);

    // Only up to 50 records can be created at one time, so do it in batches.
    while (deliveriesToCreate.length > 0) {
        await deliveriesTable.createRecordsAsync(deliveriesToCreate.slice(0, 50));
        deliveriesToCreate = deliveriesToCreate.slice(50);
    }
}

output.text('✅ Done!');

function getDateFromString(dateString) {
    // Assumes dateString is yyyy-mm-dd
    const parts = dateString.split('-').map(part => parseFloat(part));
    const date = new Date();
    date.setFullYear(parts[0]);
    date.setMonth(parts[1] - 1);
    date.setDate(parts[2]);
    date.setHours(0, 0, 0, 0);
    return date;
}

function getStringFromDate(date) {
    // Returns yyyy-mm-dd string.
    return [
        date.getFullYear(),
        (date.getMonth() + 1).toString().padStart(2, '0'),
        date.getDate().toString().padStart(2, '0'),
    ].join('-');
}

You can get a copy of the demo base here: https://airtable.com/shrf1wV9kvkxg3Gcs

[Kasra]

Updated script:

• Now if you change the “End date” of any recurring orders and run the script again, it will create new deliveries as needed. Previously it would skip recurring orders that had been processed. It uses a rollup field in the “Recurring orders” table to get the last scheduled delivery date.
• The recurring order “Start date” and the delivery “Date” now include time, so you can schedule orders at specific times of day.

const ordersTable = base.getTable('Recurring orders');
const deliveriesTable = base.getTable('Deliveries');

const orders = await ordersTable.selectRecordsAsync();

const dayOfWeekToNumber = {
    Sun: 0,
    Mon: 1,
    Tue: 2,
    Wed: 3,
    Thu: 4,
    Fri: 5,
    Sat: 6,
};

for (const order of orders.records) {
    const name = order.name;
    const startDateString = order.getCellValue('Start Date');
    const endDateString = order.getCellValue('End Date');
    const daysOfWeek = order.getCellValue('Days of week');
    const lastDeliveryDateString = order.getCellValue('Last Delivery Date');

    if (!startDateString) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have a start date.`);
        continue;
    }
    if (!endDateString) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have an end date.`);
        continue;
    }
    if (!daysOfWeek) {
        output.text(`⚠️ Skipping "${name}" because it doesn't have any 'Days of week' to repeat.`);
        continue;
    }

    const daysOfWeekSet = new Set();
    for (const dayOfWeek of daysOfWeek) {
        if (!dayOfWeekToNumber.hasOwnProperty(dayOfWeek.name)) {
            throw new Error(`Unexpected day of week: ${dayOfWeek.name}`);
        }
        daysOfWeekSet.add(dayOfWeekToNumber[dayOfWeek.name]);
    }

    const endDate = getDateFromString(endDateString);
    endDate.setDate(endDate.getDate() + 1); // First moment of the next day.

    // By default, deliveries will be creating starting from the start
    // date. But if deliveries have already been created, we want to start
    // at the day after the most recent delivery.
    const newDeliveriesStartDate = getDateTimeFromString(startDateString);
    if (lastDeliveryDateString) {
        const lastDeliveryDate = getDateFromString(lastDeliveryDateString);
        lastDeliveryDate.setDate(lastDeliveryDate.getDate() + 1);

        if (lastDeliveryDate >= endDate) {
            // The last generated delivery was after the end date, so skip processing
            output.text(`⚠️ No new deliveries to generate for ${name}. The last delivery date is past the end date.`);
            continue;
        }
        
        newDeliveriesStartDate.setFullYear(lastDeliveryDate.getFullYear());
        newDeliveriesStartDate.setMonth(lastDeliveryDate.getMonth());
        newDeliveriesStartDate.setDate(lastDeliveryDate.getDate());
    }

    let deliveriesToCreate = [];
    for (
        let currentDate = newDeliveriesStartDate;
        currentDate <= endDate;
        currentDate.setDate(currentDate.getDate() + 1)
    ) {
        if (daysOfWeekSet.has(currentDate.getDay())) {
            deliveriesToCreate.push({
                fields: {
                    Date: getStringFromDateTime(currentDate),
                    Restaurant: order.getCellValue('Restaurant'),
                    Hospital: order.getCellValue('Hospital'),
                    "Recurring order": [{id: order.id}],
                },
            });
        }
    }

    output.text(`Creating ${deliveriesToCreate.length} deliveries for "${name}".`);

    // Only up to 50 records can be created at one time, so do it in batches.
    while (deliveriesToCreate.length > 0) {
        await deliveriesTable.createRecordsAsync(deliveriesToCreate.slice(0, 50));
        deliveriesToCreate = deliveriesToCreate.slice(50);
    }
}

output.text('✅ Done!');

function getDateFromString(dateString) {
    // Assumes dateString is yyyy-mm-dd
    const parts = dateString.split('-').map(part => parseFloat(part));
    const date = new Date();
    date.setFullYear(parts[0]);
    date.setMonth(parts[1] - 1);
    date.setDate(parts[2]);
    date.setHours(0, 0, 0, 0);
    return date;
}

function getDateTimeFromString(dateTimeString) {
    // Assumes dateString is like 2020-04-10T09:00:00.000Z
    const [dateString, timeString] = dateTimeString.split('T');
    const dateParts = dateString.split('-').map(part => parseFloat(part));
    const timeParts = timeString.split(':').map(part => parseFloat(part));

    const date = new Date();
    date.setFullYear(dateParts[0]);
    date.setMonth(dateParts[1] - 1);
    date.setDate(dateParts[2]);
    date.setHours(timeParts[0], timeParts[1], 0, 0);
    return date;
}

function getStringFromDateTime(dateTime) {
    // Returns yyyy-mm-ddThh:mm:00.000Z string.
    return [
        dateTime.getFullYear(),
        (dateTime.getMonth() + 1).toString().padStart(2, '0'),
        dateTime.getDate().toString().padStart(2, '0'),
    ].join('-') + 'T' + [
        dateTime.getHours().toString().padStart(2, '0'),
        dateTime.getMinutes().toString().padStart(2, '0'),
        '00.000Z',
    ].join(':');
}

[Jonathan_McCarv]

Thanks for making these date time converter functions. I was so surprised that the only output option from airtable is a GMT string!

[valeria_tapia]

Hi Kasra! Do you think it would be possible to create a script that creates a copy of a record but changes some info of the record? For expample creates a copy. of a record but adds 4 weeks. the date column and changes the status from the status column?

[Kamille_Parks]

That general idea has been answered several times, most closely in the thread linked below.

Duplicate record - clear fields/move values in fields Base design

I posted a related question a couple of weeks ago, and was able to implement the Script Block (Add new (semi-duplicate) record when checkbox selected) - super awesome, and THANK YOU! Here is what i need to do next: Field Names Current Item Title Current Item Desc Curr Editor Item Title Direction Curr Editor Item Desc Direction Curr Editor Item Title Modified Curr Editor Item Desc Modified Curr Edits Complete (checkmark box) Curr Edits Implemented (checkmark box) Each item is updated m…

[valeria_tapia]

Thanks a lot Kamille, could it be possible to contact you for some doubts that i have? thanks!