Help with sequence formula

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
Currently have the following formula as a field in my table:

If([Incoming Year]=2017, 17500, [Incoming Year]=2018, 18500, [Incoming Year]=2019, 19500)

If the Incoming Year is 2017 it will return a value of 17500, if the Incoming Year is 2018 it will return a vale of 18500, etc...

I want to have it so that when a new record is created and the Incoming Year is 2017 it returns a value of 17501, the next record created with an Incoming Year of 2017 is 17502.  If a record is created and the Incoming Year is 2018, I would want it to create 18501.

Not sure if I explained it properly.
Photo of John

John

  • 80 Points 75 badge 2x thumb

Posted 10 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
I have done these kind of numbering systems in the past and truthfully, they are very difficult to do with native Quick Base.

An alternate suggestion is not to number sequentially, but rather use the [Record ID# in a formula.

For example

Right(ToText([Incoming year]),2)
& "-"
& ToText(Record ID#])

that would give a format like 17-123

You could also change it to ensure it had at least 4 digits like

17-0123

I know that is not what you asked for, but I have found its a real pain to get those kind of sub-numbering sequencing working.  It can be done, with extra tables and look fields ands snapshots of summary totals, but it is not a 20 minute job to get working.
Photo of John

John

  • 80 Points 75 badge 2x thumb
How do I change it to make sure that it has 4 digits?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Right(ToText([Incoming year]),2)
& "-"
& Right("000" & ToText(Record ID#]),4)