6 Replies Latest reply on Jan 29, 2010 1:43 PM by philmodjunk

    Not understanding the theory of this...

    whardy7

      Title

      Not understanding the theory of this...

      Post

      I am not understanding something about why one way works and another doesn't concerning two tables.

      I will simplify - I have 2 tables with fields; Household (HHID, Name) and FamilyMembers (FMID, HHID, gFMID, Name, Address), related by HHID. I also have a TO called FamilyMembers2.

       

      My layout is based on Household and lets me enter households. It also contains a portal based on FamilyMembers that lets me enter family members (only Name) of that household. This all works great.

      I need to click on a family member in the portal in order to edit the remaining FamilyMembers fields below it that contain the address fields, etc. I can do this as long as I populate a global variable gFMID in FamilyMembers with FMID using Set Field. It works great.

      So, why doesn't it work if I simply relate FamilyMembers2 (TO of FamilyMembers) to FamilyMembers through FMID? It seems this would also keep FamilyMembers2 on the current record in FamilyMembers.

      My end goal is the the simplest/cleanest way to click on a particular FamilyMember Name in the portal and edit it's remaining fields.

       

      Thanks for any clarifications. I can't complain because it works great, but it would help me to understand why I can't do it with a simple relationship.

       

      whardy7 

        • 1. Re: Not understanding the theory of this...
          philmodjunk
            

          Let me see if I'm interpreting your design correctly.

           

          Your layout is based on the Household Table Occurrence and your portal is based on FamilyMembers correct?

           

          The relationship is Household::HHID = FamilyMembers::HHID

           

          You want to define an additional relationship:

           

          FamilyMembers2::FMID = FamilyMembers::FMID

           

          Nothing wrong with that, but neigther your layout nor your portal refer to the FamilyMembers2 table occurrence and that prevents this from having any effect on how your current layout functions.

           

          Each TO has it's own current: Record, Sort Order and Found set. This will be different for each TO even if they have the same source table.

          • 2. Re: Not understanding the theory of this...
            whardy7
              

            Phil,

            I should have said the fields below the portal were from FamilyMembers2, not FamilyMembers. Sorry. The editable fields below my FamilyMembers portal refer to FamilyMembers2 TO. With the relationship way of doing it, they do not update as I change records in the portal. With the global Set Field way, they do (by using Set Field on OnObjectExit.)

             

            It sounds like you are saying my relationship should work. I must be doing something else wrong. 

            • 3. Re: Not understanding the theory of this...
              philmodjunk
                

              I hadn't considered that possibility. I don't see how that option will work either and here's why...

               

              Context is everything here. You have to consider the TO names specified for your layout and your portal as the key points of reference through which filemaker determines which records match the current record in your layout.

               

              Your layout is based on Household. When you place fields from FamilyMembers2 on this layout, you are telling filemaker, "bring up the record or records from FamilyMembers2 related to the current record in Household." Since there is no direct link between FamilyMembers2 and HouseHold, Filemaker traces the reference from Household to FamilyMembers to FamilyMembers2. This means that the fields from FamilyMembers2 on your layout will come from all records in the FamilyMembers2 source table that match the current record by HHID--not FMID because that's the link between HouseHold and FamilyMembers.

               

              Unlike fields in your portal, the familymember2 fields on your layout can display data from only one record. Thus, filemaker displays data from the "first" matching record in the datasource table. If your relationship does not specify a sort order, this will be the first related record to be created. If you've specified a sort order for your relationship, it will be whichever matching record is first in the specified sort order.

              • 4. Re: Not understanding the theory of this...
                whardy7
                  

                OK. So maybe I am missing a big piece of FM theory here.

                 

                I have this:

                TO:Households   Link:HHID   TO:FamilyMembers   Link:FMID   TO:FamilyMembers2

                 

                Households is on HHID:100 which causes FamilyMembers to be a found set of 5 records (family members) with HHID:100. When I click on one of those FamilyMembers (in a FamilyMembers portal), it has FMID:3. So, that does not make FamilyMembers2 "park" on that particular record? I thought FamilyMembers2 was only effected by FamilyMembers, not by Households. I think that must be the part I am having a hard time understanding. I am coming from years of FoxPro, which is hard to get out of my system.

                 

                I guess my next question would be, what is the best way to accomplish being able to select a FamilyMember from the portal and be able to edit it within the same layout? 

                • 5. Re: Not understanding the theory of this...
                  comment_1
                    

                  whardy7 wrote:
                  what is the best way to accomplish being able to select a FamilyMember from the portal and be able to edit it within the same layout? 

                  Define two relationships:

                   

                  Households::HHID = FamilyMembers::HHID

                   

                  Households::gFMID = FamilyMembers 2::FMID

                   

                  Base the portal on the first relationship. Selecting a record is done by setting the gFMID field to the FMID of the selected record. For editing, place fields from FamilyMembers 2 on the layout of Households.

                   

                   

                  Note that the global field is in the Households table.

                  • 6. Re: Not understanding the theory of this...
                    philmodjunk
                      

                    So, that does not make FamilyMembers2 "park" on that particular record? No, not within the Household context established in layout setup.

                     

                    Sometimes, when trying to anaylze a filemaker relationships graph, I picture the table occurrence boxes as lily pads in a pond and the only way I can "get to" data on another "pad" is to jump along a relationship line linking the two. I can get to more distant "pads" only by jumping from pad to intervening pad. In each jump, the relationship linking the two pads and the values in the key field(s) of the pad I am on will determine what records I "land on" in the next jump.

                     

                    In your case, the layout's table occurrence, HouseHold, is your starting pad and to get to Familymembers2 you have to first jump to FamilyMembers. The first matching record specified in the relationship to family members is the record you land on. The self Join to FamilyMembers2 then limits you to landing on the same exact record you have already landed on in FamilyMembers. As you have discovered, the current portal record has absolutely no effect on which record you end up seeing in the FamilyMembers2 fields in the bottom of your report.

                     

                    Hope you can get around the sillyness of the above image to understand how the process works.

                     

                    TO do what you want, you'll need a script that responds to your mouse click on the portal row.

                     

                    Add a text field (can be a global text field or a local text field) to HouseHold called SelectedFMID.

                    Change your relationship to FamilyMembers2 to:

                    HouseHold::SelectedFMID = FamilyMembers2::FMID

                     

                    Now put a button on your portal row or define the fields themselves as a button to do this:

                    Set Field[HouseHold::SelectedFMID; FamilyMembers::FMID]

                    Refresh Window

                     

                    You can even use conditional formatting to highlight the portal row fields of the selected row.