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

is [Log in number] just a field on the record?------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

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

Yes Login Number is just a filed on the record.------------------------------Raymond Marlon Tiangco------------------------------

Try thisvar 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------------------------------

Hi @Mark Shnier, Is this the formula I should write in the Rich Text Field? is "My Date / Time Field" a certain field name? How about "My Date"? Because my field name is "Quote Date & Time Processed" May i know how am I supposed to type the formula? since I'm getting an error message "Formula Syntax Error" Please advise. ------------------------------Raymond Marlon Tiangco------------------------------

Log Number | Qrew Discussions

19 Replies

PaulPeterson1
Qrew Assistant Captain
3 years ago
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
  Qrew Trainee
  3 years ago
  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
    Qrew Legend
    3 years ago
    @Raymond Marlon Tiangco
    Try this
    
    Right(ToText(Year([Date Field])), 2)
    
    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
MarkShnier__You
Qrew Legend
3 years ago
is [Log in number] just a field on the record?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- Raymond_MarlonT
  Qrew Trainee
  3 years ago
  Yes Login Number is just a filed on the record.
  
  ------------------------------
  Raymond Marlon Tiangco
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    3 years ago
    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
    ------------------------------