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#
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")