Relationship question

[Mike Janus]

I'm working with Filemaker Pro 8 on a PC.

I have one file called Adults.fp7. Each adult has a unique ID as well name and address fields (fullname, address1, address2, citystatezip, and phone)

I have another file called Students.fp7. In this file, I have, among other fields, four fields called Parent1, Parent2, Parent3, Parent4 (in today's world, this family arrangement is not uncommon). In those fields, I enter the unique ID from the Adults database.

I want to then display from the Adults database the relevant name, address, , and phone information for each of the four parents.

I've tried setting up the relationships in both databases in various ways, but nothing seems to work. I can get the information from Parent1 to show up in the Student record if I use a simple relationship of Parent1 to Adult ID.

But as soon as I add a relationship of Parent2 to Adult ID, neither Parent1 nor Parent2 information will show up.

Can anyone suggest a solution?

Mike

[Fitch]

Using 4 fields as you have done, you need a separate relationship for each adult, and a separate set of fields on the layout; you cannot use a portal.

If you used a single field for parent IDs, one ID on each line -- this would have to be a text field -- you could then display related parent info with a single set of related fields in a single portal.

(Of course the "correct" way to do this is using a separate join table, where each record has two fields, a parent ID and a child ID.)

[Matthew F]

I have one file called Adults.fp7.... I have another file called Students.fp7.

In v.7+ you should put both student data and the adult data in two separate data tables within a single file. If for some reason you want to keep the data in separate files (as it was in older versions) you can but its a little more tedius.

As Fitch mentioned, it would probably be better to setup a multiple-to-multiple relationship. The reason is that each student can have multiple parents and each parent can have multiple students. To do this you would create a third ('join') table which basically just a listing of StudentIDs and ParentIDs. You then create a relationship from Students to Join (via StudentID) and another from Join to Adults (via AdultID). Finally you can put the Adult data on the Student layout in the form of a portal. There's no need to limit this to a maximum of 4 parents - just set as many portal rows as you like.

[LaRetta]

Using 4 fields as you have done, you need a separate relationship for each adult, and a separate set of fields on the layout; you cannot use a portal.

Well actually you can use a portal. In the Students table, create a calculation called cFamily (result is text) with:

Parent1 & ¶ & Parent2 & ¶ & Parent3 & ¶ & Parent4

... and join Adults::ParentID = Students::cFamily. Then one single portal on the Student layout will show all parents. You might want an additional field (in Adults) showing the relationship of the parent to the child, ie, whether father, mother, caregiver, uncle etc.

But ... sure as anything ... you will have ONE instance where there are five parents. And what do you do then? So I would also suggest a join table instead so you have unlimited options in the number of parents AND the number of children. Or another option is, instead of multiple fields holding the parent IDs, simply use a multiline in Students (since that's what you end up creating with the calculation anyway). Either way, all adults can reside in the same portal and you are not limited to multiple fields or hampered by multiple relationships in your graph.

LaRetta :wink2:

[Mike Janus]

I want to thank the recipients to my original question. I posted it on the 9th and then went out of town on the 12th. I hadn't had a chance to get back to the problem until now.

I'll be taking a look at all the possibilities, but I think I need to brush up on Join. I'll check out the FM help file--unless someone has a better suggestion.

Sorry for my silence until now. I do appreciate the time people take to help with problems.

Mike