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

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

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

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

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

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

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

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

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

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

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

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