Why can't your invoice number field be an auto-entered serial number so that it automatically enters the next number just like InvoiceID? If this value needs to with 1 each new year (I can see that you aren't using it as the primary key), You can either manually or via script reset the next serial value setting back to 1.
thanks for the reply,
yes I could do this way too but I have the same problem at the beginning of each year and I do not want to manually change next serial number value.
I can script something but I have to check max date and current date to calculate if I have to reset counter. Could be easier if you automatically run the script for each new invoice.
My way should work too I think, I have got some different result, seems correct, by changing the calculation: I have unchecked the option "Do not evaluate if all referenced fields are empty". In this way for some reason the max number field works.
I can script something but I have to check max date and current date to calculate if I have to reset counter.
That's actually a pretty simple script.
Yes, I suppose there is an action to reset or to set the next serial number value, I didn't use it never till now.
About my solution, I am not sure if the global storage is correct for my calculation fields and I don't understand why by uncheking the "Do not evaluate if all referenced fields are empty" option in calculation things seems to work.
Calculation should return always current data results and in my relations there is always data to compare. Why does it make difference?
..anyway my solution still doesn't work.
So I am trying to apply yours but I have problems too :-(
1. If I create a new Invoice and I delete it, the next serial number will be wrong because it goes to the next while the previous one was not used.
2. I cannot fin any layout script trigger that could check the invoice number when I start to inser a new Invoice.
3. I suppose the action to use is "Set next serial value" but I have to find a script trigger to call even when I delete a new invoice that I have started and not completed.
..so I have created two buttons and two scripts to manage such situations. But again I don't feel comfortable with this solutions:
- If I need to know which was the last invoice number of previous year I still need an approach similar to mine;
- I cannot set the invoice number to be unique so it could happen that for a mistake two invoices of the same year have the same number;
- I have to add buttons that call scripts, but if I use the status bar default buttons all my work is not useful because scripts will not work.
If I can make work my solution, I should not have all these possibile mistakes and I do not have to script anything.
What do you think about?
Global calculations evaluate according to really funky logic. They update to compute a value from the most recently modified field for any field that they reference so this value can be very misleading. A Global storage option should not be needed for this.
Your method needs a relationship that matches Invoices to invoices by year rather than invoice number:
Invoices::cYear = Invoices|SameYear::cYear
Where cYear is defined as Year ( date ) and is defined to return a number data type.
Then Max ( Invoices|SameYear::invoiceNumber ) + 1
can compute the next number in the series. Be sure to clear both "do not evaluate if all referenced fields are empty" and "Do not replace existing value" check boxes.
But here's a script that can run automatically to reset the next serial value:
Define a summary field, sMaxInvoiceDate, in Invoices to compute the maximum of Date to use with this script.
Go to Layout ["Invoices" ( INvoices ) ]
Show All Records
If [ Year ( Get ( CurrentDate ) ) > sMaxInvoiceDate // time to reset the serial number ]
Set Next Serial Value [ Invoices::InvoiceNumber ; 1 ]
Use File options to set this script to run "onFirstWindowOpen". It will run every time the file is opened, but will reset the serial number only at the start of the new year. If you host this file with FileMaker Server, you can also use a server schedule to run this script once a day.
many thanks for the reply, it is really frustrating for me to be still here searchin for a solution of this little thing!
I will try your suggestion to make work my solution, about yours I am using FM 11 Pro Adv, no server at all and may be I do not have the onFirstWindowOpen event, may be I could eventually try with the file start up script, but it still seems a more complicated way for me.
The option is still there in FileOptions for FileMaker 11, but it's not identified as a script trigger. You use the option for performing a script when the file is opened--which is the same thing with a different name.
At the end your fix to my solution does the job and seems easier to me.
File start up script is a problem for me too if I use it on mobile device with FM GO: for some reason it will uncheck such start up script when I copy back the database to my mac.
if I use it on mobile device with FM GO: for some reason it will uncheck such start up script when I copy back the database to my mac.
That is odd. I've not seen that behavior with FM GO 12 and 13. The Known Bugs List database the I created and maintain uses such a script to select different layouts depending on whether FM Go or FM Pro opened the file.