6 Replies Latest reply on Mar 28, 2012 3:00 PM by Marty1




      Contacts & Addresses



      I have 2 databases, one to hold contact information, the other to hold an address. The reason for the 2 databases is that some contacts have common addresses, i.e. they share the same office block/ faculty. I wish to create a new contact, then either create a new address for this contact, or select an existing address and associate that address to the contact.

      My problem is that after creating the contact, when I go to create a new address a new contact is also created; how do I retain the new address to the current contact, and likewise when I list the addresses I can not seem to select an address to link to a contact.

      I show the simple relationship diagram so far


        • 1. Re: Contacts & Addresses

          Question: You've indicated that many contacts need to link to the same address record. Is the reverse also true? Can one contact link to more than one address record? (Might have work and home addresses or shipping and billing addresses).

          If a contacts record can only link to one addresses record, your relationship should be:

          Contacts::_kf_address_id = Addresses::_kp_address_id

          If a contacts record can have many addresses, you have a many to many relationship and that requires using a join table:


          Contacts::_kp_Contact_id = Contact_Address::_kf_Contact_id
          Addresses::_kp_Address_id = Contact_Address::_kf_address_id

          • 2. Re: Contacts & Addresses

            Thanks for the suggestion. I do have that relationship currently; when in a layout 'owned' by Contacts, the creation of a new contact is good. I then go to another layout 'owned' by Address, and see all the addresses listed. By selecting an address, what script steps are required to tell the 'Contact' that this address is associated?

            Many thanks

            • 3. Re: Contacts & Addresses

              I don't think your current relationship will work. That's why I am suggesting a different relationship. Which of the options is best for your database depends on the nature of your address data and how you intend to use it.

              • 4. Re: Contacts & Addresses

                Sorry, I think I must have missed your point (or maybe I'm not understanding your suggestion correctly).

                Under the premise that one Address can be linked to several contacts, I have used your suggested relationship

                Contacts::_kf_address_id = Addresses::_kp_address_id

                As shown.

                So my remaining challenge is to link an address to the contact by selecting it. ?


                • 5. Re: Contacts & Addresses

                  If you look at the image you uploaded with your last message, you'll see that you still showed your original relationship. Your last post shows the "one to many" option.

                  There are quite a few different methods for looking up an address record and assigning it's value in _kp_address_id to the current contact record's _kf_address_id field.

                  The simplest is to format contacts::_kf_address_id with a value list of addresses. You can define a value list where Address::_kp_Address_id is listed as "field 1" and then a text field can be specified for "field 2". You might want to define a calculation field that combines more than one addy_line field into one field and possibly the county and country in order to make it easier to select the correct address record. The value list can be set up to hide the id number and just display field 2 data.

                  You can also set up search portals and auto-completing values lists based on a text field in order to make it easier to select the correct record. These methods are useful when you have large numbers of values to choose from. Here's a demo file: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                  You can also set up conditional value lists where you limit the values in the list by selecting a category (such as a country and/or a county) and then your value list only displays values that are a member of that category.

                  • 6. Re: Contacts & Addresses

                     Thanks you, a helpful demo.

                    The relationships have been modified and working now - great !

                    My issue was concerned with the population of the primary and foreign key fields, the links were not correct. Your demo solved that - have a good day.