Discussions

 View Only
  • 1.  How do I create a unique project number?

     
    Posted 01-20-2016 20:06

    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.




  • 2.  RE: How do I create a unique project number?

    Posted 01-20-2016 20:37
    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/


  • 3.  RE: How do I create a unique project number?

    Posted 01-20-2016 21:40
    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.


  • 4.  RE: How do I create a unique project number?

    Posted 01-20-2016 21:48
    >  They are always a bit finicky to get working.

    That is why you should use script!


  • 5.  RE: How do I create a unique project number?

    Posted 01-20-2016 21:57
    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.


  • 6.  RE: How do I create a unique project number?

    Posted 01-20-2016 22:07
    Both approaches can also have conflict resolution and cancelation problems in multimate uzer settings.


  • 7.  RE: How do I create a unique project number?

     
    Posted 01-21-2016 12:33
    Thank you for helping me with this issue.


  • 8.  RE: How do I create a unique project number?

     
    Posted 01-21-2016 12:33
    Thank you for helping me with this issue.


  • 9.  RE: How do I create a unique project number?

     
    Posted 01-21-2016 12:34
    Thank you for helping me with this issue. I appreciate having the choice of a script and non-script approach to this issue.


  • 10.  RE: How do I create a unique project number?

    Posted 01-21-2016 13:53
    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.