4 Replies Latest reply on Nov 15, 2013 12:12 AM by CharlieColes

    Create active list of links to other records

    CharlieColes

      Title

      Create active list of links to other records

      Post

           I have a database of political groups. Although I have a number of tables and layouts in the database, the vast majority of interaction by users will be done using the 'Groups' layout. This uses a tab control and works like a 'home page' for that group, displaying all relevant information about that particular group and including a number of portals linking data from other tables (e.g. logos, members, publicity releases, etc).

           There is one thing I just cannot get right, despite trying for several hours a day over the past few weeks and reading every bit of advice I can on forums and the FM Missing Manual. It's crazy, because it seems like it should be a really simple thing. I really hope somebody can help - it will need to be in plain English, and preferably a step-by-step guide!

           All I want to do for each group is to show which other groups in the file it is linked with. This should be mutually reflected, so once a link between GroupA and GroupB is entered on GroupA's record, that link will show up automatically on Group B's record. There isn't going to be any substantive information for this link - the link itself is enough. I then want to put a button in each portal row which will take the user straight to the 'home page' of that linked group.

           I had been trying to achieve this using portals, self-join relationships, 'match' join tables, and other tables, including creating several TOs on the relationships graph. I guess I just keep getting the relationships wrong.

           All help and advice very gratefully received.
            

        • 1. Re: Create active list of links to other records
          philmodjunk

               A "many to many self join" relationship should do the trick:

               Groups----<Group_links>-----Groups|Linked

               Where Groups and Groups|Linked are occurrences of the same table.

               Groups::__pkGroupID = Group_Link::_fkGroupID
               Groups|Linked::__pkGroupID = Group_Link::_fkLinkedGroupID

               But this creates a "one way" link from Groups to Groups|Linked. To make it a "two way" link, you create two records in Group_link. In the second record, the values in _fkGroupID and _fkLinkedGroupID would be swapped.

          • 2. Re: Create active list of links to other records
            CharlieColes

                 Thanks for your help.
                 I was with you up until the end bit (starting with 'But this creates...").  So the one-way link is ok.
                 You lost me when you mentioned creating records in Group_link. I have no data to put in there so how does this work? And then of course I don't understand how different records in the same table can swap pk and fk values...

                 Sorry - I'm not sure why I'm failing to grasp such basics having read so much about it all.

                  

                  

            • 3. Re: Create active list of links to other records
              philmodjunk
                   

                        You lost me when you mentioned creating records in Group_link. I have no data to put in there so how does this work?

                   You DO have data to put in there and it's shown in my previous post. The only data you have to enter into a record in Group_Links are the ID numbers of the two records linked to it.

                   

                        And then of course I don't understand how different records in the same table can swap pk and fk values...

                   Typically, you use a portal to the join table in order to link records via join table. In your case, you can put a portal to Group_Link on your groups layout, enable "allow creation..." in the relationship and format _fkLinkedGroupID with a value list of groups to select from in order to link the current record on your Group layout to another group. Fields from the Groups|Linked table occurrence can be included in the portal row to show info about that group once you have selected it from the drop down list.

                   If you have a record in Group_Link with these values:

                   _fkGroupID = 25
                   _fkLinkedGroupID = 55

                   Which links group 25 to group 55. Then a script can create a new record in Group_Link with these values:

                   _fkGroupID = 55
                   _fkLinkedGroupID = 25

                   Which links group 55 to group 25 to so that you now have links in "both directions".

              • 4. Re: Create active list of links to other records
                CharlieColes

                     I get it. Thank you very much.