How do I create a unique project number?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I’m creating a project number tracking app which uses a unique project number. The project number format is YY-MM-SSS, where YY is the current year, MM is the current month, and SSS is a sequential project number. I’ve tried using a formula-text field type, to concatenate the Today () function and the Record ID# field to create the project number but I get an error message. I would like for the formula to build the unique project number with the current YY and MM components and roll over the sequential number back to 001 monthly. Can you help me? Thanks.


Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb

Posted 3 years ago

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

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You have to use script to do this. Here is the code (untested):

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=469


I am about to travel but if I get a chance I will make a working demo out of it when I get back.

Notes

(1) QuickBase has the datejs library loaded on the page so we can easily manipulate dates. See:

http://www.datejs.com/
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
This can also be done without script. I have done this for s few clients. They are always a bit finicky to get working.

The technique would be to create a table where the Key field is set to YY-MM and loaded up via Excel for say the next 10 years, so like

16-01
16-02
16-03

Then make a similar field in your project table by formula. You should be able to base it off the [Date Created] field.

Then make a relationship and do a Summary of the # of Projects.

The magic technique is then to lookup that Summary Total down to the Projects table and lastly to make a snapshot field of that lookup. That will be your frozen count for the YY-MM.

Then you create a text formula field to make the YY-MM-SSS strung that you want.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
>  They are always a bit finicky to get working.

That is why you should use script!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Issues arise if users are allowed to delete Projects. That can be solved with another loop to get the maximum of the SSS and then look that up and snapshot that.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Both approaches can also have conflict resolution and cancelation problems in multimate uzer settings.
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
Thank you for helping me with this issue.
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
Thank you for helping me with this issue.
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
Thank you for helping me with this issue. I appreciate having the choice of a script and non-script approach to this issue.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Here you go:

Unique Project Number ~ Add New Record
https://haversineconsulting.quickbase.com/db/bkjugeh48?a=nwr

Pastie Database UPDATED
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=469


Notes

(1) The datejs library is not automatically loaded on the ?a=nwr page so I manually reloaded it from the QuickBase CDN. However, since the URL to the QuickBase CDN has a version number (70087-8) in it this demo will eventually fail when QuickBase deletes the old version of the resourse.

https://quickbase.intuitcdn.net/res/70087-8/js/date.js


(2) I made the [Project Number] field readonly on the add record form so nobody could mess with it. If you needed to fix the [Project Number] you can edit it through grid edit.