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

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

Wade Davis

160 Points

Posted 2 years ago

2 years ago

Chris

4,430 Points

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.

2017-11-01T16:27:53+00:00

Chris

4,430 Points

The last term would not need the parenthesis wrapped around it.

2017-11-01T16:34:23+00:00

Wade Davis

160 Points

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.

2017-11-01T18:40:56+00:00

Chuck Grigsby

2,100 Points

replace date d with the name of your date field Year([date field])

2 years ago

Chuck Grigsby

2,100 Points

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

2017-11-01T16:52:14+00:00

Wade Davis

160 Points

Also Received Error:

"Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets."

2017-11-01T17:28:47+00:00

QuickBaseCoach App Dev./Training, Champion

69,118 Points

Please post your formula and we will correct it.

2017-11-01T18:39:33+00:00

Chuck Grigsby

2,100 Points

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)

2 years ago

QuickBaseCoach App Dev./Training, Champion

69,572 Points

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)

2 years ago

QuickBaseCoach App Dev./Training, Champion

69,572 Points

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]

2017-11-01T16:37:56+00:00

Wade Davis

160 Points

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)

2017-11-01T18:51:32+00:00

QuickBaseCoach App Dev./Training, Champion

69,572 Points

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

2017-11-01T18:54:24+00:00

Chuck Grigsby

2,100 Points

I wonder if he doesn't have a date field and it should be Year(Today())

2017-11-01T18:58:10+00:00

QuickBaseCoach App Dev./Training, Champion

69,572 Points

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)

2 years ago

Wade Davis

160 Points

Finally got it.

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

Thank you everyone....

1 person likes this

2 years ago

Kyle Chase

90 Points

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

2 years ago

QuickBaseCoach App Dev./Training, Champion

69,572 Points

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.

2 years ago

Kyle Chase

90 Points

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.

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

Related Categories

App builders

Errors