Discussions

Expand all | Collapse all

Help with sequence formula

  • 1.  Help with sequence formula

    Posted 01-19-2018 15:13
    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.


  • 2.  RE: Help with sequence formula

    Posted 01-19-2018 15:20
    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.


  • 3.  RE: Help with sequence formula

    Posted 01-19-2018 15:33
    How do I change it to make sure that it has 4 digits?


  • 4.  RE: Help with sequence formula

    Posted 01-19-2018 15:54
    Right(ToText([Incoming year]),2)
    & "-"
    & Right("000" & ToText(Record ID#]),4)