Generate Job Number via Script

[egordin]

Hello! We use a simple bid and job numbering system to create new bid and job numbers, and we have a table in a base where we input a new record for each new bid and job.

Is it possible to have the scripting app do this for us automatically (or after clicking a button or run)? The recipe for bids and jobs is as follows:

XYY.MM#.### where:

X is 5 for jobs, and 4 for bids
YY is the 2 digit year such as 20 for 2020
MM is the 2 digit month, such as 09 for September
and #.### is a running counter of the respective bids or jobs that we have, such as 3232 for bids or 6933 for jobs.

Here are two examples: 520.096.933 is a job that was created in September 2020 and is the 6,933rd job that we have ever created.

Is this possible to do with a script?

[egordin]

Thank you so much! This is great.

Is it possible to hard input a starting number for bids and jobs that is not zero, as my counts right now are as follows:
latest bid: 2098
latest job: 3250

Thanks again!

[Justin_Barrett]

That’s what those views are for that I mentioned above (“Jobs” and “Bids”). The script uses those to count the current number of each record type. If you’ve got 2098 bid records, and the “Bids” view only contains those, then the script will use 2099 as the next bid number.

[egordin]

Ah got it! Unfortunately I don’t have a comprehensive list of all of the bids and jobs in Airtable, since those date back to 1992. Is it possible to hard code the starting point?

[Justin_Barrett]

Perhaps, but it would be easier (and probably safer) to modify the code to find the largest available number from the {Objective Number} field, and increment that for the next record. I still recommend having separate views to make the highest-number calculation easier. I’ll modify the code and share the new version later.

[egordin]

That would be a much more reliable approach! Great idea thank you!

[Justin_Barrett]

Here’s the updated script. The stuff to customize in this is the same as the other one, but this one will read the highest available number across all records of the same type (job/bid) and assign a number one higher to the chosen record. It will also ignore the current record in that highest-number calculation, which will prevent it from incrementing an already-assigned number if you click the button again.

// Functions
function padLeadingZeroes(num, count) {
    let numString = num.toString()
    return "0".repeat(count - numString.length) + numString
}

function highestNumber (acc, curr) {
    if (curr.getCellValue("Objective Number") > acc.getCellValue("Objective Number"))
        return curr
    return acc
}

// Setup
let table = base.getTable("Jobs & Bids")
let bidView = table.getView("Bids")
let bidQuery = await bidView.selectRecordsAsync()
let jobView = table.getView("Jobs")
let jobQuery = await jobView.selectRecordsAsync()

// Get the record and determine if we're making a job or bid ID
let record = await input.recordAsync("Select a record", table)
let objectiveType = record.getCellValueAsString("Objective Type")
let objectiveNumber = objectiveType === "Job" ? "5" : "4"
let query = objectiveNumber === "5" ? jobQuery : bidQuery
let records = query.records.filter(rec => {return rec.id != record.id})
let highestRecord = records.reduce(highestNumber, records[0])
let dateCreated = new Date(record.getCellValue("Date Created"))
let month = padLeadingZeroes(dateCreated.getMonth() + 1, 2)
let year = padLeadingZeroes(dateCreated.getFullYear(), 4).slice(2)
let nextNumber = (highestRecord.getCellValue("Objective Number") + 1) % 10000
let count = padLeadingZeroes(nextNumber, 4)

let objectiveID = `${objectiveNumber}${year}.${month}${count[0]}.${count.slice(1)}`

// 
await table.updateRecordAsync(record, {"Name": objectiveID, "Objective Number": nextNumber})

[egordin]

This is great!

Since there’s no way to hide a button based on a condition, is it possible to have this script not run or do nothing if the “Name” field is already populated? This would keep my staff from generating new numbers of existing objectives.

[Justin_Barrett · Accepted solution]

// Functions
function padLeadingZeroes(num, count) {
    let numString = num.toString()
    return "0".repeat(count - numString.length) + numString
}

function highestNumber (acc, curr) {
    if (curr.getCellValue("Objective Number") > acc.getCellValue("Objective Number"))
        return curr
    return acc
}

// Setup
let table = base.getTable("Jobs & Bids")
let bidView = table.getView("Bids")
let bidQuery = await bidView.selectRecordsAsync()
let jobView = table.getView("Jobs")
let jobQuery = await jobView.selectRecordsAsync()

// Get the record and determine if we're making a job or bid ID
let record = await input.recordAsync("Select a record", table)
if (record.name.indexOf(".") == -1) {
    let objectiveType = record.getCellValueAsString("Objective Type")
    let objectiveNumber = objectiveType === "Job" ? "5" : "4"
    let query = objectiveNumber === "5" ? jobQuery : bidQuery
    let records = query.records.filter(rec => {return rec.id != record.id})
    let highestRecord = records.reduce(highestNumber, records[0])
    let dateCreated = new Date(record.getCellValue("Date Created"))
    let month = padLeadingZeroes(dateCreated.getMonth() + 1, 2)
    let year = padLeadingZeroes(dateCreated.getFullYear(), 4).slice(2)
    let nextNumber = (highestRecord.getCellValue("Objective Number") + 1) % 10000
    let count = padLeadingZeroes(nextNumber, 4)

    let objectiveID = `${objectiveNumber}${year}.${month}${count[0]}.${count.slice(1)}`

    // 
    await table.updateRecordAsync(record, {"Name": objectiveID, "Objective Number": nextNumber})
} else
    output.text("❌ Record already has an ID ❌")

[egordin]

Thank you so much @Justin_Barrett! Amazing.

This may be a question about the scripting block more than this specific script, but is there a way to run the script via the button without opening the apps pane?

[Justin_Barrett]

Not currently. It would also be nice if buttons could trigger automations, but that’s also not currently an option. What I sometimes do as a workaround is use a single-select field to drive an automation, with the automation also clearing the field in addition to doing its thing.