5 Replies Latest reply on Aug 2, 2016 4:00 PM by MartinBishop

    Sibling Record Help/Method

    MartinBishop

      I am looking for an idea or method to link two or more tabled together automatically... For example

       

      ProjectTable:

      Project 10 - Sibling

      Project 25 - Sibling

      Project XX - Sibling

      -These are more like Peers, since they're not related by a parent record.

       

      Using a join table I can add Project 10 to Project 25 and this works, but I would like to automatically have the vise versa happen. So that when I go to Project 10, Project 25 is also already included. Would just using a script be the best way to accomplish this? Manually add the relationship, then on commit create a new record in join table and flip the values?

        • 1. Re: Sibling Record Help/Method
          coherentkris

          You could put a text field in the projects table that holds a return delimited list of project IDs that any given project is associated with.

          Then you have something to relate projects to projects in a many to many relationship.

          However that may not be the best way to solve your problem because i'll bet that projects is not the only entity in the solution.

          it is definitely possible that a join table would fill your needs but we don't know enough of what your trying to accomplish.

           

          If you in fact have two tables project 10 and project 25 then you should stop and rethink your design.

          • 2. Re: Sibling Record Help/Method
            philmodjunk

            You can also use a script to create a second join table with the match field values swapped in order to make the link between project records bidirectional.

             

            So if you create this join table record:

            Join::_fkProjectA = 325

            Join::_fkProjectB = 654

             

            Your script would generate a second record that looks like this:

            oin::_fkProjectA = 654

            Join::_fkProjectB = 325

             

            And now your join table links project records in both directions

            • 3. Re: Sibling Record Help/Method
              philipHPG

              Another option is to use a ProjectGroup table that has two fields: groupID and fkProject

               

              Record 1:

              groupID = 1

              fkProject = Project 10 ID

               

              Record 2:

              groupID = 1

              fkProject = Project 25 ID

               

              Record 3:

              groupID = 1

              fkProject = Project XX ID

               

              These records can be added or deleted independently, without reference to another project.

               

               

              Then you find a project's siblings through a Project_siblings table occurrence

               

              ProjectGroup::groupID = ProjectGroup_siblings::groupID

              (optionally: ProjectGroup::fkProject ≠ ProjectGroup_siblings::fkProject)

               

              and a Project_siblings TO where

               

              ProjectGroup_siblings::fkProject = Project_siblings::id

              • 4. Re: Sibling Record Help/Method
                philipHPG

                Guess I should apply the KISS principle... and ask a few more questions...

                 

                Will a project only ever be connected to one group of siblings (ie. Project 10, Project 25 and Project XX group together and each does not group with any other projects)? Or is it ever the case that a project might be a part of multiple groupings (ie. Project 10 groups with Project 25 and Project XX, and also with Project 33 and Project 42, but Project 25 does not group with Project 33 or Project 42). If there is only one set of siblings for each project you could add a SiblingSetID field to the ProjectTable and connect to other Projects with the same SiblingSetID. Simple, but limited flexibility.

                 

                How large are the sibling sets? Ie. On average how many projects are grouped together? A large sibling set requires a significant number of join records, however not so many ProjectGroup records.

                • 5. Re: Sibling Record Help/Method
                  MartinBishop

                  Thanks for this info! I ended up using a combination of your suggestions.

                  Initially my goal was for only 1 set of groups, but I added the ability for multiple groups in case the future deems necessary.