10 Replies Latest reply on Feb 22, 2011 9:07 AM by BoiceDaniel

    List Function Sort Order

    BoiceDaniel

      Title

      List Function Sort Order

      Post

      I have a list of ids from a child table in a field in a parent table in a certain order.  I have a calculation field in the parent table that gets a list of data from the child table.  I want the list of data from the child table to appear in the order I have set in the parent ids field. 

      The list function will sort the data alphabetically and by the definition set by the relationship, however it does not seem to want to unsort the records so the results will appear in the custom order that I have set in the parent field.

      I thought about creating a variable and storing it in the child field, and then sorting it based on that, but of course that won't work because the records in the parent table all have different orders for the parent field. 

      Perhaps I need a custom list function.  I could create a string based on a calculated field in the child table that I could then play with, but I just wanted to check to see if there is any other way to do this first, because that seems like it would get rather complicated.

      I have defined a relationship between the parent table field with the child ids to the id field of the child table. 

      The reason for all this is that in the parent table I have records of a list of publications, and the child table has a list of authors.  One author may have several publications, and one publication may have several authors.  The list of authors needs to be set for each publication to appear in the defined order for the citation.  The structure I have seems to work, except for the sort order for the authors using the list function.

      I had it set up with a join table..  Perhaps that is the best solution; use a sort field in a join table for each publication.  But again that will get a little tricky arranging and rearranging the order and it seems a little redundant.  It would be nice if the list fuction had a variable that allowed you to specify the sort order.

      Perhaps I could retrieve the author name each time the author is selected and append it to the end of the list in the parent field, then if it is deselected, have a substitute fuction subtract it from the list.  That might work to create and store the citation, except it is not very efficient in terms of the amount of data that is stored.

        • 1. Re: List Function Sort Order
          philmodjunk

          The order you see returned from the List function is controlled by the sort order defined in the relationship that establishes the context from which it was evaluated. This is a fixed sort order, can't be changed, but you can try borrowing a trick used to make portals dynamically sortable as they have the same limitation.

          Can you give an example of how you want to control the sort order? I suspect that you can set up a calculation field that is used for the relationship's sort order, but can't go into detail without knowing more.

          • 2. Re: List Function Sort Order
            BoiceDaniel

            Thank you for your reply.

            As I mentioned in the previous post, I am making a little database to organize some research articles.  One table has the list of books and articles, another table has a list of authors.  The research articles often have a whole list of authors and the authors must appear in a certain order for each article.  The authors do not necessarily use alphabetical order.  The order of the authors on the publication may depend on who did the most work or has the most prominace, so you need to specify the order of authors for each article.  The authors may appear on several different articles with other authors in a different order for each article.

            So what I did was have it so the ids for the authors would be added to a field in the article table when selected from a list.  That was fairly easy to do, so now I have a field with a list of ids in the order I want the authors to appear.  I created a table instance of the article table with a link from that list of ids to the author id field in the author table (many-to-many).  I was then able to have the list of authors appear in a portal benieth each article in a layout.  The only problem is that the authors are in the wrong order.  I want them to appear in the order as the field in the article table.  I also have a calculation field that was using the list function to generate a citation, which has to be in a certain format with the authors in the right order.  The list function puts the authors in the order defined by the relationship, but I do not want that.

            I would imagine that this sort of thing would be common.  For example, if you had a movie database with a movie table and an actor table.  You would want the cast to appear in the right order under the movie depending on the order set by the movie producers.  You might also want to see all the movies under a certain actor (which would be easier because you could sort the movies by date).

            Your help is very much appreciated.

            • 3. Re: List Function Sort Order
              philmodjunk

              If you defined a number field in your join table where you entered a number, you could define a sort order for the relationship to the join table that sorts on this number field. Put a 1 in the field of the first author,  a 2 for the second and so forth. Then your list function would list values from this table in the user specified order.

              You'd want to define this field in the join table as the order will be different for each publication.

              • 4. Re: List Function Sort Order
                BoiceDaniel

                I tried that but couldn't get it to work.  It would sort the join table but not the authors.  It continued to sort the authors based on its own order.  Maybe I did something wrong.  I then tried to add a lookup field in the authors table that would get the sort value from the join table, but that didn't work either.

                • 5. Re: List Function Sort Order
                  philmodjunk

                  The following worked for me.

                   I defined a field in the join table, cName to return the name from the related author table.

                  I defined a sort order for the join table in the Publications to Join table relation ship that sorted the Join records by a Sort Order number field in descending order.

                  I defined this calculation field: cAuthorList as Substitute ( List ( Join::cName )  ; ¶ ; ", " )

                  and I got a horizontal list of author names separated by commas. If I edited the values in the sort order field, the order of names in this calculation field updated to match the new order.

                  • 6. Re: List Function Sort Order
                    BoiceDaniel

                    Very good.  i suppose that is what you would have to do.  But still I wonder if that is the most efficient way to store the data.  It seems that you would be storing the name over and over in the join table.  Or is that calculated every time?  I suppose it doesn't matter because it isn't a very big database.

                    Perhaps a sorting feature for this situation would be something for the filemaker developers to add to the program.  Maybe an option in the relationship window to have the sort based on the order the ids appear in one table instead of just ascending or decending.  That would be a nice (useful) feature that could probably be used in many ways.  Or perhaps if you did not select a sort order for a table, then it would default to the order of the preceeding table.  That way you could sort a join table and like in this case it would sort the next table over (authors) in that order.

                    Thanks for the help

                    • 7. Re: List Function Sort Order
                      philmodjunk

                      the name calculation and the list calculation are unstored calculations by definition as they access related records in their expressions.

                      • 8. Re: List Function Sort Order
                        BoiceDaniel

                        I played with it for a little while and it is still putting the list in the wrong order.  It also isn't updating the citation field in the article table when I change the value of the sort field in the join table.

                        I was able to get the portal to sort correctly and I put an calculation field in the join table that looked up and stored the author name.  I defined the sort order in the relationship to the join table to sort based on values in a sort field.  So you would think that the list function, since it is getting the list from the join table now, which is sorting the list based on the sort field, would display the list in the right order, but it doesn't.

                        I wonder what I am doing wrong.

                        • 9. Re: List Function Sort Order
                          philmodjunk

                          Here's a download link (click the blue download button and wait 20 seconds for download link to appear) to the file I created to double check the answer I posted. Compare it to your own to see what's different.

                          http://www.4shared.com/file/j1bUR0Bd/AuthorTOPublications.html

                          • 10. Re: List Function Sort Order
                            BoiceDaniel

                            Ah! ok, I see.  I had the field set to "text" and then was setting the calculation in the options.  This is what was causing the confusion on my part about the storage.  Once I changed the field type to "calculation" (before opening the options and entering the calculation), it worked.  I thought that by setting a calculation, that would make it a calculation field.  It didn't make sense that it was copying and storing the name in the join table.  Forgive me, I am a novice with filemaker.  Perhaps this will help someone else someday.

                            good thing I checked.

                            Thanks a lot.