7 Replies Latest reply on Apr 16, 2012 4:47 PM by philmodjunk

    Make Family Member profiles link to Current profile-FM11

    FM_Rookie

      Title

      Make Family Member profiles link to Current profile-FM11

      Post

      I've been working on this TOO LONG, & I still can't figure it out.  The screenshot below shows what I want, but I can't get it to work (ignore the data there that makes it seem that it works... it doesn't).

      Here's what I have

      Contacts::__kp_ContactID

      FamilyJoinTable::__kp_JoinTableID
      FamilyJoinTable::_kf_ContactID
      FamilyJoinTable::FamilyID

      Their are 3 Table Occurences (TO): (Those in CAPS are the table the layout is based on)
      CONTACTS
      contacts_FAMILYJOINTABLE
      contacts_familyjointable_FAMILYMEMBER (this is based on table "Contacts")

      The TOs are joined
      Contacts::__kp_ContactID = FamilyJoinTable::_kf_ContactID = contacts_familyjointable_FAMILYMEMBER::__kp_ContactID (because this is actually the "Contacts" table)

      OK... looking at the portal below, how do I use the drop-down list (value list works fine) to populate AND join them?  When I hit "go" (which brings them to the related record) it doesn't work because I can get FamilyJoinTable::FamilyID to produce an ID number and then populate in this related record.

      Should I produce the FamilyID in the "Contacts" table??  If so how?  I'm not good at writing (or even understanding scripts).  Most of mine have been copied from people who know what they're doing.

      Thanks!

      pic.png

        • 1. Re: Make Family Member profiles link to Current profile-FM11
          philmodjunk

          First, you need to modify your relationships to get:

          Contacts----<contacts_familyJointable>-----contacts_familyJointable_familymember

          Contacts::__kp_ContactID = contacts_FamilyJoinTable::_kf_ContactID
          contacts_FamilyJoinTable::_kf_contactID 2 = contacts_familyjointable_FAMILYMEMBER::__kp_ContactID

          Then your portal should list contacts_FamilyJoinTable in "show related records from" and your fields would be from contacts_familyJointable_familymember except for a drop down list or pop up menum where you select a value in _kf_contactID 2 to link a specific contact to the current contact as a family member.

          And you'd enable "allow creation of records via this relationship for contacts_familyJointable in the contacts to contacts_familyJointable relationship.

          • 2. Re: Make Family Member profiles link to Current profile-FM11
            FM_Rookie

            Seems this works only with spouse.  What about children?  Don't I need a FamilyID?  How would I generate if & populate the join table with it?  (No need for concern about blended families, because they are international students here for a limited period of time & unlikely to divorce or die.

            • 3. Re: Make Family Member profiles link to Current profile-FM11
              philmodjunk

              You can add as many family members as you need this way. When you link in a family member, you are selecting that individuals contact ID in the second foreign key fields. Using a portal, this works for any number of contacts.

              You may indeed need a family ID in a related Family table where you have one record for each family, but not in a join table set up like this.

              I'd set this up with a mailing address and other family specific data in a related family table and then use __pk_FamilyID from this table to link to as many contacts as needed by selecting a value in a "_fk_familyID" field in Contacts. You'll only need the join table (between family and contacts) if you need to document a contact as being part of more than one family.

              • 4. Re: Make Family Member profiles link to Current profile-FM11
                FM_Rookie

                Ok, I set up the relationships the way you mentioned.  However, (looking at the above image), when I hit the "go" button to go to "Frank Speaking's" wife, "Alpha Liopic," Frank foes not show up on her list of family members.  What am I doing wrong?

                • 5. Re: Make Family Member profiles link to Current profile-FM11
                  philmodjunk

                  I suggest using the table of Family records instead so that you don't encounter this issue.

                  Links in a join table are "one way". To get a two way link requires creating a second record in the join table with the ID numbers switched so that contact ID 1 has the value from contact ID 2 and vice versa. This could be done with a script trigger.

                  • 6. Re: Make Family Member profiles link to Current profile-FM11
                    FM_Rookie

                    Could use some help with the script trigger

                    • 7. Re: Make Family Member profiles link to Current profile-FM11
                      philmodjunk

                      Do you really want to do that? It's simpler to use a Family table and link all contacts to a common family record.

                      The trigger used depends on the design of your layout. If you use a pop up menu for selecting the related contact, onObjectModify works. If you use a drop down list, onObjectSave is a better option.

                      The script runs like this:

                      Set variable [$ID1 ; value: contacts_FamilyJoinTable::_kf_ContactID 1 ]
                      Set variable [$ID2 ; value: contacts_FamilyJoinTable::_kf_ContactID 2 ]
                      Freeze window
                      Go to layout [contacts_FamilyJoinTable]
                      #Check to see if linking record already exists
                      Enter Find Mode []
                      Set Field [ contacts_FamilyJoinTable::_kf_ContactID 1 ; $ID2 ]
                      Set Field [ contacts_FamilyJoinTable::_kf_ContactID 2 ; $ID1 ]
                      Set Error capture [on]
                      Perform Find []
                      If [ not get ( FoundCount ) ]
                         New Record/Request
                         Set Field [ contacts_FamilyJoinTable::_kf_ContactID 1 ; $ID2 ]
                         Set Field [ contacts_FamilyJoinTable::_kf_ContactID 2 ; $ID1 ]
                      ENd If
                      Go To Layout [original layout]