Calculate next invoice number

[Jason Turley]

I have a very simple invoicing solution and am struggling with automating the next invoice number.

The invoice number is a text field and made up of 3 parts; the project number (446), "INV", and an incremental 3 digit number "001".

invoice number = 446INV001 (next number would be = 446INV002)

I'd like to have the invoice number field automatically calculate the next invoice number when a new record is created with a matching project number. How can I increment the final 3 digits but still keep the invoice number in the same format? I can work out the relationship bit without problem, but the calculation is stumping me.

any suggestions?

Jason...

[Jason Turley]

I have a very simple invoicing solution and am struggling with automating the next invoice number.

The invoice number is a text field and made up of 3 parts; the project number (446), "INV", and an incremental 3 digit number "001".

invoice number = 446INV001 (next number would be = 446INV002)

I'd like to have the invoice number field automatically calculate the next invoice number when a new record is created with a matching project number. How can I increment the final 3 digits but still keep the invoice number in the same format? I can work out the relationship bit without problem, but the calculation is stumping me.

any suggestions?

Jason...

[Jason Turley]

I have a very simple invoicing solution and am struggling with automating the next invoice number.

The invoice number is a text field and made up of 3 parts; the project number (446), "INV", and an incremental 3 digit number "001".

invoice number = 446INV001 (next number would be = 446INV002)

I'd like to have the invoice number field automatically calculate the next invoice number when a new record is created with a matching project number. How can I increment the final 3 digits but still keep the invoice number in the same format? I can work out the relationship bit without problem, but the calculation is stumping me.

any suggestions?

Jason...

[Søren Dyhr]

Your construction is not particular 1NF (one fact per field) but you can seek inspiration to your numbering in this old template:

http://www.filemakerpros.com/SerialCat.sit

...but be aware that today should/ought etc. Lookups be inside a calc'field/autoenter instead. Anyways give it a whirl and see what I'm hinting at.

--sd

[Søren Dyhr]

Your construction is not particular 1NF (one fact per field) but you can seek inspiration to your numbering in this old template:

http://www.filemakerpros.com/SerialCat.sit

...but be aware that today should/ought etc. Lookups be inside a calc'field/autoenter instead. Anyways give it a whirl and see what I'm hinting at.

--sd

[Søren Dyhr]

Your construction is not particular 1NF (one fact per field) but you can seek inspiration to your numbering in this old template:

http://www.filemakerpros.com/SerialCat.sit

...but be aware that today should/ought etc. Lookups be inside a calc'field/autoenter instead. Anyways give it a whirl and see what I'm hinting at.

--sd

[Jason Turley]

Forgot to mention I will be storing the next invoice number in a calculated field and when a new record is created the invoice number will be automatically looked up.

I think what I am looking for is a sort of Max calc. Trouble is I want it to work with text and numbers but retain the formatting of previous invoices e.g. 446INV001, 446INV002 etc...

Jason

[Jason Turley]

Forgot to mention I will be storing the next invoice number in a calculated field and when a new record is created the invoice number will be automatically looked up.

I think what I am looking for is a sort of Max calc. Trouble is I want it to work with text and numbers but retain the formatting of previous invoices e.g. 446INV001, 446INV002 etc...

Jason

[Jason Turley]

Forgot to mention I will be storing the next invoice number in a calculated field and when a new record is created the invoice number will be automatically looked up.

I think what I am looking for is a sort of Max calc. Trouble is I want it to work with text and numbers but retain the formatting of previous invoices e.g. 446INV001, 446INV002 etc...

Jason

[-Queue-]

See attached for sample. Note that the relationship is sorted by pseudoSerial descending and the auto-enter calc has 'do not evaluate' deselected.

[-Queue-]

See attached for sample. Note that the relationship is sorted by pseudoSerial descending and the auto-enter calc has 'do not evaluate' deselected.

[-Queue-]

See attached for sample. Note that the relationship is sorted by pseudoSerial descending and the auto-enter calc has 'do not evaluate' deselected.

NextInvoice.zip