Forum Discussion

Raymond_MarlonT's avatar
Qrew Trainee
5 years ago

Ticket Number


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 ticket number that looks like this 0406202010

Can you help me create the correct formula?

Please advise.


5 Replies

  • PaulPeterson1's avatar
    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
      Qrew Trainee

      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
        Qrew Cadet
        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