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!