Sequential Numbering System based on multiple factors

Question

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.

QuickBaseCoachD · Answer

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#

Sign up

Login to the community