1 Reply Latest reply on Dec 9, 2008 1:37 PM by TSGal

    Sorting one record multiple times

    scornkby

      Title

      Sorting one record multiple times

      Post

      I have an Excel spreadsheet that lists multiple dates for one record. The information in the spreadsheet is Movie Title, Country and Date. There are multiple Countries for each Movie Title and vice versa. I need to be able to sort all of the Dates chronologically for each Movie Title (listing the Countries and their Dates) and then by Country (listing the Move Titles and their Dates), but this would require that the one record be duplicated multiple times for each of those sorts.

       

      I have managed to import the sheet by Movie Title, and then transpose the data and import the sheet by Country.

       

      Any ideas on what can be done short of creating a duplicate script to make each Movie Title, Counrty and Date it's own record?

        • 1. Re: Sorting one record multiple times
          TSGal

          scornkby:

           

          Thank you for your post.

           

          Each record in FileMaker Pro is its own entity.  Therefore, you would need each field filled in.  This is easily accomplished with a script.  It might include

           

          Enter Browse Mode []

          Go to Record/Request/Page [First]

          Set Variable [$movie; "" ]

           

          Loop

             If [IsEmpty (Movie) ]

                Set Field [Movie; $movie ]

             Else

                Set Variable [$movie; Movie ]

             End If

             Go to Record/Request/Page [Next; Exit after last ]

          End Loop

           

          ==============

           

          That is, we go to the first record and initialize the variable $movie to nil/empty.

           

          We then enter the loop and evaluate to see if the field Movie is empty.  If so, then we fill Movie with the contents of $movie.  If Movie is not empty, then we store the contents of the field into the variable $movie.

           

          We go to the next record and evaluate again.  If the last record is reached, we exit the loop.

           

          Does this make sense?  You can adjust it for the other fields as well.

           

          If you need clarification for any of the above steps, please let me know. 

           

          TSGal

          FileMaker, Inc.