7 Replies Latest reply on Mar 16, 2012 9:53 AM by KylePutzier

    Finding like related records.

    KylePutzier

      I have a table of people. The people in that table have many related friends in a different table. How can I tell if a particular person has the same friend as another person? Each persons friend(s) would be a different record in the friends table.

      It sounds like I'm making up the people and friends scenario, but believe it or not, that is the actual scenario. I need to figure out (and show) who has common friends. I'm going to assume that the friends table has a somewhat reliable identifier like a phone number or something. I don't have that actual data yet.

       

      BTW, I could decide on a good name for this topic. What do you think it should have been?

       

      Kyle

        • 1. Re: Finding like related records.
          comment

          KylePutzier wrote:

           

          How can I tell if a particular person has the same friend as another person?

          ...

          I need to figure out (and show) who has common friends.

           

          These two are not exactly the same thing. are you going to select two people and ask if they have common friends? Or do you want to see all other friends of the current person's friends?

          • 2. Re: Finding like related records.
            KylePutzier

            I want to see the other people that have the same friend(s) as the person currently being viewed.

             

            Kyle

            • 3. Re: Finding like related records.
              comment

              Assuming the following relationships:

               

              People -< PeopleFriends >- Friends

               

              you could extend the chain to:

               

              People -< PeopleFriends >- Friends -< PeopleFriends 2 >- People 2

               

               

              A portal to People 2 placed on a layout of People will show all people who have at least one friend common with the current person.

               

              To exclude the current person from appearing in the portal, place a global field with the current person's ID in the PeopleFriends join table and populate it by a script triggered OnRecordLoad, or use portal filtering (version 11).

              • 4. Re: Finding like related records.
                KylePutzier

                I will have the problem of the two different people records will not be related to the same friend record. They will be related to their own friend record that happens to be the same real life friend.

                 

                Joe Smith (person)

                    Mary Johnson 714-555-1212 (friend)

                    Mr. Jim Green 562-123-4567 (friend)

                 

                Amy Peterson (person)

                    Mrs. Mary Johnson 714-555-1212 (friend)

                    Betty Wilkes 310-890-1111 (friend)

                    Mark Adlund 818-123-8521 (friend)

                 

                If I were viewing Joe Smith's record, I would like to see that Amy Peterson has the same friend (Mary Johnson 714-555-1212). Note that Mary Johnson has two different records in the same table. I will have to find a way of generating a common identifier. I have used the phone number in this case.

                • 5. Re: Finding like related records.
                  mbraendle

                  Friends are also just people, so I followed this Ansatz, using three tables:

                   

                  people

                  has_relationship_with

                  relationship_types

                   

                   

                  People -< has_relationship_with >- TO 2 of people table

                                            |

                                   relationship_types

                   

                   

                  <has relationship with> can have values such as "is friend of", "is father of", and so on, which it takes from the relationship_types table.

                   

                  Or in a RDF related manner, we put the subject (source id), the predicate (the relationship type) and the object (target id) as foreign keys into the the join table <has relationship with>.

                   

                  Two relationship records for "is friend of" in the <has relationship with> table must be created, since usually friendship is mutual, so A is friend of B and B is friend of A. Also for hierarchical relationships, two records must be created, e.g. A is father of B and B is daughter of A.

                   

                  For finding double-relationships, I created a selfjoin between <has relationship with> and a second TO of the <has relationship with> table, using e.g. the foreign source id key as self-join criterion:

                   

                  People -<has relationship with TO1>- <has relationship with TO2> - TO3 of people

                  In <has relationship with TO1> I create a calculation field that counts the associated records in <has relationship with TO2>.

                   

                  I create then a layout based on <has relationship with TO1> and put <has relationship with TO2> as portal on it as well, and do the search like this for yielding all multiple relationships:

                   

                  relationship type (TO1) = "friend of"

                  relationship type (in portal, TO2) = "friend of"

                  relationship count > 1

                   

                  With GTRR you can then obtain the associated persons.

                   

                   

                  When would like to focus on a single, given person:

                   

                  relationship type (TO1) = "friend of"

                  target id (TO1) = given person

                  relationship type (in portal, TO2) = "friend of"

                  target id (in portal, target id field of TO3) = given person

                  relationship count > 1

                   

                   

                  Hierarchical networks can also be searched in a similar way:

                   

                  relationship type (TO1) = "PhD student"

                  relationship type (in portal, TO2) = "Doctoral advisor"

                   

                  finds all persons who had made a PhD study and got professors later.

                  • 6. Re: Finding like related records.
                    KylePutzier

                    I can find the matching friends now. I just need to get rid of the matching "self" person. I cant think of a way at the moment. Please see attached example file.

                    • 7. Re: Finding like related records.
                      KylePutzier

                      Portal filtering allowed me to exclude the "self" person.