One way would be to simply create the first invoice for the year that itemizes each item purchased in a line items table displayed in a portal.
Then for each subsequent invoice of the year, simply duplicate both the invoice and line items records. This takes a script and you have to loop through your line item records to both duplicate them and then change their invoice numbers to match that of the new invoice, but it can be done fairly simply and you have the added advantage of being able to support a customer renegotiating their contract mid-year if you choose to permit that.
I thought it would be more simple than that. Why doesn't it work to just have a portal with TESTS::test_name TEST_PRICES::price show all their records on the "Customers pricelist" layout. I know that it doesn't work but I don't understand why. There isn't a simple way to do this?
It can, if you have a different set of test_price records for every customer, for each year.
The draw back is that all your invoices for the year will be the same. Any changes to your test_price records will change all your past invoices for the same year. In most businesses, that's a bad idea because they can't guarantee that the invoices will be exactly the same each month. If you business is an exception to that and you are sure you will always invoice a customer exactly the same way each year, then yes, you can set up a table like this, but make sure you include both a customer ID and an effective date field so that you can create a new set of these records every year.
Setting up a "duplicate current invoice" script isn't all that complicated.
How would I go about setting up that script then? I don't have any experience with filemaker scripting yet.
Adapting from an earlier thread on the same subject:
#Capture the number of the next new invoice and save it in a variable
Set Variable [$NewInvoice, GetNextSerialValue ( get(fileName) ; YourTable::YourInvoiceID)]
#Pull up the portal's records in a found set on a layout that refers to the portal records
Go To Related Record [Show Only Related Records ; From Table "YourPortalTable"; Using Layout "Your Layout" (YourPortalTable)]
#Duplicate each portal record and give it the correct ID number to link to the new invoice
Set Field [PortalTable::InvoiceID, $NewInvoice]
Go To Record/Request/Page [First]
Exit Loop If [Get(foundcount) = 0]
Go To layout [original layout]
#Duplicate the Invoice Record
You'll need to substitute your names for the object names given in this script. "PortalTable", for example, would be replaced by the name of your line items table.
Awesome! I'll give this a shot and let you know how it goes.