If you want to use the Max( ) function you need it to look at a range of records. As you have it now it is only looking at the current record, so of course the Max will be whatever the value is in that record.
To get it looking at a range, you will need to create a relationship to a new TO, via the year. I think you'd need to make an invoiceYear field that parses out the year from the invoice date—make it an auto enter field that uses Year ( invoiceDateField ) as its formula. Then you would create a self-join relationship (i.e. to that same table) matching that invoiceYear. Then in your calculation set the Max function to look at the date through that relationship filter and it should work.
It's not really clear what you are trying to do. As @keywords said, the MAX function doesn't really work as you would expect without some extra work.
I tried to create a simple application, but wasn't exactly sure what you expected as output. This isn't difficult, but don't code before you can solve something (at least a simple case) on the back of, say, an envelope. Going to the computer too early will only cause frustration. (The computer, programming, should be the easy part with proper preparation).
Try to create some test data how the code should work first.
If you have FMPA, step through your code in the debugger.
Consider forgetting about the MAX function and just use SQL instead:
ExecuteSQL ( "SELECT MAX(YEAR(INVOICE_DATE)) FROM <TABLE_NAME>" ; "" ; "" )
serialincrement is a custom function (?)
Why not just define a field as 'serial number', incremented by 1 and use that fir the string? SN can be scripted for a new year..
the above is nonsense, just found an identical posting in the german section of the community
Such systems of invoice numbers are rods to beat yoursef with.
Build a table to hold InvoiceYears, where each record is a year. The record has a field that contains the invoice number. Increment this number being sure to trap for record lock errors.
The reason for keeping previous year's invoice number in a table is because just after 1/1 there will be a need to enter both new year invoices and previous year invoices. Trust me on this.
ouch - language problem, no english version here )-:
FortlNrInTextÄndern = SerialIncrement
p.s. see the /15/fmp/... in the above link? change it to 'it', 'fr', 'se', etc. So far, I've found the help to be well done for other languages, just by changing the country-code.
first thought that it's a cf - I fully misunderstood the question, sorry
(I got the manuals in en and de on my iPad, but did not check. No FM here at the moment)
If you aren't going to use FileMaker's build in auto-entered serial numbers, care must be taken if this is.a hosted solution with multiple clients creating invoices. It's quite possible that two clients will produce invoice numbers that are identical if they both create invoices at the same wrong instant. The more users you have, the more likely this becomes.
Setting up a field as an auto-entered serial number that is either manually or via script reset at the start of each year would thus be far safer than using either the Max or a related table of max values to produce this increment.
There is also a technique that uses an auto-entered serial number to produce a second value "on command" that might be used, but it seemus over complex for the purpose described here.