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.
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.
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.
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?
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.
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]
You can even use conditional formatting to highlight the portal row fields of the selected row.