Calculating an invoice number
Hi all, I'm hoping you can help me.
I have a hire database which generates quotes, invoices, delivery notes etc.
What I want to do is generate a serial invoice number for each quote that turns into an invoice.
Ive set up the database with a JOB ID number and a seperate field containing the invoice number. Obviously the issue is that there will be some records where a quote has not turned into a job and therefore an invoice won't have been generated.
InvoiceNo is the invoice number
ApplyInvoiceNo is a field which has a text value of yes or no in it.
My invoice number should be in the format INV-0001 and the next invoice generated should be INV-0002 etc.
I've had some success in getting the database to generate a text entry when I click yes in the ApplyInvoiceNo field which takes a value list of Yes or empty using If(IsEmpty(Apply Invoice Number); ""; "TEST") which obviously will put TEST into the invoice number field.
However, I'm not sure how to look at all of the records and generate the next invoice number based on the values already stored in the table.
Can anyone help?
I'm very new to filemaker and have limited programming knowledge involving if statements etc. I think a Max operation might be necessary but I'm unsure of the syntax to look at all the records and find the max value then add one to it.
Many thanks to whoever can help!