13 Replies Latest reply on Apr 14, 2017 6:57 AM by tcwaters

    List function sort order

    RickWhitelaw

      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.

      RW

        • 1. Re: List function sort order
          LaRetta_1

          Hi Rick!

          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.

          • 2. Re: List function sort order
            RickWhitelaw

            Thanks LaRetta!

            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.

            RW

            • 3. Re: List function sort order
              LaRetta_1

              That won't work, as you have found out.

              In your main table, create a calculation which is List (child::IDs).  Relate that calculation to a table occurrence directly to your grand-child table.  Sort THAT relationship as you wish.

              • 4. Re: List function sort order
                peter2017

                Hi

                 

                I also have this isue, but changing the relationship is not working for me.

                 

                Essentially I have a document database, and in that database I have a calculated field [Document_name] that takes various parts of the name of a document and assembles them:

                 

                Prefix | Type | Suffix | From | To |

                 

                e.g

                 

                Registered | Letter |           | John Jones, Peter Smith | Fred Murray, Andy Perry

                 

                (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:

                 

                2017-02-21_Relationships_Graph.png

                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]:

                 

                2017-02-21_Document_name_calculation.png

                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:

                 

                2017-02-21_Docs_Data_Entry.png

                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?

                 

                Thanks for any help anyone can give on this.

                 

                Peter

                • 5. Re: List function sort order
                  philmodjunk

                  "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.

                  • 6. Re: List function sort order
                    peter2017

                    Phil

                     

                    Thanks.

                     

                    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.

                     

                    Peter

                    • 7. Re: List function sort order
                      philmodjunk

                      To repeat, the order you describe is the order that you should get if you don't sort the relationship so it appears that you don't need any sort order.

                       

                      As to why you don't get that order now, perhaps your ID field is defined as a text field.

                      • 8. Re: List function sort order
                        peter2017

                        Here are some more explanatory images:

                         

                        Docs data entry:

                         

                        2017-02-21_Documents_data_example.png

                        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):

                         

                        2017-02-21_People_Docs_From_table.png

                         

                        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.

                         

                        Peter

                        • 9. Re: List function sort order
                          philmodjunk

                          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:

                          1. Add an unstored calculation field in the join that copies the Names from players and list it with your list funciton.
                          2. 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.

                          • 10. Re: List function sort order
                            peter2017

                            Brilliant

                             

                            I will try this a bit later and report.

                             

                            P

                            • 11. Re: List function sort order
                              tcwaters

                              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 ?

                               

                              Thank you.

                              • 12. Re: List function sort order
                                philmodjunk

                                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.

                                1 of 1 people found this helpful
                                • 13. Re: List function sort order
                                  tcwaters

                                  Yes, had the sort incorrect in the relationship