I'm attempting to generate a "Unique" identifier for a tracking number that is auto generating.

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I'm attempting to have a number generated using "TORC" as the beginning identifier
then the year. and then starting at 10000 and using the record ID#.

I'm new to "formulas and I am stumped
This is what I initially came up with but the error tells me that "There are extra characters beyond the end of the formula." 

"TORC"Year(Date d)"-"([Record ID#]+10000)

This is what I would like to see:
TORC17-100001
Photo of Wade Davis

Wade Davis

  • 160 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

Your new field would have to be a formula text field by data type.

"TORC" & Year(Date d) & "-" & ([Record ID#]+10000)

see if that cleans it up a bit.

Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
The last term would not need the parenthesis wrapped around it.
Photo of Wade Davis

Wade Davis

  • 160 Points 100 badge 2x thumb
With or without the parentheses
 
"TORC" & Year(  Date d) & "-" & [Record ID#]+10000

Returned Error
Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
replace date d with the name of your date field Year([date field])
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
Try this.
"TORC" & Year(Date d) & "-" & ([Record ID#]+10000)
Photo of Wade Davis

Wade Davis

  • 160 Points 100 badge 2x thumb
Also Received Error:

"Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets."
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,200 Points 50k badge 2x thumb
Please post your formula and we will correct it.
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
I feel like it should be this if its a formula text field and a date field as proper data type.
"TORC" & Right(ToText(Year([date_field])),2) & "-" & [Record ID#]+10000
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Not tested but try this.  Let me know if there are syntax errors.

Try this

"TORC"
& Right(ToText(Year([Date d])),2)
& "-"
& ToText([Record ID#]+10000)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I see everyone responded :)

I think my formula has the best chance for first round syntax errors being clean. 

The field for date d will need be used as [date d]
Photo of Wade Davis

Wade Davis

  • 160 Points 100 badge 2x thumb
This returned the error:
Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.


"TORC" 
& Right(ToText(Year(date d)),2) 
& "-" 
& ToText([Record ID#]+10000)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
"TORC" 
& Right(ToText(Year([date d])),2) 
& "-" 
& ToText([Record ID#]+10000)
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
I wonder if he doesn't have a date field and it should be Year(Today())
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
But then the unique identifier would keep changing when the year rolls over.

Maybe he wants
"TORC" 
& Right(ToText(Year(ToDate([Date Created]))),2) 
& "-" 
& ToText([Record ID#]+10000)
Photo of Wade Davis

Wade Davis

  • 160 Points 100 badge 2x thumb
Finally got it.

"TORC" 
& Right(ToText(Year([Request Date])),2) 
& "-" 
& ToText([Record ID#]+10000)

Thank you everyone....
Photo of Kyle Chase

Kyle Chase

  • 90 Points 75 badge 2x thumb
I also (as a result of attempting to automate a manual input process) am attempting to do the same thing.  However, my question is about the structure rather than the formula.

Being new to QB, I'm having to decipher what you guys are saying about the fields.

In a new test table, I created a formula-text field and placed the above formula in it.  I created a 'Request Date' field to satisfy the arguement, and it populates the 'TORC-' but not a number.

Could someone please briefly explain what else I'm missing to test this?  The Noob wants to learn.

Thank you so much in advance!

-Kyle
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Are you viewing a saved record?  That full formula will not calculate until the record is saved and a Record ID# is assigned to that record.

Also, please post your formula.
Photo of Kyle Chase

Kyle Chase

  • 90 Points 75 badge 2x thumb
No need.  For some reason, the minute I read your post, I got it.  Duh.  It's not going to generate until save.

I'm so sorry I wasted your time, and thank you so much for your help.

-Kyle

This conversation is no longer open for comments or replies.