sorting data related to related data

[aharown07]

I think this is a relationship problem but I'm not sure.

I've run into twice today & don't remember seeing it before....

Here's the situation

* I have a table occ. I'll call Table A.

* Table A has one record & is joined to a TO named "Call" in such a way that the one record is related to all the records in Call.

* Call joins to a TO called "Officer Involvement" which joins to a TO called "Officer"

* In a layout based on Table A, I place a portal displaying records from Call. This works as expected, showing multiple Call records when appropriate and in the sort order I specify (either in portal or in relationship).

I also want to display in each row of this portal, the related data from Officer Involvement.

This also works OK, except that there can be multiple Offcr Invlvmts per call...so it displays just one of them.

I've been pretty successful (I think) getting that to behave as I want.

But... I also want to disply in this same portal, data from Officer, specifically THE data that is related to whatever Officer Involvement is showing.

But when there is more than one Officer Involvement related to the Call, the portal consistently displays the Involvement data from one record, but the Officer data from one of the others.

Why? And is there some way to get the Officer data to sync w/the Offcr Invlv data?

(I checked...Call is not related to Officer in any other way than via Officer Involvement).

I have a somewhat similar problem with a report, but I don't know if there is any real connection. Different tables involved, but it is another scenario where only one of several related records is displayed, but the data related to the displaying record is from one of the others.

Have I broken something?

[SlimJim]

I think this can happen when you are going through relationships in a portal. Can I suggest, no guarantees, that you try displaying records from Officer (or maybe Office Involvement) rather than from Call.

[aharown07]

Well, I could give that a try, but the purpose of the portal is to be a "pick list" for selecting a Call.

Since each Officer record is related to gobs of calls, I think that would be even messier.

I actually have alot of situations like this. Rather than have a copy of the Officer's name(s) on every call record, I've set it up to link by an ID... but then in situations where I want to see the Officer name, I run into trouble. In this particular case, seeing the name is optional, but I'd love to understand why it behaves the way it does. Seems like it would be "easier" for FM to follow the relationship down to the end of the chain rather than do what it does.

Edited November 29, 2005 by Guest

[SlimJim]

Lets be truly simplistic: Say you have one Call. The call has two Office Involvements Office1, Office2. Each office has two officers: Officer11, Officer12 at Office 1 and Officer21, Officer22 at office two. What you will see in the portal is the first office in the sort order of Offices and the first officer in the sort order of Officers. If you want to arrange that the officer belongs to the office then sorting the Officer table by Office then Officer and sorting the Office Involvement table by Office (same sort) should do the job. Unfortunately I have nothing to check this on so it is somewhat theoretical.

[aharown07]

Yes... I get it now I think. As long as the tables going down the relationship chain are sorted the same way, it all works fine.

Thanks!