5 Replies Latest reply on Aug 13, 2014 5:46 AM by cat22fish

    Link two fields in the same table to view spouse information

    cat22fish

      Title

      Link two fields in the same table to view spouse information

      Post

           I hope someone can help me, I'm very new to FM and I have got quite far with it but there are some things I want to do which I have no idea where to start. I have a database which stores contact information.  Some of the contacts on the database have relationships with other contacts on the database e.g. a husband and wife have their own entry.  What I would like to do is to link the contacts who are related and have their name displayed in each other's record on the layout but I cannot work out how to do this.  This is what I've tried to do:

           I've added a relatedId field as a number field (which I'm guessing should contain the serialnumber of the spouse)

           Copied the main contacts table occurrence and created a link between the two.

           Placed a portal on the layout database and displayed RelatedId field (in browse mode), created a copy of this field (including settings but changed it to in find mode, so I can see the full name)

           I think I have completely confused it because it is displaying the RelatedId field but then when I want it to display the name of the other record it reverts to the original records name.  So i'm guessing I'm asking it to pull in the wrong information.  ahhh I know I'm going about this all wrong, any really easy step guide advice greatly received.

           Thank you in advance for any help.

            

        • 1. Re: Link two fields in the same table to view spouse information
          philmcgeehan

               You're on the right track with creating a Table Occurrence of your main Contacts table.
               I imagine it's just the relationship that might be wrong, or you're referencing the wrong T.O. when displaying the spouse information.

               Your relationships should look something like this:
               (I've taken liberties with the field names: _pk_ for Primary Key and _fk_ for Foreign Key)
               Contacts::_pk_ID ---- = ---- ContactsSpouse::_fk_ID

               In the main contacts layout you should have a portal showing records from ContactsSpouse;
               you can have any information in the portal such as: ContactsSpouse::_pk_ID and ContactsSpouse::Name ...
               But the records in the portal all need to be showing records from the second table occurrence!

          • 2. Re: Link two fields in the same table to view spouse information
            philmcgeehan

                 To add a spouse to a contact you should be able to just start typing into the empty row in the portal. FM should automatically set the primary key for for the spouse (as you should have this field as an auto-entered serial number) and it will also automatically set the foreign key from the primary key of the contact to which you are adding a spouse.

            • 3. Re: Link two fields in the same table to view spouse information
              cat22fish

                   Thank you for your help, I had it working through your instructions (which are great !)  but I had to manually enter the corresponding serial number into a spouseID field with the matching spouse otherwise it will not show any information.  I have the portal fields set up as drop down lists and then value is from ContactSpouse. I feel like I've missed a very basic step? Also sometimes the spouse of a contact does not have their own record and I would like to be able to free type on these occassions, is it possible to do this in the same field?

                    

              • 4. Re: Link two fields in the same table to view spouse information
                philmodjunk

                     The typical value list for this is a "use values from a field" value list where you specify the _pk_ID field as the source of values for field 1 and a name field as the source of values for field 2. At your option, you can then hide the first field by selecting "Show only values from second field". With or without that option selected, this is a value list where you can select a value by name, but the value specified for the first field, the ID, is what is entered when you select a value from the value list.

                • 5. Re: Link two fields in the same table to view spouse information
                  cat22fish

                       Thank you for all your help.  I'm still struggling with this but I think it's me rather than the instructions you have given me.  I think I will have a break and come back to this at a later date.