Forum Discussion

JohnStreeper's avatar
JohnStreeper
Qrew Trainee
7 years ago

Help with sequence formula

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.
App builders
Formulas and functions

3 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
    • JohnStreeper's avatar
      JohnStreeper
      Qrew Trainee
      7 years ago
      How do I change it to make sure that it has 4 digits?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Right(ToText([Incoming year]),2)
      & "-"
      & Right("000" & ToText(Record ID#]),4)