Can I create a sequential number based on the year of [Date Created] that resets to 0001 with each new year?

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Can I create a unique sequential number that starts at 0001 and resets back to 0001 with the start of each new year that also uses the last two digits of the year as the first two digits of the sequential number? Example 170001 would be the for the first record created in 2017, 180001 would be the first record created in 2018. I had a formula that didn't reset and was simply based on the record ID, but have now been asked to make it reset and do not know how. My plan was to base it on [Date Created].


Any help would be greatly appreciated. 
Photo of Greg

Greg

  • 0 Points

Posted 2 years ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
Try this



Create a table of years and load it up with say 50 years using Excel. Set the Key field to be the Year.

Make a field on your details table (Let's call it "Projects") with the formula of Year(ToDate([date created]))

Make relationship to the Years table.  1 Year has many Projects.

Make a summary field on the Years table of the # of Projects records.

Look that up back down to Projects and call it [# of Projects this year (lookup)]

Make a new field called [Project #] as a numeric field and set it to be a snapshot of the [# of Projects this year (lookup)] field.

Lastly set Role permissions so no one can ever delete a Project Record.
Photo of Greg

Greg

  • 0 Points
Thanks for your help. Where am I referencing the field I created with the formula of Year(ToDate([date created])) in this? I am getting that field to work, but the summary fields are coming out empty even with the snapshot field set to Initialize field for existing records.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
When you build the relationship where 1 year has many Projects, this field you created will hold the year, like 2017.  Use that on the right hand side of the relationship so that the calculated Year matches up with your table of Years.
Photo of Greg

Greg

  • 0 Points
I get a: Error
We are sorry -- at this time, you cannot use this formula and have a summary field using this relationship.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
OK, i will come up with another way to get a date.  We will need to make a form rule to set the date when the record is saved, to today, gotta run now......
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,348 Points 50k badge 2x thumb
OK, try this.  Make a field called [Date First Saved].  Make a form rule that says
when the record is saved] and the [Date First Saved] is blank
Change the value of the [Date First Saved] to the current date.

Then you that field in your relationship.