Forum Discussion

KenKen's avatar
KenKen
Qrew Trainee
9 years ago

How do I create a unique project number?

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.


Forms
Formulas and functions
NotificationsEmail

9 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    >  They are always a bit finicky to get working.

    That is why you should use script!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    Both approaches can also have conflict resolution and cancelation problems in multimate uzer settings.
  • KenKen's avatar
    KenKen
    Qrew Trainee
    9 years ago
    Thank you for helping me with this issue. I appreciate having the choice of a script and non-script approach to this issue.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    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.