Your addresses should be related records and not fields within your Contacts table. You include a unique, auto-enter FM-generated serial and call it AddressID. You would then create a new address record and put that AddressID in a field in your Contacts table called PreferredAddress and relate them. In this way, the older address records will be out of sight but will still exist for the Contact's history (include a creation timestamp in Addresses so you will know when the address was last active).
Your Addresses table should also contain the ContactID (again a unique, auto-entered serial). So you may have a Type field in Addresses ... so your Contact can have a Mailing address, Shipping address, Physical address; you won't be limited.
You could then have, in Contacts, ShipAddressID, MailAddressID etc as fields as your business logic dictates.
As soon as you mentioned this I remembered one of the first FM Tutorials, where this sort of thing is explained. Thanks.
I work in FM10 Pro, on a Mac with OS 10.5.8.
I want to make the database easy to use (who doesn't?), so I was planning/hoping to have the newest address show automatically in the relevant "Contacts" record, without having to enter the autocreated AddressID in its field in "Contacts".
Also, I would like to be able to automatically create a new address record when needed.
I am now almost there: I can change/amend existing address details for any given contact with ease, and the most recent one always shows in the "Contact" record, but have run into the (only minor) problem that FM no longer allows me to create new Contacts. We have no need for different types of addresses in what we do.
Tables: Contacts, Addresses
Relationships: Contacts::ContactID = Addresses::ContactID; Contacts::AddressID = Addresses 2::AddressID
Contacts::AddressID is calculation, max(Addresses::AddressID)
The actual address lines on the "Contacts" layout are the relevant lines from "Addresses 2"
There is a button on the "Contacts" layout that takes me to the correct (latest) record for this contact in "Addresses", the record is duplicated, the duplicate can then be amended, automatically gets the highest AddressID and is subsequently shown in the "Contacts" record.
However, when I create a new contact in "Contacts", I am allowed to complete the "Contact"-specific fields, but when I get to commit the first of the address lines (in Addresses 2), the program says the AddressID first needs to be given an valid value before this field can be modified. So essentially the whole thing grinds to a halt.
Relationships: Contacts::ContactID = Addresses::ContactID;
All you need is the primary relationship. Join Contacts::ContactID = Addresses::ContactID and sort the relationship on the addresses side descending on the address creation date. In this way, the FIRST related address for this contact is always the latest. You can then just place the Addresses fields directly on your layout. :^)
The sorting function does indeed do away with the need for second instances of tables. Much simpler; works a treat.