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

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