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

Can you help me create the correct formula?

Please advise.



------------------------------
Raymond
------------------------------
Formulas and functions
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago
    Raymond,

    There is a trick to getting this to work right.  Quick Base counts months 1 to 12, therefore we have to force January to be 01 and not 1.  It is the same with the days of the month.

    Create a Formula Text field.

    Enter this as a the formula.  

    var text NumMonth = If( Month(ToDate([Date & Time Processed]))<10, "0" & Month(ToDate([Date & Time Processed])), Month(ToDate([Date & Time Processed])) );

    var text NumDay = If( Day(ToDate([Date & Time Processed]))<10, "0" & Day(ToDate([Date & Time Processed])), Day(ToDate([Date & Time Processed])) );

    var text NumYear = ToText(Year(ToDate([Date & Time Processed]))) ;

    var text NumRID = ToText([Record ID#]);

    var text Ticket = $NumMonth & $NumDay & $NumYear & NumRID;

    $Ticket


    I did not test this so double check my parentheses for syntax error.
    The first four lines create text variables for the components that make up your Ticket Number. 
    The last var declaration puts them together in the order for your ticket.   
    The final line will show the value of the variable Ticket in the Quick Base user interface.

    Please reach back if there is problem.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • Raymond_MarlonT's avatar
      Raymond_MarlonT
      Qrew Trainee
      5 years ago

      Hi @Don Larson,
      Good day!

      Thank you for helping me with my ticket number formula. But I'm getting a Formula Syntax Error stating "The argument list of the function NumRID must begin with a left parenthesis."​

      Below is the one I entered in the formula as you stated.

      var text NumMonth = If(Month(ToDate([Date & Time Processed]))<10, "0" & Month(ToDate([Date & Time Processed])), Month(ToDate([Date & Time Processed])));
      var text NumDay = If(Day(ToDate([Date & Time Processed]))<10, "0" & Day(ToDate([Date & Time Processed])), Day(ToDate([Date & Time Processed])));
      var text NumYear = ToText(Year(ToDate([Date & Time Processed])));
      var text NumYear = ToText(Year(ToDate([Date & Time Processed])));
      var text Ticket = $NumMonth & $NumDay & $NumYear &       NumRID;
      $Ticket

      When I added a add a parenthesis in before the NumRID, I get this Syntax Error "An identifier must follow the type in a variable declaration."

      var text NumMonth = If(Month(ToDate([Date & Time Processed]))<10, "0" & Month(ToDate([Date & Time Processed])), Month(ToDate([Date & Time Processed])));
      var text NumDay = If(Day(ToDate([Date & Time Processed]))<10, "0" & Day(ToDate([Date & Time Processed])), Day(ToDate([Date & Time Processed])));
      var text NumYear = ToText(Year(ToDate([Date & Time Processed])));
      var text (NumRID = ToText([Record ID#]);
      var text Ticket = $NumMonth & $NumDay & $NumYear & NumRID;
      $Ticket


      Please advise. Thanks!



      ------------------------------
      Raymond Marlon
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        5 years ago
        Raymond,

        The variable declaration for Ticket has an error which was in the original post I made.   To use the previous variable you have to have the $ sign.   It should be $NumRID.

        So you should remove the parentheses from the declaration of the NumRID and edit the declaration of Ticket.

        Hopefully that is the only error from the original post.

        ------------------------------
        Don Larson
        Paasporter
        Westlake OH
        ------------------------------