6 Replies Latest reply on Jan 13, 2015 3:29 PM by mkmcgeevt

    Table complexity due to self-referencing - any hints?

    mkmcgeevt

      Title

      Table complexity due to self-referencing - any hints?

      Post

      Hi - I have a small project that is rather unusual (I think) and less than a year of experience with FMPro.  I have made progress over the last month in nailing down a useful field / table /relationship structure.  I am working with one main table and a half dozen more that basically serve only as complex value lists.  

      The main table is structured around WordFamily.  Each WordFamily has it's own UniqueID. From there I do something like a family tree (think of it as a word tree).  Additional fields are inserted based on known associations (like son, daughter, sister, brother in a family tree), which establish specific connections to other WordFamily-s through the same UniqueIDs that make up the complete set of records in the table.  

      My issue is that each time I create a layout field and try to get it to display the WordFamily for the UniqueID in the field (not the UniqueID itself), I can only get it to display the WordFamily associated with its own UniqueID.  

      If anyone can grasp the issue I am having, feel free to respond with hints or resources.  If you are inclined to understand the question but cannot, feel free to ask clarifying questions.  Either would be much appreciated.  

        • 1. Re: Table complexity due to self-referencing - any hints?
          philmodjunk

          You'd need to describe both your relationships and exactly how you set up your value lists.

          I can only get it to display the WordFamily associated with its own UniqueID.

          What do you need it to display? If this is a "use values from field" value list and you want it to display all values from the table, this might be as simple as clearing the "include only related values" option.

          That's the most detailed suggestion I can make without a much better understanding of your current layout, relationship and value list design.

          I can offer some general resources on value lists and alternative value selection methods that you can review for possible ideas to include in your database:

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Table complexity due to self-referencing - any hints?
            mkmcgeevt

            It's not a value list issue, though I can see how it might read like that. 

            An example is best.  The numbers in parentheses are UniqueIDs.

            Godzilla (1) and Catwoman (2) are parents of Batman (3).  Batman and Fiona (4) are parents of Dash (5). 

            Each superhero has his/her own record, but also occurs in the fields of his/her relatives. 

            In Godzilla's record (1), Batman is listed in a child field by his UniqueID (3).  

            When I create a family tree layout, I want the superhero of interest's name to appear, but also show related superhero's names.  So far I am running into the problem that I can only get the superhero of interest's name to appear when I expect to display the superhero name from a child field (for example).  I tried applying a few different self-relationship scenarios but nothing works yet.  

            I have been able to get a portal to show records by superhero name, but only when they are based on a criteria other than UniqueID.

            • 3. Re: Table complexity due to self-referencing - any hints?
              philmodjunk

              I'm not sure I follow all that, but this sounds like a classic many to many self join with a need for the many to many relationship to be bidirectional.

              Superheroes----<SuperHeroRelationship>----Superheroes|Related

              Superheroes::__pkSuperHeroID = SuperHeroRelationship::_fkSuperHeroID
              SuperHeroes|Related::__pkSuperHeroID = SuperHerRelationship::_fkRelatedSuperHeroID

              SuperHeroes and SuperHeroes|Related are two occurrences of the same data source table.

              Godzilla's (_pk = 1 ) Relationship to Batman (pk = 3 ) would be shown by creating a record in SuperHeroRelationship where

              _fkSuperHeroID is 1 and _fkRelatedSuperHero is 3. Another field in this join table would show the relationship as "Father". To "backlink" BatMan to Godzilla, you would create another record where the two _fk field values are swapped and the relationship field Reads "Son". It's possible to set up a script that generates the "back link" record each time you create a new record in the join table.

              • 4. Re: Table complexity due to self-referencing - any hints?
                mkmcgeevt

                Phil, I hope you know what you are talking about -- because I don't!   

                With some concentrated study, and trial and error, I hope to soon though.  

                And finally, you were very kind to not point out my obvious error in referring to Godzilla as a superhero surprise 

                • 5. Re: Table complexity due to self-referencing - any hints?
                  philmodjunk

                  This may help:

                  For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                  • 6. Re: Table complexity due to self-referencing - any hints?
                    mkmcgeevt

                    Phil -- it worked, I did it!  It took some reading, trial and error, and a little team troubleshooting with my husband, but now it really works.  Thank you for your answer, and hat's off to a great product and community.  

                    I only had one problem and that was that I had misinterpreted the instructions to mean that I needed a different key to join the tables with.  So I went on a wild goose chase adding another key to the main table and trying to get the join and relationship tables to work together. 

                    Now on to the next hurdle...