trying to create a job number generator

  • 0
  • 1
  • Question
  • Updated 7 months 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?
Photo of moleman108

moleman108

  • 458 Points 250 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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#]))
Photo of Carol Short

Carol Short

  • 164 Points 100 badge 2x thumb
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)
Photo of Justin

Justin

  • 248 Points 100 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Justin, and then if they delete an Opportunity, then they get duplicate numbers.
Photo of Justin

Justin

  • 248 Points 100 badge 2x thumb
In that case, I wouldn't allow Opportunities to be deleted. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.  
Photo of Justin

Justin

  • 248 Points 100 badge 2x thumb
I totally agree with you. I just wanted him to know it takes more than just a formula field.