Forum Discussion

frnmj's avatar
frnmj
Qrew Member
2 years ago
Solved

Invoice Number Formula

I was hoping to create a field that's auto-generated with this format

[YY]+0000 <- where this increments by one every time a new invoice is added. Can someone help me with the formula?



------------------------------
frnmj
------------------------------
10
  • MarkShnier__You's avatar
    MarkShnier__You
    2 years ago
    This should work as a formula text field.  The numeric portion will come from the [Record ID#] which is auto generated when you first save the record.


    IF([Record ID#]>0,
    List("-",
    Right(ToText(Year(ToDate([Date Created]))),2),
    PadLeft(ToText([Record ID#]),4,"0")))

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

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 years ago
    The current year is 2022

    Are you looking for  a result like this

    22-0010 for invoice #10?
    ​ and in 2023 the number would just keep counting up from the last invoice # the previous year?   ie it would not reset back to 1 (that is difficult to do).​

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • frnmj's avatar
      frnmj
      Qrew Member
      2 years ago

      Yes, that's exactly what I wanted it to look like. Would having it reset back to 1 the following year be easier to implement? If so, what would the formula look like? If you have other suggestions of doing this, I'd highly appreciate it.



      ------------------------------
      frnmj
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        2 years ago
        This should work as a formula text field.  The numeric portion will come from the [Record ID#] which is auto generated when you first save the record.


        IF([Record ID#]>0,
        List("-",
        Right(ToText(Year(ToDate([Date Created]))),2),
        PadLeft(ToText([Record ID#]),4,"0")))

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