Skip to main content
Question

Automated Project Number

  • June 23, 2020
  • 0 replies
  • 34 views
Hi there!

I use QuickBase to keep track of projects, (project title, project number, etc.).  I have a field called project number that takes the last two digits of the year ("YY") and the sequential number of projects that have taken place that year ("XXX").  So if I were to begin working on my third project of the year the project number would be "20003." 

I am running into problems making this automated, I want to format the field type so when a new project is started the project number is automatically generated.  I don't know what field type to choose, I have tried to do it with a numeric formula but am unsure of what code to type. 

Any help would be much appreciated! Thanks!

------------------------------
Brandon Bolino
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
There are different ways to do this.  Here is one.
Load up a table of numeric Years for the next 20 years.  so 2020 2021 etc. Make that the Key field.
Make a relationship to your projects table where the reference field on the right is a formula with the formula of

Year(ToDate([Date Created])

Make a numeric field on projects called [Yearly Sequence Number].

On he Relationship Make a summary of the Maximum of the [Yearly Sequence Number]. call it [Current Yearly Sequence Number]

Make a formula called [Next Yearly Sequence Number] as [Current Yearly Sequence Number] +1

Look that up down to Projects.

Make a numeric field called [Sequence Number Maintained by Automation].

Make an Automation that triggers when a project is added to Edit he Project and set the field [Sequence Number Maintained by Automation] to the value of [Next Yearly Sequence Number] of the Trigger record.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img+11
  • IvanWeiss
  • Registered
  • June 24, 2020
Just one piece of advice with this....  I used to do something similar.  We have project numbers for this year start at 20001, 20002, etc.

Thought that was important.  Someone on the forum said why, there really is no good reason.  My team wanted it etc.  I made it simple went with an auto incrementing number and it really was no big deal and has worked out great.  Any argument wasnt necessary because Quick Base tracks when you create the record so I always know what year the project originated.

So just think about if you do actually need it before doing it.

------------------------------
Ivan Weiss
------------------------------
Terms & ConditionsAccessibility statement