Discussions

 View Only

Increment number for new record, start a zero each year

  • 1.  Increment number for new record, start a zero each year

    Posted 11-20-2022 11:14
    I have a project reference field that the stakeholders would like to use their current reference number for. 

    The reference number should not be generated when the record is created (so I can't use the record ID in the formula). 

    The format of the reference is:

    BU-001-23

    "BU" never changes.  "001" should be auto-incremented over the last number and start from 001 at the beginning of a new year.  "23" is the current year. 

    I would like to create a button called "Generate Project Reference" that, when clicked, will generate the reference and populate it in the Project Reference field. 

    Once a Project Reference is assigned a Project record should not be deleted, only given the status "cancelled" if it doesn't go forward past that the point it gets a reference number. 

    My understanding of formulas is still fairly basic. The main thing I'm stuck at is determining how to find the last number in the table to increment from.  It seems creating a separate table for just project reference numbers would be easiest, but it seems strange to make a table relationship where one project will only have one reference and vice versa. 

    I feel if I read-up on and fiddle around I'll be able to figure out the math/logic to increment based on year, but any tips would be appreciated.

    ------------------------------
    Paul Easton
    ------------------------------