Best format for Unique Number Generation

  • 39
  • 1
  • Question
  • Updated 2 months ago
  • Answered
  • (Edited)
I am in need of unique numbers for multiple different tables and am wondering what the best format is for the structure. 

So far I know that I will need to track a letter to correspond to the table. I will also need to track a year, a month, and a constant four digit section containing the record Id, padded with leading zeroes. 

My specific question is, for searchability, etc. Should I put the letter and/or date at the end or the beginning. 

Example:      Q18290001  vs  00011829Q  ??

Any thoughts? Pros, cons? 

Thanks for any advice. 


PS, current code looks like this-->

"Q" & Right(ToText(Year([Quote Date])),2) & If(Month([Quote Date]) < 10, "0", "") & ToText(Month([Quote Date])) & Right("00000" & ToText([Record ID#]), 4)
Photo of David Brogdon

David Brogdon

  • 826 Points 500 badge 2x thumb

Posted 2 months ago

  • 39
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
I would put the [Record ID#] portion last and I would suggest for human readability having some dashes or at least a space to separate the elements.

Q-18-03-1234

I would also suggest to zero pad the month like i did above


Photo of David Brogdon

David Brogdon

  • 826 Points 500 badge 2x thumb
Thanks for your reply. Are there any performance, searchability, or other benefits to having the letter in front vs at the end, likewise are there specific benefits to using the record ID at the end vs beginning? I'm just trying to be thorough before I start tracking real data with one format or the other. It seems like the record ID at the front would make drop-down search typing more accurate?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
The new type ahead drop down record picker (which you probably have not experienced yet) is wonderful because it is essentially a live "contains" search regardless of the position of the numeric portion of the string.  You can request early access now by putting in a support ticket. 

But because that is imminent, you should have the record ID portion last for human convenience.

There is no performance difference for Quick Base.
Photo of David Brogdon

David Brogdon

  • 826 Points 500 badge 2x thumb
Okay, thanks for that info. How will the new record picker affect typing in grid edit mode? At the moment I am able to type a few numbers or letters and it brings up the related record I need and I can quickly go through a long list of records in grid edit mode like this. Will the new picker exhibit the same functionality? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Hmmm, good point.  i had never had a reason to try the Grid Edit   It still works the "old" way.  So if you do need to search numerically in grid edit yes, you may need to flip the sequence.

But still, keep in mind that when you are selecting records, you get to control what fields are showing in which sequence for "picking records".  So you may choose to have a different format being presented to the user in Grid Edit than when viewing on a form.