Forum Discussion

Raymond_MarlonT's avatar
Raymond_MarlonT
Qrew Trainee
5 years ago

Ticket Number

Hi, How can I create a ticket no. based on Month-Day-Year and Record ID? I already have a field name Date & Time Processed which shows 04-06-2020 6:19 PM. with Record ID# 10 I want to see a ti...
Formulas and functions
PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
Someone else may have a better answer, but this should work, 

ToText(Day(Today())) & ToText(Month(Today())) & ToText(Year(Today())) & [Record ID#]

------------------------------
Paul Peterson
------------------------------
Raymond_MarlonT's avatar
Raymond_MarlonT
Qrew Trainee
5 years ago

Hi Paul,

Thank you for your feedback. But the ticket number is based on the current date not based on the date indicated in the Date & Time Processed  which is 04-06-2020 6:19 PM. The ticket number changes when the current date changes.

If you can suggest a better formula that would be great.



------------------------------
Raymond Marlon
------------------------------
  • NathanHawe's avatar
    NathanHawe
    Qrew Cadet
    5 years ago
    Paul's formula will work if you replace Today() with ToDate([Date & Time Processed]).  Here's that change with some additional statements to pad the day and month values.

    if(Month(ToDate([Date & Time Processed])) < 10, "0", "") & Month(ToDate([Date & Time Processed])) 
& if(Day(ToDate([Date & Time Processed])) < 10, "0", "") & Day(ToDate([Date & Time Processed])) 
& Year(ToDate([Date & Time Processed])) & [Record ID#]​


    ------------------------------
    Nathan Hawe
    ------------------------------
    • JohnCrosland's avatar
      JohnCrosland
      Qrew Trainee
      5 years ago
      You can also use PadLeft() formula instead of the if statements in Nathan's formula.
      PadLeft(ToText(Month(ToDate([Date & Time Processed]))), 2, "0") & PadLeft(...

      This solution is a formula field and currently can't be set as the key. If you need this as the key value, then you will need to use another method. I would recommend keeping the key value as Record ID at the end, then you can use the right function to retrieve it. in another table.
      ToNumber(Right([Field],Length([Field])-8))

      ------------------------------
      John Crosland
      Software Developer
      Inglett & Stubbs
      Mableton GA
      ------------------------------
    • Raymond_MarlonT's avatar
      Raymond_MarlonT
      Qrew Trainee
      5 years ago

      Hi @Nathan Hawe

      Thanks for taking time to help with the formula that you provided.

      It works to what I needed.
      Cheers to you! :)

      Have a great day!​



      ------------------------------
      Raymond Marlon
      ------------------------------