4 Replies Latest reply on Nov 30, 2012 3:05 PM by C.Kobel

Problem sorting report via portal field

Title

Problem sorting report via portal field

Post

I am attempting to alphabetically sort a report (layout: Roster, table: Office) by a field in a portal (table: Dentist). For some reason it's not sorting properly. It does rearrange some of the records, but not in any order I can tell.

The field is a calculation (Name_Last & ", " & Name_First). The portal is filtered to only show the main conact (by way of a dropdown selection from a value list on the Dentist table).

Any help would be appreciated.

• 1. Re: Problem sorting report via portal field

A portal implies that there is more than one record related to the current record on the layout. If that is the case, which value from which related record in that portal should determine the sort order?

What should happen is that the "first" related record will supply the value used to determine the sort order. That "first" record may or may not be the first record shown in the portal.

• 2. Re: Problem sorting report via portal field

The relationship is Office (one) to Dentist (many). The roster report is pulling the names of the dentists at each office (Dentist::Name_Full LF). I filtered the portal to only display records which are marked as the main dentist (which is done on the Dentist table/layout). So there are multiple records related to the current record, but only one is displayed in the portal.

Is there a way to change which value determines the sort order? Or is there a simpler method of setting this up that I'm overlooking/overthinking?

• 3. Re: Problem sorting report via portal field

You could specify a sort order in the relationship (Open Manage | Database | Relationships and double click the relationship line.) that sorts the related records in such a way that the main dentist is the first related record.

You could, instead, define an additional relationship that only matches to "main" dentists. You can setup a new occurrence of your portal's table for this relationship and include a calculation field in Office that matches to the field that identifies the Main dentist so that you are only matching to the main dentis. THen you can refer to a field in this new occurrence of dentist for your sort order.

And if you are using FileMaker 12, you can use an ExecuteSQL calculation in a calculation field in Office that only returns the name of the Main dentist and then you can sort on this field for your sort order.

• 4. Re: Problem sorting report via portal field

I tried sorting the relationship before and it didn't work. I just tried again because of your comment and it worked like a charm! I apparently sorted ascending rather than descending before. This has been frustrating me for 2 days. THANK YOU!!!!