6 Replies Latest reply on May 21, 2012 3:46 PM by philmodjunk

    Trying to relate records - any help appreciated.

      Title

      Trying to relate records - any help appreciated.

      Post

      Scenario for a "contacts" database:

      3 records - 1) Husband, 2) Wife, 3) Wife's Mother...

      I am trying to create a portal that would show the 1st level of related records.

      The file would have an "add related record" button that would copy the current record's serial ID to the record I want to relate and visa versa - so that each record has the other's ID in it (OR, if better, another record is created somewhere with the related info in it... I can't seem to figure out which way is better / more elegant / easier)...

      Example: from Husband I relate wife and each record would end up with the other's ID in it.

      In the portal, Husband's record would show Wife as a related record... and Wife's record would show Husband.

      Now I add Wife's Mother as a related record to Wife...

      Now... Husband should only show wife... Wife's Mother should only show Wife... And Wife should show both Husband and Wife's Mother.

      Any (and all) help appreciated.

      Thanx.

       

        • 1. Re: Trying to relate records - any help appreciated.
          philmodjunk

          This could be described as a "many to many self join". You appear to undestand how to create a self join relationship, but since each contact can relate to many other contacts and vice versa, it's also a many to many relationship.

          You need a join table:

          Contacts---<Contact_Join>----RelatedContacts

          Contacts::ContactID = Contact_Join::ContactID 1
          RelatedContacts::ContactID = Contact_Join::ContactID 2

          where Contacts and RelatedContacts are both occurrences of your contacts table.

          You can then create any number of records in Contact_Join to link a contact to any number of other contacts.

          To make the link work in both directions like you described with the husband and wife requires using a script to make a second Join record with the ID numbers reversed.

          Example:

          If wife has ID = 2 and Husband has ID = 500

          Create two Join records, one with Contact ID 1 = 2 and Contact ID 2 = 500 to link wife to husband and one with Contact ID 1 = 500 and Contact ID 2 = 2 to link Husband to Wife.

          • 2. Re: Trying to relate records - any help appreciated.

            PhilModJunk... Thanx... You've been a great help...

            Ok... 2 last requests (for the moment)...

            ---- 1st Request ----

            It does seem a better solution to creat a Join table rather that a repeating field...

            But the script makes the screen flash since its going back and forth between layouts...

            Is there any way this can be done in the background to prevent screen flash?

            Here is the relevant portion of my script... From "Contacts", a NEW related contact is created...

             

            **Custom Dialog = Create a NEW related contact**

            > Set Variable [$ID1; Value:Contacts:Client ID]

            > Goto Layout{"Related Contact Joiner"]

            > New Record

            > Set Field [Related Contact Joiner::ID1; $ID1]

            > Insert Calculated Result [Select; Related Contact Joiner::Contact ID2; GetNextSerialValue(Contacts::Client ID)]

            > GotoLayout [Contacts]

            > New Record

             

            ---- 2nd Request ----

            Ok... It's "Cinco De Mayo"... & I already forgot my 2nd request... LOL.

            I will follow up when the problem presents itself again.

             

            Thanx for any and all input.

            • 3. Re: Trying to relate records - any help appreciated.

              Oh yeah... LOL...

              ---- 2nd Request ----

              I've got a "drop-Down-List" field - Select all on entry...

              When I tab into it, the entire list shows - GOOD.

              I can then scroll through the items with the up/down keys - GOOD.

              But when I scroll to my desired selection and TAB out - the field doesn't take the selection.

              The selection is only taken if I use the enter or return key to move to the next field.

              Is there any way to have the TAB key keep the selection?

              Thanx again.

              • 4. Re: Trying to relate records - any help appreciated.
                philmodjunk

                1) Use Freeze Window to prevent the window from updating until the script finishes and returns to the original layout.

                2) There is no way that I know of to use the tab key to select the value short of taking paint to your keyboard and relabeling the keys...

                • 5. Re: Trying to relate records - any help appreciated.

                  OK...

                  Ive done everything listed here...

                  But I cant get any related data to appear...

                  I've set up the following as suggested...

                  ************************************************

                  Contacts---<Contact_Join>----RelatedContacts

                  Contacts::ContactID = Contact_Join::ContactID 1
                  RelatedContacts::ContactID = Contact_Join::ContactID 2

                  where Contacts and RelatedContacts are both occurrences of your contacts table.

                  ************************************************

                  And the "Contact Join" table is populated with 2 different IDs for 2 seperate cantacts.

                  On my "Contacts" layout I have a ""Related Contacts" field - Nothing shows up.

                  • 6. Re: Trying to relate records - any help appreciated.
                    philmodjunk

                    Did you use a portal to Contact_Join?

                    How are you entering values in the two ID fields defined in Contact_Join?

                    The first should auto-populate when you add a new record in the portal by typing in date or selecting an ID for ContactID 2 if "allow creation of records via this relationship" is enabled for contact_Join in the relationship from Contacts to contact_Join.

                    Typically, you'd then set up the ID 2 field with a drop down list of Contact ID's with a name field included as the second field so that you can select a contact by name.