Relationship Sorting question Table A to Table C via Table B

[djlane]

I have three tables. Table A is related to Table B using >= and a primary key. ie The _pk in table A must match the _fk in table B, and a Date field in Table A must be greater than or equal to a date in Table B. The relationship shows multiple records in B but its sorted so that the record in B that I want appears at the top. So far so good.

Table B is related to Table C by Primary key only. (_pk in C = _fk in B )

What I want to do is display only the related record in C which is related to the record I want in B (ie the top record).

What I’m getting is the first record in C, in creation order sequence. Its not the record in C that is related to the record showing from Table B.

Is there a way that I can make the records in C sort according to the same sequence as in B ? ie, that the first record in C is the one that is related to the first record in B ?

[mr_vodka]

Huh. I just tested and confirmed this. I didnt realize this issue either. One would figure that if the relationship is sorted, then whatever is at the top is the first related record and thus a related table occurence based off that one would return the first related record to the sorted one.

I dont recall this being an issue before but then again I dont really sort too many relationships. Perhaps others can chime in here and verify that it happens in older versions as well.

sorted_relationship.zip

Edited September 16, 2008 by Guest
added sample file

[comment]

It's a known issue ever since version 7 (that is ever since cascading relationships became possible):

http://fmforums.com/forum/showpost.php?post/291896/

[mr_vodka]

Hmmmm... I dont recall that thread, again perhaps because I dont really use them that often. I wonder if this was left like this on purpose or if its a bug.

Personally, I think its rather odd. Especially if you consider sorting a relationship for a portal and then you want to put a related table's info on there are well.

Edited September 16, 2008 by Guest

[comment]

wonder if this was left like this on purpose or if its a bug.

I am leaning towards the former. At least conceptually it seems a more correct approach: first, we establish an unordered set of all related records from the target TO - regardless of through which intermediate record/s they are related. Only then the set is sorted.

It's tempting to think of the 'first related grandchild' as the 'first child of the first child'. But how about the second, the third ... and the last - when it's possible for the same grandchild to be related through more than one child?

There must be some order that is consistent throughout the entire related set - otherwise you could get the same record as both the first AND the last. That would be in stark contradiction to what you would see in a portal directly to the grandchild TO.

if you consider sorting a relationship for a portal and then you want to put a related table's info on there are well

I don't think there's a problem with that - as long as the other TO is not in the path to the portal TO. Each row will show data from ITS OWN first related record in the other TO.

[mr_vodka]

So it seems that when putting a field from the grandchild will "flow through" from the perspective of the parent, however putting the same grandchild field on the parent layout without using a portal evaluates from the perspective of the CHILD native table using the default creation order.

This is interesting and something to keep in mind when using such models as Anchor Buoy.

[comment]

So it seems that when putting a field from the grandchild will "flow through" from the perspective of the parent, however putting the same grandchild field on the parent layout without using a portal evaluates from the perspective of the CHILD native table using the default creation order.

I'm afraid you've lost me there. :

[mr_vodka]

Sorry. Let me try and clarify my cryptic message... :

If the field of the grandchild is put on the portal row (reflecting the sorted child records), it will return the first related record of the SORTED CHILD's first related record ( row 1 or portal) as I had expected earlier.

However, putting the grandchild field directly on a layout based off the parent, will make it reflective of the CHILD table in the default state which goes to the first related grandchild record.

[comment]

If the field of the grandchild is put on the portal row (reflecting the sorted child records), it will return the first related record of the SORTED CHILD's first related record ( row 1 or portal) as I had expected earlier.

Yes, but sorting the child portal (or its underlying relationship) has no effect on this. A grandchild field placed inside a portal to Child will always return data from the first related grandchild record, as seen from the child record IN THAT ROW - no matter how the portal is sorted (or not).

However, putting the grandchild field directly on a layout based off the parent, will make it reflective of the CHILD table in the default state which goes to the first related grandchild record.

I don't think that is a correct assesment. I believe that in this case the sort order (including the default creation order) of the child records is ignored completely, and only the sort order as defined for the grandchildren in the Child - Grandchild relationship is respected.

Note:

The Parent, Child, Grandchild terminology can be somewhat misleading here, because the same rules apply to both:

Parent -< Child -< Grandchild

and:

Parent -< Join >- "Grandchild"

[mr_vodka]

Yes, but sorting the child portal (or its underlying relationship) has no effect on this. A grandchild field placed inside a portal to Child will always return data from the first related grandchild record, as seen from the child record IN THAT ROW - no matter how the portal is sorted (or not).

You have stated both things better than I.

I still find it strange that this is the behavior ( although now I can buy into it). Its almost as if the records in the portal ( and its relationships sort order included ) is respected first on a parent layout due to it being in a portal row, as opposed to it not being on a portal row but rather the parent layout itself.

I don't think that is a correct assesment. I believe that in this case the sort order (including the default creation order) of the child records is ignored completely, and only the sort order as defined for the grandchildren in the Child - Grandchild relationship is respected.

Yes I did not want to go into a case scenario where the grandchild relationship had a sort on it.

[comment]

Yes, a field placed in a portal of a TO other than its own is a rather special case. If you place the field directly on a layout, it will show data from the record that would be in the first row of a portal directly to the field's own TO.

The more I think of this, the more sense this behavior makes to me - especially when I consider a join table scenario.

I did not want to go into a case scenario where the grandchild relationship had a sort on it.

It makes no difference - a relationship is ALWAYS sorted. The rules, I believe, are the same for the default creation order as for a custom one.

[djlane]

Thanks Guys,

On my Table A layout I put a one line portal to Table B, and in the portal is the field from Table C that I want to show.

To make it look like a “normal” field, I removed the border from the portal, and I now have the correct record from Table C appearing on my layout.

Thanks again!