Generate Job Number via Script

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?

It’s definitely possible. There are several ways this could be set up, but my inclination is to use a separate scripting app (formerly block) for each one: one would create a new bid record and assign its number, the other would do the same for a new job record. That keeps you from needing a button field in the table just to build the number.

The trickiest part of the script would be formatting that running counter portion. What happens when a number is five digits? Or six digits? How does that change the format? Is the decimal always going to fall where there would normally be a comma; e.g. 10,542 --> 10.542, meaning that the center section is going to grow while the outer sections don’t?

Hi @Justin_Barrett! Thank you for getting back to me!

I’m fine with the counter starting over after 9999 for both jobs and bids, so that the running counter always stays 4 digits.

Sounds good. And just to make sure I understand the setup, both jobs and bids are in the same table, correct? I’m guessing you have a single select field to tag records to the appropriate type.

Also, are you saying that you currently have 6933 job records in that table? Or is 6933 just the highest available number? That will impact the technique used to find the next number in the sequence.

Yes, assume that jobs and bids are in the same table, with a single select field to classify them. Right now, jobs and bids are not in the same table (because I didn’t know what I was doing when I created this base), but I plan to merge them in the next few days with the single select field.

Those numbers were just made up. In reality, as of this evening, the jobs counter is at 3230 (created earlier today) and the bid counter is at 2059 (created earlier today). The combined bids and jobs table will have about 2500 records in it (about 1500 bids and about 1000 jobs).

Thank you so much!

Heh…don’t thank me just yet. I won’t be able to dive into this for a few days, but I’ll let you know when I’ve got something.

I greatly appreciate it!

I’m finally able to revisit this, and I’ve got more questions (sorry)…

  1. What is the name of the field containing the job/bid number?
  2. Is this job/bid number the only way that records are identified as either a job or bid? Or is there maybe a single-select field with “Job” and “Bid” options somewhere else in the record?

Hi Justin,

No problem about questions - ask as many as you like!

The field containing the job/field number is called {Objective Number}.

There will be a single select field called {Objective Type} that classifies the objective as a bid or job.

Thanks again!

1 Like

Sorry for the massive delay. Here’s a script that will do what you want. Descriptive stuff below:

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

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

// 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 dateCreated = new Date(record.getCellValue("Date Created"))
let month = padLeadingZeroes(dateCreated.getMonth() + 1, 2)
let year = padLeadingZeroes(dateCreated.getFullYear(), 4).slice(2)
let nextNumber = (objectiveNumber === "5" ? jobCount : bidCount) % 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})

To make this work, you’ll need to add a button that launches this script in a Scripting app. Click that button once you’ve added a date and set the objective type for a new record, and seconds later you’ll have the new ID in the primary field, and the matching number in the {Objective Number} field.

You’ll also need two views in your main table: one named “Jobs” that filters only job records, and another named “Bids” that only shows bids. The script uses those to figure out how many existing records there are of each type, and uses that to calculate the next number. And speaking of the number…

The script correctly starts at 1 for the first record of either type. However, after 9999, it will roll back to 0, not 1. That’s an unfortunate side-effect of the math used to pull this off. I’m hoping this won’t be a huge issue.

The only things you’ll need to tweak in this script are the table name at the top, and possibly the name of the {Objective Type} field value that marks a job, in this line roughly in the middle:

let objectiveNumber = objectiveType === "Job" ? "5" : "4"

If your label is something other than “Job”, change “Job” to your thing and all will be well with the world. :slight_smile:

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!

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.

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?

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.

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

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})
1 Like

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.

// 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 ❌")
1 Like

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?

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.