Getting Started

 View Only
Back to discussions
Expand all | Collapse all

Log Number

  • 1.  Log Number

    Posted 12-17-2021 10:52

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


  • 2.  RE: Log Number

    Posted 12-17-2021 12:33
    is [Log in number]  just a field on the record?

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

    Original Message


  • 3.  RE: Log Number

    Posted 01-06-2022 07:27
    Yes Login Number is just a filed on the record.

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

    Original Message


  • 4.  RE: Log Number

    Posted 01-07-2022 09:04

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

    Original Message


  • 5.  RE: Log Number

    Posted 02-08-2022 10:07

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

    Original Message


  • 6.  RE: Log Number

    Posted 02-08-2022 10:13
    raymond

    This would be a formula text field.

    var text MyDate = ToDate([Quote Date & Time Processed]);
    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
    ------------------------------

    Original Message


  • 7.  RE: Log Number

    Posted 03-22-2022 12:31
    Edited by Raymond Marlon Tiangco 03-22-2022 12:31

    Hi @Mark Shnier

    ​I try to use this formula but i'm still getting Syntax Error.

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

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

    Just to explain further.
    I have a field named Quote Date & Time Processed, which is a Date / Time field type (example: 03-23-2022 03:30 PM)

    And I have another field name Quote No., which will be my formula text field that would need the formula that would show 2203003 based on the data of the Quote Date & Time Processed, where 22 = based on 2022 year,  03 = month of March, 003 = based on the Record ID# in QuickBase so if it is Record ID# 1 it becomes 001 if it is Record ID# 24 it should be 024

    Will you be able to help create the correct formulas?

    Thanks for your help.

    ​

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

    Original Message


  • 8.  RE: Log Number

    Posted 12-17-2021 12:48
    Edited by Paul Peterson 12-17-2021 14:52
    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
    ------------------------------

    Original Message


  • 9.  RE: Log Number

    Posted 02-08-2022 10:47

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

    Original Message


  • 10.  RE: Log Number

    Posted 02-08-2022 11:01
    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
    ------------------------------

    Original Message


  • 11.  RE: Log Number

    Posted 03-22-2022 14:01
    @Raymond Marlon Tiangco
    Try this

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

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

    Original Message


  • 12.  RE: Log Number

    Posted 03-23-2022 08:49
    Hi @Mark Shnier
    I tried the changes you mentioned, but I'm still getting Syntax Error message

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

    List("", $YY, $MM, ToText([Record ID#]))​




    Kindly advise.

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

    Original Message


  • 13.  RE: Log Number

    Posted 03-23-2022 09:15
    This line here needs to end with a semicolon.

    var text YY = Right(ToText(Year([Quote Date & Time Processed])), 2);

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

    Original Message


  • 14.  RE: Log Number

    Posted 03-23-2022 11:15

    Hi @Mark Shnier

    There is still Syntax Error after I end it with a semicolon.


    Will you be able to find what is wrong with the formula?

    Thanks in advance.



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

    Original Message


  • 15.  RE: Log Number

    Posted 03-23-2022 11:22
    Please copy and paste the code as I cannot edit a screen shot.

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

    Original Message


  • 16.  RE: Log Number

    Posted 03-23-2022 11:58

    Hi @Mark Shnier

    Here is the code for the Text Formula field.


    var text MyDate = ToDate([Quote Date & Time Processed]);
    var text YY = Right(ToText(Year([Date Field])), 2);
    var text MM = PadLeft(ToText(Month($MyDate)),2, "0");

    List("", $YY, $MM, ToText([Record ID#]))



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

    Original Message


  • 17.  RE: Log Number

    Posted 03-23-2022 13:06
    sorry for the hassler, I tested this.

    var date MyDate = ToDate([Quote Date & Time Processed]);
    var text YY = Right(ToText(Year($MYDate)), 2);
    var text MM = PadLeft(ToText(Month($MyDate)),2, "0");

    List("", $YY, $MM, ToText([Record ID#]))

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

    Original Message


  • 18.  RE: Log Number

    Posted 03-24-2022 06:57