4 Replies Latest reply on Apr 27, 2011 5:56 AM by Meulendijk

    How to keep track of address changes

    Meulendijk

      Title

      How to keep track of address changes

      Post

      Hi,

      I am building a database with a.o. a "Contacts" table, with fields for name, initials, address lines, etc.

      I would like to be able to keep some sort of record of address changes. I am envisaging changing the address, as and when necessary, in the relevant "Contacts" record - so that is always the up-to-date one - and would then like to see the old data written to something like "old_address" with a amendment date. These fields do not normally have to be visible; only when specifically requested.

      Not having any experience with them, I was wondering if using repeating fields would perhaps be useful in this respect, and if so, how. 

      But of course, any suggestion to go about this in a different way would be most welcome.

      Thanks,

      Jan Meulendijk

        • 1. Re: How to keep track of address changes
          LaRetta_1

          Hi Jan,

          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.

          • 2. Re: How to keep track of address changes
            Meulendijk

            Hello LaRetta,

            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.

            Any suggestions?

            Jan Meulendijk

            • 3. Re: How to keep track of address changes
              LaRetta_1

              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.  :^)

              • 4. Re: How to keep track of address changes
                Meulendijk

                Thanks LaRetta!

                The sorting function does indeed do away with the need for second instances of tables. Much simpler; works a treat.

                Jan Meulendijk