Forum Discussion

BrandonBolino's avatar
BrandonBolino
Qrew Member
5 years ago

Automated Project Number

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
------------------------------
App builders
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    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
    ------------------------------
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    5 years ago
    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
    ------------------------------