Discussions

Expand all | Collapse all

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

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

    Posted 11-01-2017 16:21
    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


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

    Posted 11-01-2017 16:25

    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.



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

    Posted 11-01-2017 16:27
    The last term would not need the parenthesis wrapped around it.


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

    Posted 11-01-2017 16:34
    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.


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

    Posted 11-01-2017 18:40
    replace date d with the name of your date field Year([date field])


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

    Posted 11-01-2017 16:26
    Try this.
    "TORC" & Year(Date d) & "-" & ([Record ID#]+10000)


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

    Posted 11-01-2017 16:52
    Also Received Error:

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


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

    Posted 11-01-2017 17:28
    Please post your formula and we will correct it.


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

    Posted 11-01-2017 18:39
    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


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

    Posted 11-01-2017 16:27
    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)


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

    Posted 11-01-2017 16:29
    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]


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

    Posted 11-01-2017 16:37
    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)


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

    Posted 11-01-2017 18:51
    TORC" 
    & Right(ToText(Year([date d])),2) 
    & "-" 
    & ToText([Record ID#]+10000)


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

    Posted 11-01-2017 18:54
    I wonder if he doesn't have a date field and it should be Year(Today())


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

    Posted 11-01-2017 18:58
    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)


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

    Posted 11-01-2017 19:01
    Finally got it.

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

    Thank you everyone....


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

    Posted 11-02-2017 17:58
    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


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

    Posted 11-02-2017 18:11
    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.


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

    Posted 11-02-2017 18:20
    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