How to find next invoice number?
I am lost in a stupid thing: I have an invoice table, each invoice have an ID a date and a number.
I want to know wich will be the next invoice number when I will create a new one.
So the first thing I have to know is which is the date of the last invoice, may be the max date in table.
In this way I should be able to find max invoice number too and be sure that it is relate to the current year.
I have created a selfjoin table using invoiceID and a calculation field to find the max date. Then I have created one more TO of Inoivce table related by maxDate field and date field. This way I should be able to see all invoices that have the same date when the date is the most recent one.
Next I have created another calculation field to get the maxInvoiceNumber of the last invoice TO.
The problem is that such field does not return any value but an empty field :-(
My suppositions are broken somewhere and I am unable to find where... can anybody give me some suggestions to go out of here?