List function sort order

Title

List function sort order

Post

Hi,

I was wondering . . . when using the List function to refer to a field in a related table with several records, what determines the sort order in the resulting calculation result? The table to which the function is directed is sorted but the list does not appear in the proper sort order. I'm trying to use a merge variable to generate the list which is working fine, but not appearing in the correct order.

You need to sort the relationship (in the graph) by the field you want sorted. This is because List() is based upon the relationship and not a found set. :^)

So you can actually have another table occurrence of your child table and sort it differently than the main relationship. You could even have two calculations, one pointing to a table occurrence of child which is sorted by Status (ascending) and point your calc to it and a second calculation which is pointing to a table occurrence of chid which is sorted by Status (descending) and point your calc to it.

Here's the scoop, and it's still not working as hoped. The table whose layout the list function is calling from is related to a another table sorted by "chair number" i.e. position in an orchestra. The second table is related to a contacts table with actual names. I'm trying to use the List() function in a merge variable two tables away.

(It also inserts to and from in the right places but that doesn't matter.)

To achieve the From part I have a Documents table, a Players (people and companies) table and a join table:

The relationship that I am having trouble with is arrowed. It is set to sort by join table (People_Docs_From) ID. I will explain why in a minute.

Here is the calculation of [Document_name]:

The relevant line is arrowed. Essentially I am pulling out a list of related names from the Players table and listing them.

Here is the Documents data entry screen:

When you fill in the fields the calulation assembles the document's name and displays it the document description field.

This is all working perfectly apart from one thing.

You can see that I have a portal for the From, To and CC. These are each linked to their own Players TO via their own join table. In the case of the people (Players) who a letter is from the join table which the portal is based on is People_Docs_From.

Let's say I want to have the above From and To people, in this order:

John Jones, Peter Smith | Fred Murray, Andy Perry

So I enter John Jones in the first line of the From portal (there's a dropdown which creates a new record in People_Docs_From) and Peter Smith in the second line.

They will be added to the [Document_name] calculation and displayed in the Document description field. But they are not always displaying in the right order.

I have ascertained that they are displaying in ascending Players::ID order, irrespective as to which person I put on the first line of the portal and which I put on the second line.

I want them to display in ascending People_Docs_From::ID order. So I have applied a sort on that field to the relationship shown with a red arrow above. But it makes no difference to the displayed sort order that the calculation produces. The calculation is still displaying the names by Players:ID order, not People_Docs_From::ID order as I wish. So let's say John Jones is Players::ID 10 and Peter Smith is Players::ID 2, in the document description field they will display:

Peter Smith, John Jones

But I want to display them the other way around, i.e. in the order in which they appear in the From portal. (There is a good reason for this.) It doesn't make any difference what I do in the relationship (arrowed above) on sort order, they are still sorting by Players:ID.

So the short version of my question is, how can I in the [Document_name] calculation sort the names that List() pulls out of the Players table by ascending People_Docs_From::ID order?

"I want them to display in ascending People_Docs_From::ID order. So I have applied a sort on that field to the relationship shown with a red arrow above. But it makes no difference to the displayed sort order that the calculation produces. So let's say John Jones is Players::ID 10 and Peter Smith is Players::ID 2, in the document name field they will display:"

Well, I'm confused too, the order that you report getting IS in ascending order. 2 sorts before 10 after all. But also realize that the order you descrobe is the same order you'd get with an unsorted relationship as your auto-entered IDs will be created in ascending order.

They are displaying in Players::ID order asc, but I want them to display in People_Docs_From::ID order asc, which they are not, even though the arrowed relationship sort order is People_Docs_From::ID order asc. I want them to display John Jones, Peter Smith, which is People_Docs_From::ID order asc, but they are displaying Peter Smith, John Jones, which is Players::ID order asc. This is happening on all of my calculated fields - Players:ID asc order keeps winning.

Sorting Players in any way won't help as people (Players) are needed to be in a different order on different documents. I need People_Docs_From::ID order asc to win every time, but I can't find a way to do that.

Notice in the from portal above they have been entered as John Jones then Peter Smith, which is the order in which they appear in the People_Docs_From table (ID 58 and ID 59):

Their Player IDs are also showing above (ID 12 and ID 11).

You will see in the document description field above (which displays what is returned by the [Document_name] calculation) they are showing in their Player::ID order (11,12) not their People_Docs_From table ID order (58, 59).

They would also sort in the From (that is People_Docs_From) portal in the Players::ID order if I had not added a sort order (People_Docs_From::ID asc) to the From portal.

From all of this I conclude that the Player::ID asc order is being imposed by Filemaker on the List(Players::Name) function and on the People_Docs_From portal, no doubt because both return/display data from the Players table. This happens even though the [Documents to People_Docs_From] relationship is sorted by People_Docs_From::ID asc order. I can re-sort the From portal in the portal settings to show the records in People_Docs_From::ID asc order, which I have done, but I don't know how to sort the data that is being returned by List(Players::Name) into People_Docs_From::ID asc order. That is my problem.

Apologies, the morning blood sugar levels are rising and I am now on a computer instead of an iPhone where it's easier to pick out the details.

The sort order between Documents and the Join table will have no effect on the order of values listed from Players. You'd need to specify a sort order between Join and players, but the field that you need is not available to you when sorting that relationship.

Two options:

Add an unstored calculation field in the join that copies the Names from players and list it with your list funciton.

Use ExecuteSQL to list the values as you then have more options for specifying the sort order. (You'll need a join clause to get both the names and the correct ID's in the same query so that you can list Names and sort on the ID's from the join.)

PS. I often think of ExecuteSQL as the "List function on steroids" as I can use it to do anything that list can do but without needing a relationship in Manage |Database and with a large number of options not possible in list.

LaRetta_1 I am struggling with this same concept/process and I'm not able to follow your answer well enough. I have a "Course" table, a CourseObjective table and an Objective table. I want to display a field "Objective::output_Objective" but ordered based on "Objective:number"

In my Course table, I currently have the field "list_Objectives" which is a calc = List(Objective::output_Objective). This works except that they are in the wrong order.

I can create a field calc is List(Objective::number) but I'm confused about what table occurrence to make. A TO of Course and relate it to Objective? Is this relationship supposed to be field calc and the Objective:number ?

You don't spell out the details of your relationships. From the names, it appears that you have:

Course----<CourseObjective>-----Objective

Your list function should list all objectives selected for a given course. All you need is to double click the relationship line between CourseObjective and Objective and select the sort option for Objective, selecting "number" as the field on which to sort the relationship.

Then your list calculation should list the Objects in the order specified for your Objective::number field in the relationship.

I have a similar situation where I am creating a block of text specific to each student that includes a list of ClassNames, Dates, and Times. I want to sort the list by date and then time order. It is sorting, as best as I can tell, by the EnrollmentID (or creation oder) which is the table where I join the student to the section (which comes from a join table that connects the class name to particular date and time).

I have set the relationships between all of these TOs to sort by date and then time.

This is a calculated field in my enrollment table in a filed called ClassInfo:

I get the list of classes by something list this:

" Intro text goes here" &

¶&

List (Enrollment::zctClassInfo) &

¶&

"Remaining text goes here"

This all works, I just want it to sort by the Sections::Date and Sections::StartTime.

Can you tell what I am missing?

Thanks,

Ann

PS:

I do not know Execute SQL and spent some time trying it based on examples. The most I was able to get was a "?".