7 Replies Latest reply on Jul 11, 2013 11:42 PM by carojo

    FM12 Relationship sort order behaviour (problem with)

    carojo

      Hi there

       

      Wondering if any of you experts out there can cast light on the following issue I'm having using the Sort option in Table Relationships which is resulting in some unexpected behaviour.

       

      According to FM12 help (Changing Relationships "Select Sort records to display the first found record in a particular sort order (such as the lowest or highest value) when you have one related field directly on a layout of the current table. (For example, display the most recent check-out date for a particular piece of equipment.) Also sort related records to access multiple records that are in a particular sort order and display them in a portal."

       

      Whilst this appears to be working ok for fields from the related table with sort order specified (many end of 1:m) what I'm finding is that the sort order does not get applied to fields from another related table to this "many" table (in this case it's many:1 end of relationship)

       

      To atttempt to clarify this further I have:

       

      Table A < related to > Table B < related to > Table A_1 (same base table as A)

      < A::primary_key = B::foreign_key > < B::foreign_key_1 = A_1::primary_key >

      Relationship Sorted by date field in B (desc) Relationship Sorted by same date field in B (desc)

       

      So on a layout based on table occurence A a portal for fields in B appears in reverse date order as expected. Similarly a field from B placed directly on the layout (ie not in a portal) is for the record in B with most recent date as expected.

       

      However the logic applying to sort order does not carry through to fields based on the table B relationship ie from Table occurence A_1.

       

      No matter which way I sort the relationships to table B I get the same record for A_1 which is presumably coming from tracing the relationship from A via the first created B record (ie default behaviour when no sort specified) instead of what I was hoping for.

       

      So in the layout (which doesn't use a portal) I end up with data from records in tables A and B as desired but with A_1 data from a record completely unrelated to the table B data displayed.

       

      I can think of a get-around for this using calculated fields and ExecSQL statement but it seems a pity that I can't use the built in relationship definition.

       

      Any feedback / comments / suggestions most welcome!

       

      Thanks

      Caroline