# trying to create a job number generator

• 0
• Question
• Updated 1 year ago
• In Progress
OK so we're using Qbase for sales, so we track customers w/ "opportunities". Each customer can have multiple opportunities.

For the purposes of production, we use a job number that, for now, we manually assign. With multiple salespeople, and 1 person in charge of handing out the job numbers so that we don't duplicate, it creates a lot of work. Here's how our 8 digit number is set up now:

18050025

The 18 is "2018", the 05 is May, and the last 4 digits are a sequential number for total opportunities so far this year. Our business creates custom parts, so our production uses this unique number to track these parts over the years, in the situation that something has to be reproduced down the road.

I can't seem to figure out how to create a formula that would generate this number for me. What I've tried is creating a field in my opportunities table for "job number" that is a formula - numeric. However, every formula I've tried to create has failed with syntax errors, etc. There is very little info on the formatting for these formulas, and I'm not experienced enough with that kind of thing to fumble through it. Ultimately, I don't want to waste 2 hours on it and still not have it. Seems like it should be fairly simple to do. Can anyone out there help me with this?
• 954 Points

Posted 1 year ago

• 0
• 70,394 Points
Truthfully, I have been down this road before interms of numbering sequentially within a year.

imho and based on a lot of experience "the juice ain't worth the squeeze".

It's a pain to get that setup and hence I suggest that you simply make a formula field to take the last 2 digits of the year of the [Date Created] and then a dash and then use the Record ID#.  I will bet that you number jobs internally in sequence within a year just because you have always done it that way and it was a useful way at one time to have unique numbers.

List ("-", Right(ToText(Year(ToDate([Date Created]))),2), ToText([Record ID#]))
• 184 Points
I had a similar issue and resolved it by using the following formula:
[Related OEM2 - Quote Prefix]&Year(ToDate([Date Created]))&[Record ID#]

I used an OEM prefix (from a separate table), current year (which is 2018) and the record ID # to solve the issue.  I hope this helps.
(Edited)
• 248 Points
Summary field totaling all opportunities for the year of 2018.. And field 2 that gets the value from the summary field and doesn't allow it to change. Then use field 2 in the formula?
• 70,394 Points
Justin, and then if they delete an Opportunity, then they get duplicate numbers.
• 248 Points
In that case, I wouldn't allow Opportunities to be deleted.
• 70,394 Points
While I agree that it can be done with the summary field and snapshots and lookups, my experience with my clients is that they rarely have a valid reason to number jobs sequentially within a year.

The reason is usually just that is how they "always used to do it" before Quick Base.

So typically its not worth messing around for the couple hours it takes to set it up.

Also, and importantly, you can't fix the historical orders so you then need an override field.  It tends to get messy to get working.

So, yes it can be done if it is for some reason really important to the users and not just because that is the way they always used to do it in Excel and someone had the important status in the company as the giver of job numbers.
• 248 Points
I totally agree with you. I just wanted him to know it takes more than just a formula field.