Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Find the Earliest Date Across Multiple Date Fields

1288 1
cancel
Showing results for 
Search instead for 
Did you mean: 

When a record has multiple date fields, sometimes you’d like to know which date is the earliest.

In the below example, each record is a social post that has different live dates for different channels. I’d like to know the earliest live date across all three channels. I could do this in a formula, but the ‘LIVE DATE’ field is also used by other teams, so I’d rather calculate the date with the below script (plus the awesome batchAnd script to do this for more than 50 records):

Example Base Here

c9780c367ed148c2f89887fa46398c9512132acc.gif

 // 🌸🌸 👇Add all the date fields you need here 👇 🌸🌸
let fields = ['Instagram Date','IG Stories Date','Facebook Date','LIVE DATE']

// Let Airtable know which table and views records you want to use
let table = base.getTable("Content");
let view = table.getView('All');
let query = await view.selectRecordsAsync({fields:fields});
let records = query.records;

// Find the Earliest Date
let dates = records.map( c => fields.map( x => (c.getCellValue(x) != null && x != 'LIVE DATE') ? Date.parse(c.getCellValue(x)) : null).filter(x => x));
let minRaw = dates.map( c => c.reduce((acc,cur) => Math.min(acc,cur)));
let min = minRaw.map( c => new Date(c).toISOString())
let update = min.map( (c,i) => ({id:records[i].id,fields:{
    'LIVE DATE': c
}}));

/*
    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.

    ::PARAMETERS::
    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to
            perform

    ::RETURNS::
    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
*/
async function batchAnd(action, table, records) {
    let recordsActedOn;

    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;
        
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                recordsActedOn.push(...recordIds)
                records = records.slice(50);
            };
            break;

        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.deleteRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            }
            break;

        default:
            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    }
    return recordsActedOn;
}


// Update the records
await batchAnd('Update',table,update)
1 Reply 1

Hello @VictoriaPlummer,

I’m a part-time scripter coming from python to javascript mostly inside airtable script Block.
Scripting is helping me at my job that isn’t scripting but video color grading and teaching it (education).

I would like to thank you for your generosity in Scripts and Explanations because you take the time to publish them while you also have a job at Airtable!

oLπ
Brussels, Belgium, Europe.