How do I create a unique project number?

Question

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.

_anomdiebolt_ · Answer

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/

quickbasecoachd · Answer

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_ · Answer

&gt;&nbsp; They are always a bit finicky to get working. That is why you should use script!

quickbasecoachd · Answer

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_ · Answer

Both approaches can also have conflict resolution and cancelation problems in multimate uzer settings.

Forum Discussion

How do I create a unique project number?

Related Content

Roundup whole number

Calculating number of days between two date fields

AI Smart Builder for Creating Apps and Streamlining Pipelines

Project Names/ #s

Add hyphens to phone number field