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.
I am about to travel but if I get a chance I will make a working demo out of it when I get back.
(1) QuickBase has the datejs library loaded on the page so we can easily manipulate dates. See:
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
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.
Unique Project Number ~ Add New Record
Pastie Database UPDATED
(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.
(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.