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? --------...
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
    ------------------------------
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
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
    Icon for Qrew Legend rankQrew Legend
    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
    ------------------------------
    • frnmj's avatar
      frnmj
      Qrew Member
      2 years ago
      Sorry it took me a while to respond. The formula works perfectly!

      ------------------------------
      frnmj
      ------------------------------