1 2 Previous Next 15 Replies Latest reply on Mar 21, 2011 8:32 AM by philmodjunk

    Creating a Relationship Between 2 IDs on 2 Different tables Portals

    KAmsinger

      Title

      Creating a Relationship Between 2 IDs on 2 Different tables Portals

      Post

      I have a database that has several tables in it. Using FMP Advanced 10

      1 table is called PERSONS which has a ID assigned to each record per persons profile.

      I have a 2nd table called Groups which I can add a group from a valued list to assign to a person profile in the PERSONS table.

      These relationships are shown in 2 places. In the Groups table there is a portal showing all the Persons assigned to that 1 particular Group, and I have it setup where you can click a button next to each persons name it takes you to that persons profile in the PERSONS table. And in the PERSONS Table I have a portal showing all the groups assigned to that person and you can click on a button next to the groups name and it takes you to that particular group on the Groups Table.

      I setup the above Group system using the example file 06a_FTS_Meetings_Solutions.fp7

      I want to create a new type of groups that has a different but similar functionality. Here is a new type of group I want to setup lets call it Client Date List.

      1. I want it where if I check a box in the PERSONS table on 1 particular persons profile it creates a new group in the Groups table and creates a relationship between the new Group ID and that particular Persons ID that I checked the box on their profile, and I want it where the group is automatically named with that persons name.

      So for example if I was in John Doe's profile, in the PERSON table, and I checked a box on his profile it would automatically go into the Group table create a new record or Group, named John Doe and adds John Doe's name to the Value List, and have a relationship setup between John Doe's PERSONS ID (_kp_Person) and the new Group ID (_kp_GroupID).

      2. Then in the Groups table I want a portal that I can add people to John Doe's group. 

      3. in John Doe's Profile in the PERSONS table I want a portal that will show all the people assigned to his group and his group only.

      4. And then another part of this is in the PERSONS table,  I want a portal showing the Group assigned to that person. So if I wanted to Add Jane Smith to John Doe's group, on Jane Smiths profile in the PERSONS table there would be a separate portal that shows John Doe as a group assigned to Jane Smith. 

      Side Note: both John Doe and Jane Smith are on the same table PERSONS and have their own unique PERSONS ID for each of them. 

      I already have it setup to show in the GROUPS Table the list of people assigned to that group. Also, have it setup that in the PERSONS table the List of Groups assigned to that person. 

      What I need setup is the check box to create the new group in the Group Table and link the PERSONS ID and the new GROUP ID together, name the group the Persons Name (John Doe) and add their name to the valued list, and have the portal on the John Doe's profile in the PERSONS Table to show all the people (Jane Smith) assigned to his group and his group only. And when someone is added to his group it adds their name to the portal in the Group Table for that particular group (John Doe) and adds their name to the portal in John Doe's profile only in the PERSONS Table.

      If you need an example file I would be willing to up load to gain assistance with this, I am completely stuck. 

        • 1. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
          philmodjunk

          I'm not totally clear on how you've set up your relationships here. Can one person be a member of more than one group? Or is it strictly one group per person?

          If the same person can join multiple groups, you'd need a join table like this:

          Persons----<Group_Membership>-----Groups

          Persons::PersonID = Group_Membership::PersonID
          Groups::GroupID = Group_Membership::GroupID

          If it's one group to a person record, you can just use:

          Persons>-----Groups   

          Persons::GroupID = Groups::GroupID

          Assuming only one group per person, your script would look like this:

          #Script should be perfomed from layout based on Persons
          Freeze window
          Set variable [$PersonName ; Value:: Persons::Name ]
          Go To Layout [Groups]
          New Record/Request
          Set Field [Groups::GroupName ; $PersonName ]
          Set variable [$GroupID ; Value:  Groups::GroupID ]
          Go To Layout [original layout]
          Set Field [ Persons::GroupID ; $GroupID ]

          • 2. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
            KAmsinger

            This worked perfectly.

            But now how do I make a portal on a persons profile in the PERSONS Table, that I created a group from their PersonID, to only show the people who have been added to their group? So if I created a Group with the script you provided above for 1 person creates 1 group, so for example I created a group with John Doe Person ID now anyone who I add to his group I want those people's names to show up in a portal on John Doe's profile page in the PERSONS table. Once I have that setup I have created the new system of groups that I need.

            • 3. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
              brianc_1

              You will need to create 2 more table occurrences in the relational graph:

              1) Duplicate the Persons table occurrence and label it: Persons_ByGroup

              2) Duplicate the Group_Membership table occurrence and label it: Group_Membership_ByGroup

              Then link them up:

              Persons----<Group_Membership>---<Group_Membership_ByGroupID>----Persons_ByGroupID

              Persons::PersonID = Group_Membership::PersonID
              Group_Membership::GroupID = Group_Membership_ByGroupID::GroupID
              Group_Membership_ByGroupID::PersonID = Persons_ByGroupID::PersonID

              Create a portal on your layout (Table:Persons) and use the relationship "Group_Membership_ByGroupID"

              Then add the necessary fields to that portal from the Persons_ByGroupID relationship to display the names of the individuals that belong to the group(s).

              • 4. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                philmodjunk

                What do you want to see if Jim Smith is added to John Doe's group and you go to Jim Smith's record? Would you want to see the same list, no list, or...?

                If the same list will do, add a table occurrence of persons so that you have:

                Persons>----Group-----<Persons2

                Group::GroupID = Persons2::GroupID

                Make this new table occurrence by clicking on Persons in Manage | Database | Relationships and then clicking the button with two green plus signs.

                Now you can make a portal to Persons2 on your Persons layout to see all person records that are members of the same group as the current person record.

                • 5. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                  KAmsinger

                  Hi. So I have tried both PhilModJunk and Brianc suggestions and I think I was unclear as to the last part that I was needing because I have tried both of your suggestions and neither has worked with what I am wanting to setup.

                  Let me see if I can explain this better...

                  What I have already setup is I can create a group, which will be created on the table called Groups, from a person's record ID that is on the PERSON Table.  I can add people to this person's group. for example John Doe is a person who has a record on the person's table with his own unique PERSON ID and I can create a new Group by clicking a button on his record (on the PERSON's Table) that has a script assigned to it (the one PhilModJunk provided to me in his first post) that takes his PERSON ID and creates a new group on the Group Table and names the new group John Doe.

                  I then have it setup I can go into anyone's record in the Person's table and assign them to John Doe's group. I have a portal already setup showing on each person's record, on the PERSON's Table, which groups they are assigned to. Then in the Group Table I have a portal that shows a list of People who are assigned to each group. What I want is have that same list of people who are assigned to a group (the same list that show's in the group table), for example John Doe's Group, to show on a portal on John Doe's record only, in the Person Table, a list of people assigned to his group. 

                  So if John Doe (Who is on the person's table) I create a group with the script that PhilModJunk provided to me in his 1st post. It takes John Doe's PersonsID and creates a new group with it on the Group Table. Then I go into Jane Smith's profile, which is on the Person's Table, click a button and it adds her to John Doe's group. So now in the Group Table under John Doe's group there is a portal that lists everyone assigned to John Doe's group and now you see Jane Smith's name listed in that portal. 

                  Now what I want is that same list of people that are assigned to John Doe's group, that is on the Group Table, to show in a new portal on John Doe's record only in the Person's table. 

                  Does this help at all?

                  • 6. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                    philmodjunk

                    I originally asked: "Can a person be assigned to more than one group?" and then carefully noted that the script was based on the "one group per person" assumption. Now it appears that you can have a person be a member of more than one group:

                    I have a portal already setup showing on each person's record, on the PERSON's Table, which groups they are assigned to.

                    As I originally stated, that need requires a different set of relationships, tables and table occurrences. It would also need a different script than I originally posted.

                    Does this help at all?

                    It doesn't answer the question I asked: If you go to Jane Smith's record, what do you want to see in this portal? Empty or the same list as you see in Jon Doe's?

                    Please confirm whether or not you can have a single person be part of more than one group. If it does it raises an entirely new question: Can one person be the leader of more than one group? IF so, what do you need in this portal as they now might have more than one list of members?

                    • 7. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                      KAmsinger

                      QUESTION: I originally asked: "Can a person be assigned to more than one group?" and then carefully noted that the script was based on the "one group per person" assumption. Now it appears that you can have a person be a member of more than one group:

                      ANSWER: Yes, 1 person can be assigned to more than one group.

                      I have a portal already setup showing on each person's record, on the PERSON's Table, which groups they are assigned to.

                      As I originally stated, that need requires a different set of relationships, tables and table occurrences. It would also need a different script than I originally posted.

                      Does this help at all?

                      QUESTION: It doesn't answer the question I asked: If you go to Jane Smith's record, what do you want to see in this portal? Empty or the same list as you see in Jon Doe's?

                      ANSWER: If you go to Jane Smiths' record, in the PERSON Table, in a portal I want to see the list groups Jane Smith is assigned to. 

                      ANSWER: If I go into John Doe's record, in the PERSON Table, in a different portal I want to see the people who are assigned to his group only.

                      QUESTION: Please confirm whether or not you can have a single person be part of more than one group.

                      ANSWER: Yes, 1 person can be assigned to more than 1 group.

                      QUESTION: If it does it raises an entirely new question: Can one person be the leader of more than one group? IF so, what do you need in this portal as they now might have more than one list of members?

                      ANSWER: 1 person will only be a leader to 1 group and 1 group only. So if I create a group off of John Doe's record only 1 group will be created, I wouldn't have the need or want to create a 2nd group that he would be the leader of. 

                      Does this help? Sorry for the confusion... I didn't realize there was a difference sorry, that was my bad!!!

                      • 8. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                        philmodjunk

                        Take a fresh look at my very first post. With one person being allowed to be a member of more than one group you have a many to may relationship and this requires a new table to function as a "join" table for listing group membership:

                        Persons----<Group_Membership>-----Groups

                        Persons::PersonID = Group_Membership::PersonID
                        Groups::GroupID = Group_Membership::GroupID

                        Note that group ID is no longer stored in the Person's table as they could be a member of several groups.

                        #Script should be perfomed from layout based on Persons
                        Freeze window
                        Set variable [$PersonName ; Value:: Persons::Name ]
                        Set variable [$PersonID ; Persons::PersonID]
                        Go To Layout [Groups]
                        New Record/Request
                        Set Field [Groups::GroupName ; $PersonName ]
                        Set Field [Groups::LeaderID ; $PersonID]
                        Set variable [$GroupID ; Value:  Groups::GroupID ]
                        Go To Layout [Group_Membership]
                        new Record/Request
                        Set Field [Group_Membership::GroupID ; $GroupID]
                        Set Field [Group_Membership::PersonID ; $PersonID]
                        Go To Layout [original layout]

                        To list the groups to which a person is a member, you can put a portal to Group_membership on the persons layout, add fields to the portal from groups and use a drop down list or pop up menu to select a GroupID in the Group_membership::GroupID field to assign a person to a new group.

                        Here's a demo file you might want to download and examine. It matches contracts to companies, but if you renamed them People and Groups, you'd have the same basic set up for what we have so far here.

                        To list a group leader's group membership, we'll need to add to our relationship graph:

                        Persons-----GroupLeader-----<GroupMembers

                        GroupLeader is a new table occurrence of groups. GroupMembers is a new table occurrence of Persons

                        Persons::PersonID = GroupLeader::LeaderID
                        GroupLeader::PersonID = GroupMembers::PersonID

                        Now a portal to GroupMembers will give you your membership list.

                        • 10. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                          KAmsinger

                          Hi Thx I will try this. However, I don't see the demo file? 

                          • 12. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                            KAmsinger

                            Quick question the table you have listed above called Group_Membership which table occurrence is that created from? 

                            • 13. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                              philmodjunk

                              It's a new table with just two fields: GroupID and PersonID. You can add fields if needed to this table to document anything specific to one person being a member of one group. Compare it to the Contracts_Companies join table in the demo file.

                              • 14. Re: Creating a Relationship Between 2 IDs on 2 Different tables Portals
                                KAmsinger

                                Hi. So I got the whole thing setup and it works great. However, I wanted to ask.... When I go into the groups table and click the built in "Delete Record" button to delete a group it ends up deleting the group and the group leader's record in the Person Table. Is there something I need t alter in the script that creates a new group so it won't do this if I ever decided to delete a group or is there a new script I need to setup to delete a group but have it delete only the group and not the people's records that was the leader of the group in the Person table?

                                1 2 Previous Next