getting text from single field in multiple records

[RossChapp]

HELP! Solving a problem for a co-worker. Been using FM for 5 years, but am apparently still a newbie 'cause I don't have a clue! (Never written a script, but I at least know what one is.) Joining the forum has taught me how small a percentage of FM I know and use.

I have a single table with lots of data on dorm residents. Each resident has one record.

Request: One of the layouts is a letter to the students on which I need to have FM display the resident's list of roommates with contact info.

Progress: I created a calculation (text) field called Roommate Info which lists the pertinent info (name, phone, etc.) from other fields in the table.

Next: Now I need FM to plug in the text from my Roommate Info field from all records with the same room number (Field = Room Assignment). I know I need a new field to receive the info, but I'm not sure how to make the connection. (The data is all there and the room assignments have been made.) I'm sure a script that sorts, searches, etc. might do the trick, but is there an EASIER way that I'm overlooking? (Did I mention I know squat about scripts?!)

This particular type of referencing just isn't something I ever do with my files. Can someone take pity on me?

[SlimJim]

You need to do a "self-join" by Room Assignment. Then a portal on your layout listing all the roommates. The only problem with this is that it will also list the student whose record you are looking at. If you do not want this, assumming that all the students have a unique ID number, add to your self-join the relation ID =/ (not equal) ID. This will pull out all the students in the same room with a different ID number to the one whose record is current.