Your Invoice table should have a foreign locationID that you use to create a relationship between Invoice and Location:
one Location --< many Invoices
Location::id = Invoice::id_location
then reference a specific location in a Invoice by setting the foreign key it to the value of the primary key of the desired location record.
Basically, all you need to do is check in your New Invoice script if $$defaultLocationID is set; if so, set the foreign key field to that locationID (or maybe use a dialog to confirm) and have the relevant fields (address) fill in themselves by using an auto-enter calculation that pulls in the data via the relationship.*
Decide on what to do should you find yourself without a default location.
*Note that you should only copy data that are bound to change over time; since a location is defined by its address, this is not going to change, so you store it with the Location and reference it in Invoice; that's what a relational database does.
On the other hand, a client company has an address, but that may change; that where it makes sense to store the company's address at the time of the invoicing with the invoice (same as with VAT rate, product prices, discount rates …)
Errolst said .. Decide on what to do should you find yourself without a default location.
i would add "Decide on what to do should you find yourself with more than one default location".
Thanks for the advice. I’m going to have to read those chapters in my Missing Manuals book to understand how to do that. I thought I was doing something like that, as I had used Serial Numbers to generate a key field in the Locations table and made the key of the default location record available to my New Invoice script. My problem was that I couldn’t figure out how access a Location record from knowing its key.
Until I read the manual and get a better understanding of how relational databases work, I have implemented a more primitive method: The Set Default Location script copies the fields I need into a set of Global fields. Then my New Invoice script can copy the data from those Global fields and put it into the new invoice.
Probably once I understand how to do what you have suggested, I will also see that there is a more elegant way to do the non-default address entry than the method I described in my original question.
As for what to do if there is no default location or more than one, if there is no default location, then the New Invoice script doesn’t enter any location info in the invoice, and I use the method I described in my original question to put in the location (as well as the option to just type the location into the invoice). There won’t be more than one default because the Set Default Location script turns off the previous default indicator when a new one is set.