Number sequences of items

sequenceNumbering.js

/**
 * Title: Sequence Numbering
 * Version: 1.0
 * License: MIT
 * Author: Justin Barrett
 * Sites:
 * http://allaboutthatbase.tips
 * https://www.youtube.com/c/AllAboutThatBase1
 * 
 * GitHub gist URL for this script: https://gist.github.com/justinsbarrett/d388e562a60ae4f173d63e2e712399b7
 * 
 * Airtable community thread: https://community.airtable.com/t/automatic-sequential-numbering-of-non-sequential-items-read-jun-9-2021-follow-up/22281
 * 
 * Description: This script will build a sequential numbering scheme for a collection of non-sequentially ordered records.
 * The numbering can be based on the order that the records were created, or the order that they exist in a specific view.
 */

const settings = input.config({
    title: "Sequence Numbering",
    description: `This script will build a sequential numbering scheme for a collection of non-sequentially ordered records.
    The numbering can be based on the order that the records were created, or the order that they exist in a specific view.
    [Watch video demo](https://www.loom.com/share/3d4aee3a766a433c8d7b302b31d08628)`,
    items: [
        input.config.table("sequenceTable", {
            label: "Sequence table",
            description: "The table containing the records to put into sequences"
        }),
        input.config.view("itemView", {
            label: "Item view",
            description: "The view containing the items to sequence",
            parentTable: "sequenceTable"
        }),
        input.config.field("itemField", {
            label: "Item field",
            description: "The field containing the items to sequence",
            parentTable: "sequenceTable"
        }),
        input.config.field("sequenceField", {
            label: "Sequence ID field",
            description: "The field where the sequence ID will be saved. This MUST be a number field.",
            parentTable: "sequenceTable"
        }),
        input.config.select("modeSelect", {
            label: "Sequencing mode",
            description: "How should the items be sequenced?",
            options: [
                {label: "Sequence all records", value: "all"},
                {label: "Only sequence records without a sequence ID", value: "empty"}
            ]
        }),
        input.config.select("orderSelect", {
            label: "Item order",
            description: "How should the script determine item order?",
            options: [
                {label: "View order", value: "view"},
                {label: "Created time (you MUST have a created time field to use this option)", value: "time"}
            ]
        }),

    ]
})

const {sequenceTable, itemView, itemField, sequenceField, modeSelect, orderSelect} = settings
let configValid = true

// Check the field type of the sequence field
if (sequenceField.type !== "number") {
    output.markdown("## 🛑 The sequence ID field MUST be a number field.") 
    output.markdown(`The field that you selected is of type "${sequenceField.type}".`)
    configValid = false
}

// Find the created time field (if necessary)
let createdTimeField
if (orderSelect === "time") {
    for (let field of sequenceTable.fields) {
        if (field.type === "createdTime") {
            createdTimeField = field
        }
    }
    // If we haven't found a created time field, alert the user
    if (!createdTimeField) {
        output.markdown("## 🛑 Unable to find a created time field.")
        output.text(`Because you chose "Created time" for the "Item order" option, a field of this type is required to set item order.`)
        configValid = false
    }
}

/**
 * Update records in a table
 * 
 * @param {Table} table
 * @param {object[]} updates
 * @param {string} updates[].id
 * @param {object} updates[].fields
 * @param {boolean} showProgress
 */
async function updateRecords (table, updates, showProgress=false) {
    const total = updates.length
    let updateCount = 0
    while (updates.length > 0) {
        if (showProgress) {
            updateCount += Math.min(50, updates.length)
            output.clear()
            output.text(`Updating records (${updateCount} of ${total})`)
        }
        await table.updateRecordsAsync(updates.slice(0, 50));
        updates = updates.slice(50);
    }
}

async function getRecordsFromView() {
    const query = await itemView.selectRecordsAsync({
        fields: [
            itemField,
            sequenceField
        ]
    })
    return query.records
}

async function getRecordsByCreatedTime() {
    const query = await sequenceTable.selectRecordsAsync({
        fields: [
            itemField,
            sequenceField,
            createdTimeField
        ]
    })
    let records = [...query.records]
    records.sort((a, b) => {
        return (new Date(a.getCellValue(createdTimeField))).getTime() - (new Date(b.getCellValue(createdTimeField))).getTime()
    })
    return records
}

async function main() {
    // Collect all records
    let records = orderSelect === "view" ? await getRecordsFromView() : await getRecordsByCreatedTime()

    // Map unique items to sequence
    const sequenceMap = {}
    for (let record of records) {
        let item = record.getCellValueAsString(itemField)
        if (!(item in sequenceMap)) {
            sequenceMap[item] = {max: 0}
        }
    }

    // If we're only sequencing items without a sequence number, parse the currently-sequenced items and update the map
    let toSequence = [...records]
    if (modeSelect === "empty") {
        for (let record of toSequence) {
            let item = record.getCellValueAsString(itemField)
            if (item) {
                sequenceMap[item].max = Math.max(sequenceMap[item].max, record.getCellValue(sequenceField))
            }
        }
        toSequence = toSequence.filter(record => !record.getCellValueAsString(sequenceField))
    }

    // Parse the records and build a list of updates
    const updates = []
    for (let record of toSequence) {
        let item = record.getCellValueAsString(itemField)
        if (item) {
            // Increase the max for this item and add an update record
            sequenceMap[item].max++
            updates.push({
                id: record.id,
                fields: {
                    [sequenceField.name]: sequenceMap[item].max
                }
            })
        }
    }

    // Update records
    await updateRecords(sequenceTable, updates)
}

if (configValid)
    await main()