Forum Discussion

Raymond_MarlonT's avatar
Raymond_MarlonT
Qrew Trainee
3 years ago

Log Number

Hi,

May someone help me create a formula for a Rich Text (Formula) field  to be able to create a log number based on a Date / Time field.

Example:

Date / Time = 12-17-2021 1:33 PM

I want may Rich Text (Formula) field to to show 2112001

Where 21 = year,  12 = month, 001 is login number

Please advise. Thank  you in advance.



------------------------------
Raymond Marlon Tiangco
------------------------------

19 Replies

  • is [Log in number]  just a field on the record?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • Raymond_MarlonT's avatar
      Raymond_MarlonT
      Qrew Trainee
      Yes Login Number is just a filed on the record.

      ------------------------------
      Raymond Marlon Tiangco
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        Try this

        var text MyDate = ToDate([My Date / Time field]);
        var text YY = Right(ToText(Year($My Date));
        var text MM = PadLeft(ToText(Month($My Date)),2, "0");

        List("", $YY, $MM, ToText([Login Number]))



        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------
  • PaulPeterson1's avatar
    PaulPeterson1
    Qrew Assistant Captain
    You could create two fields to extract the year and month similar to:

    Formula Text fields:
    Year Date:
    ToText(Right(Year([Date Field]), 2))

    Month Date:
    PadLeft(ToText(Month([Date Field])), 2, "0") 

    then in your log field:

    var text formulaQuery = "{FID1.EX.'" & [Year Date] & "'} AND {FID2.EX.'" & [Month Date] & "'} AND {3.LTE.'" & [Record ID#] & "'}";

    [Year Date] & [Month Date] & PadLeft(ToText(Size(GetRecords($formulaquery))), 5, "0")

    where FID1 is the field ID of the year date field and FID2 is the field ID for the month date field

    ------------------------------
    Paul Peterson
    ------------------------------
    • Raymond_MarlonT's avatar
      Raymond_MarlonT
      Qrew Trainee

      Hi @Paul Peterson

      Hi I'm getting "Formula error -- Bad or missing arguments in function call" message when I tried to use this formula

      ToText(Right(Year([Date Field]), 2))

      in the "Year Date" Formula text field.

      Is there another way? Please advise.

      ā€‹



      ------------------------------
      Raymond Marlon Tiangco
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        The Year function returns a number so you need to convert it to text first.

        Right(ToText(Year([Date Field])), 2)

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------