# Help with sequence formula

• 0
• Question
• Updated 1 year ago
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.
• 110 Points

Posted 1 year ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 65,796 Points
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.
• 110 Points
How do I change it to make sure that it has 4 digits?

QuickBaseCoach App Dev./Training, Champion

• 65,796 Points
Right(ToText([Incoming year]),2)
& "-"
& Right("000" & ToText(Record ID#]),4)