Forum Discussion

Qrew Member

3 years ago

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

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

Qrew Legend

3 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
Qrew Member
3 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
  Qrew Legend
  3 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
    Qrew Member
    3 years ago
    Sorry it took me a while to respond. The formula works perfectly!
    
    ------------------------------
    frnmj
    ------------------------------