Re: Generate Job Number via Script

egordin · ‎Sep 14, 2020

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?

Justin_Barrett · ‎Oct 13, 2020

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

See Solution in Thread

Justin_Barrett · ‎Sep 14, 2020

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?

egordin · ‎Sep 14, 2020

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.

Justin_Barrett · ‎Sep 14, 2020

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.

egordin · ‎Sep 14, 2020

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!

Justin_Barrett · ‎Sep 15, 2020

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.

egordin · ‎Sep 15, 2020

I greatly appreciate it!

Justin_Barrett · ‎Sep 18, 2020

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

What is the name of the field containing the job/bid number?
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?

egordin · ‎Sep 18, 2020

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!

Justin_Barrett · ‎Oct 10, 2020

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. :slightly_smiling_face: