How to create unique Job Numbers after another field's status is changed?

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
I have a table called Opportunities and on that table we have a field with the current status of the project. I'm trying to make a field that will create a unique Job Number (with a text prefix) after the status has been changed to "Won". I originally made it pull the [Record ID#] so we would just be using that (ex. IPV-234, IPV-256, IPV-261) but we would like the Job Numbers to be consecutive (basically ignoring the opportunities we did not win but still having a record of them if there is a change in the future). Is there anyway to create a field that would do this and go back through the old Opportunities and give them sequential Job Numbers (ex. IPV-0001, IPV-0002, etc.)?
Photo of Stephen

Stephen

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
I assume your original formula for [Job Number] was something like this:

"IPV-" & ToString([Record ID#])

Modify the formula to be something like this:

"IPV-" & ToString([Record ID#] - ToNumber([IPV Offset]))

where [IPV Offset] is a User Defined Variable with a value of 233. User Defined Variables used in formulas in this fashion act as if they were a field in the current table.
Photo of Stephen

Stephen

  • 0 Points
That would only work for the first group of opportunities that are skipped. I need something that would be able to continually assign consecutive numbers. Adding more detail to my problem.
Photo of Stephen

Stephen

  • 0 Points
For example we have the following [Record ID#]: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 ,13 ,14, 15
The opportunities that we ended up winning are: 1, 4, 5, 9, 11,15
Now after changing the Status to "W - Won" on 1, 4, 5, 9, 11,15 - I need to assign the respective job numbers to them IPV- 1, 2, 3, 4, 5, 6
The trick is that we may come back to some of the opportunities like number 2 for example and then that would need to be the latest Job Number, in this case IPV-7.

I have a feeling I'll have to create another table in order to do this but I'm a bit stumped on how exactly to go about it.

For reference this is what I used to just pull the record ID# as the Job Number:
If([Status]="W - Won", "IPV-" & [Record ID#], null)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Okay in that case you would have to use the image onload technique to perform a simple AJAX request on all the relevant records and gather the [Record ID#]s for all records where the [status] ="won" (or checked - however you  modeled the status condition). The script would increment the maximum [RecordID#] found and prefix it with ""IPV-" and set this new string value to the input field for [Job Number]. When you save the form the new next available [Record ID#] would be used in coming up with the new [Job Number].

If you were to next tell me that some contracts you win are occasionally subsequently lost and you want to reuse the previously assigned [Job Number], the logic would be slightly different but would still involve scanning the list of [Record ID#]s and grabbing the next available one. It is a simple script but the details and parameters depend on your particulars.
Photo of Stephen

Stephen

  • 0 Points
Hmm thanks for the answer, not sure I know how to do any of that but maybe I'll try to teach myself or figure something else out.
Photo of Eric

Eric

  • 40 Points
Stephen, the closest idea to an answer for what you requested is outlined in this knowledge base article: http://quickbase.intuit.com/developer/knowledge-base/im-trying-set-numbering-system-track-quotes-i-w...