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 starting with year and 500 + a sequential number.  If it's Company B, the sequence should be year and 700 + a sequential number. Each year starts over at 00 with the first entry for that year.  The year is derived from data entry "2019" or "2020" in a field titled [Year Expected], not an automatic date. For example, the output should be:

Company A = 2019-500, 2019-501, 2019-502, etc.; or 2020-500, 2020-501, etc.
Company B = 2019-700, 2019-701, 2019-702, etc.; or 2020-700, 2020-701, 2020-702, etc.

I would appreciate someone's help.
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.
    • LauraThacker's avatar
      LauraThacker
      Qrew Captain
      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")