I have a single DB for student records.

When you bring up a specific student there is a portal that shows who that students siblings are.

To accomplish this I created a field called ID1 which is defined as:

Student Last Name & Right(Primary Home Phone,4) so that any students that share a common last name and the last 4 digits of their phone number are considered siblings.

Then I created a self relationship where ID1:ID1 and I place this in a portal. It brings up the siblings but it also lists the current record. So that the current student shows up as his/her own sibling. Is there a way to elimanate the current record from the list?

During your navigation, set a global field to StudentID (or whatever the primary key of your record is), then create a calculation field (cFamilyID) with the following: If ( StudentID <> gStudentID, FamilyID, "" ).

You relationship then becomes FamilyID::cFamilyID, and you will show all related records apart from the current record.

Of course you have a serious problem with relating records via such a volitile data as the last name and phone numbers. As soon as one of those changes your relations all break.

I highly recommend that you use genuine IDs. See my FAQ for how to accomplish this http://www.fmforums.com/ubb/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=8&t=000018

When I set up the field cFamilyID like:

If ( StudentID <> gStudentID, FamilyID, "" ) for some reason the result comes out to just the last 4 digits of the students phone number which is different then the FamilyID which is lastname + last 4 digits of phone number.

Any ideas?

When I create fixed length portals, I use a second relationship to find the related records and reindex the "show" group all controlled by scripts.

Make sure the field definition for cFamilyID has the type for the result set to text, not number. If the resulting type is number, all characters except numbers are ignored.

-bd

Ok, that fixed that. However when I create the relationship:

FamilyID::cFamilyID

I get the error "This relationship will not work because the field ::cFamilyID cannot be indexed."

Any ideas?

Thanks

Markpro,

Can you expand on this?

It's been a long day...I am starting to brain fluf. I'll try to expand sometime this weekend. Sorry for the delay.