What determines the "many" in a one to many...?

[Hoib]

OK, I'm working on my food pantry project and I have a general question about FM 11 Pro Adv and the relationship diagram. I've got two tables called Members and Households. Table-Members holds the names, and DOBs of individuals. It also has a primary key, autoentered serial for each person. In table-Members I've also established a Household_id field which is not a key but a numeric text field denoting what Household the individual belongs in. I do it this way because sometimes people (Members) move in and out of households in our "business". In table-Household I have the household's address fields and other fields I need as attributes for households (ex: Does this household qualify for governement distributions, etc.) So in Households, I'm trying to set a key to look at Members and, when I'm on the Household based layout, come up with the Household's composition, ie which people are in Household # 581 so I can count them, and do whatever.

Now, in the relationship diagram, when I try to link Members to Households, I always get the "crowsfoot" on the Household side. That's not correct - I need the crowsfoot on the Members side of the link because there are "many" Members contained in "one" Household - right? It doesn't matter which table I start the drag in. In fact, any time I choose any field in Household and drag a little way out, I always get a crowsfoot which tells me I can never have one to many where Household is a "one".

I'm now thinking there's something in the overall table configuration that makes this automatic crowsfoot happen. Or I'm misunderstanding the true meaning of the crowsfoot. I think the crowsfoot goes toward the "many" in a one-to-many. At least that's what I've read. Maybe I have to do this with a "Find" and use a found set to show Members of Household?

What am I missing?

[comment]

Now, in the relationship diagram, when I try to link Members to Households, I always get the "crowsfoot" on the Household side.

Filemaker doesn't know about "one" or "many". That's all at the conceptual level. At the implementation level, the graph will show crow's foot unless the matchfield is validated as unique or set to auto-enter a serial number. The HouseholdID field in the Households table should be auto-entering a serial number, same as the MemberID field in the Members table - but not the HouseholdID field in the Members table.

[Hoib]

A major breakthrough! @Comment - once again you have brought bright light in to a very dark room. I can only thank you.

After tinkering with this for a while, I think I got traction. Here's another thing I have now noticed and learned. Click/drawing a link line in the rel diagram has a quite different connotation than pulling up the Options box on a field and auto-entering the field based on a Calculated result. The difference is subtle but gets the crow's foot positioned. Can you briefly tell me what exactly is the difference between drawing the line and declaring the Calc result?

Observation: There isn't a way on the rel diagram to indicate that a field in a TO box is deriving its "relationship" (auto-entered value) from the Calculated result out of Options. Being unable to see this is what's been confusing to me. Now that I've discovered it, let me see if I can get the Household_layout to show me all the Members of Household #581...

[comment]

Click/drawing a link line in the rel diagram has a quite different connotation than pulling up the Options box on a field and auto-entering the field based on a Calculated result. The difference is subtle

I am afraid you have some re-learning to do here, because the difference between these two is not subtle at all. Auto-enter has (almost) nothing to do with relationships - it's all about automatically populating the field with a value. Linking two tables in the RG creates a relationship between records, based on matching existing values in the two matchfields.

let me see if I can get the Household_layout to show me all the Members of Household #581...

A portal to Members would be good for that.

[Hoib]

OK, some more progress. Remember I'm still very much a learner here...

A portal to members. OK, I built that but I've run into a number of problems. I made something very simple to start out with. I wanted to select the visitor (Member) with a last_name drop down. In the drop down value-list set up I set the name to that Members table and in the left hand column set to member_id with second column to also show second field of member_last_name and display only second field. Set an arrow to target. Back on the portal layout just to the right, I want to show the member_first_name of the person selected. It does this once and will not refill the first_name if I choose someone else in the drop down. Further to the right, I want to see Household::Household_id of that member I selected. Even though I have the field set to Household::Household_id I get a blank field. Strangely, I set another field a little farther to the right on the layout to show the Member:member_id. Yet, here I get the person's last name again. I've check my RG and I believe I've got the links imposed properly.

Questions: In the portal am I allowed to show only results of one table? I think I should be able to show fields from several tables -IF- I have the relationships set properly. Just want to be sure.

In order to get the selectee's first name to change, is that going to be a script?

II was thinking the relationship Members-to/from-Household would pull the data through. Isn't that the way it's supposed to work?

And, then finally, I'm totally without a clue as to why the member_last_name appears rather than the member_id when it's clearly being called for. What am I looking for there?

Thanks good people.

H

[comment]

Questions: In the portal am I allowed to show only results of one table? I think I should be able to show fields from several tables

You can show fields from other related tables in a portal, but each row of a portal will always represent one related record from the portal's table occurrence. If there are fields from other tables, they will show data from the first related record of that table, as seen from the record in the portal row.

I am afraid I got a bit lost with your other questions. In general, once you have selected the parent for a child (i.e. the HouseholdID for a member), you can place fields from the parent table on the layout of the child. Make them non-enterable to prevent accidental modification.

[Hoib]

OK, thank you fo sticking with me. I realized that the questions were not very clear after I got up this morning.

First of all, the question about the first name not showing up... After reflection, that's a pretty dumb question because I can make (and just made) a new field with a calculated result concatenating last_name, " ", + first_name and use the new full_name_reversed for the drop down. I don't need to worry about a seperate first_name field on the portal layout.

I probably need to start a new thread to cover the rest because really, the OP is resolved. Agree?

H

[Lee Smith]

I can make (and just made) a new field with a calculated result concatenating last_name, " ", + first_name and use the new full_name_reversed for the drop down.

concatenating last_name, " ", + first_name Should be written as last_name & ", " & first_name

[Hoib]

Yep - I was just writting it quickly to get ready to head out this morning. But thanks for the correction nonetheless.

I'm going to start a new thread for my other "issues"... (And, boy do I have issues... :-) )

Hoib