Forum Discussion

TrinaHemphill's avatar
TrinaHemphill
Qrew Member
6 years ago

Sequential Numbering System based on multiple factors

I have a complicated formula for which I am getting an error.  Essentially, I need a sequential numbering system based on multiple factors.  For example, Company A should have a sequential number star...
Formulas and functions
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
6 years ago
My suggestion is to not have that numbering convention.

I know that seems like disrespectful comment, but 9 times out of 10 when pushed, my clients who initially ask for such a numbering system cannot defend why the number needs to be sequential within a year.

The usual answer is that we have always done it that way or my manager asked for it to be this way (because we have always done it this way), but never a valid reason.

My suggestion is to construct a formula field which includes the year, and a Company identifier, and the Record ID#. If you like you can either zero pad the Record ID or else run up the record ID# to say 4 or 5 digits by importing and deleting a bunch of records) to make an sequence number like

2019-A-00001

Or else

2019-A-1000

But the last part will be the Record ID and will not start over at zero each new year.

Can what you what be done, yes it can but it�s a whole bunch of setup with summary fields and snapshot fields, so I feel obligated to push back and ask why not keep it KISS simple and just use the Record ID#
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Funny you wrote this as we had the same conversation in our office as we are building the functionality,  Currently we use a numbering system of year (two digit) plus an incremental number that resets each year.  So this year started with 19001, 19002, 19003, etc.  At the time it made sense as it gives us a rough timeframe of how old a project is.

    As I am developing Quick Base I just used the built in auto number system and a bunch of people have said thats wrong it needs to be in the 19001 format.  I asked why....  I got the because that is how we do it answer.  I asked why.  They said because it gives us a rough estimate of when a project started.  I said but Quick Base has that info with a date started field.  They said they need it.  I said why, havent gotten another answer yet so I know no one can thing of one lol

    I too would prefer that format out of habit, but I am using the time to eliminate it and just reset it.

    I would be interested though in having a certain number of leading zero's for formatting.  I might look into that.
  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Commander
    6 years ago
    I would be interested though in having a certain number of leading zero's for formatting.  I might look into that."

    https://help.quickbase.com/release-notes/february-2019-release-notes.html

    We�ve made a number of enhancements to both our formula engine and our formula editor:

    • We added the following new formula functions:
      • PadLeft(stringToPad, desiredLength, padString), for example: PadLeft([Zip Code],5,"0")

      • PadRight(stringToPad, desiredLength, padString), for example: PadRight([Barcode],10,"0")

    Your pad-formula needs to be formula-text field; and your source needs to be either text

    PadLeft([Text Field],5,"0")

    or if your source is numeric

    PadLeft(ToText([Numeric Field]),5,"0")