6 Replies Latest reply on Apr 30, 2011 2:53 AM by AlbertHinds

    Sorting on multiple criteria based on user input

    asprings

      Title

      Sorting on multiple criteria based on user input

      Post

      I'm working on a Filemaker Database (using Filemaker Pro 9) that searches and displays data from a large data set. I've written an interface for searching, but I'm a bit stuck as to how to script the sorting of the data.

       

      I want users to be able to sort on up to three categories (e.g. last name, city, zip code, etc) with up to eight choices of sort order for each of the three - so, for example, they could sort by (last name; state; zip code) or (zip code; city; last name), or (first name; country; last name) etc. I really want to script this, rather than letting the users use the built-in sort function - mostly so I can limit which fields they can search by, but also so I don't have to train all the users how to properly use search!

       

      Unfortunately, the Sort Records script step doesn't seem to take any input other than fixed fields - I can script "sort by Last Name" but not "Sort by [calculation to get field name]". I could do this with a series of if statements for every possibility, but with three options and eight permutations there'd be 512 options to code - it seems like there must be an easier way!

       

      Any suggestions?

        • 1. Re: Sorting on multiple criteria based on user input
          TSGal

          asprings:

           

          Thank you for your post.

           

          The fastest solution, but the most time consuming, would be a sort for each of the permutations.

           

          Another option would be to have one sort on three temporary fields, TEMP1, TEMP2 and TEMP3.

           

          If the person chose Last Name, City, and Zip Code, you could replace TEMP1 with Last Name, TEMP2 with City and TEMP3 with Zip Code.  Then, perform the sort.  However, this could definitely take a long time, especially if you have a lot of records.

           

          Anybody else out there with a viable solution?

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Sorting on multiple criteria based on user input
            jsalzer_1
              

            Hey, TSGal,

             

            I use your solution, but there is a bit of work involved.  What you need is three globals:

             

            gSortOption1, gSortOption2, and gSortOption3 that display as popup menus with value lists containing the 8 sort choices.

             

            Then, you need three calculated text fields that take, for example, the LastName and enter it into the calculated field if gSortOptionX has a certain value.

             

            This is where it gets tricky.  You now have 3 text fields to sort by.  But, what if one of my options is a number?  Surely, we don't want 155 to show up before 20.  Dates will also be a problem.

             

            So, your calculated fields need to convert any date fields into a format sortable as text.  The easiest is to code it to convert into 20090122 (YYYYMMDD) format.

             

            Numbers are a bit tougher.  You need to take into account the highest possible value that might be entered into that field, determine how many decimal places to the left of the decimal are needed, and then take every number and append the appropriate number of zeros to it.  In that way, 155 in the real field might come over into the calculated text field as 00000000000155 so that it will sort after 00000000000022, which has one additional zero.

             

            It may be a pain to set up, but you'll save time down the line when you don't have to teach your clients how to use the built-in sort (which can include hundreds of fields that have no real relevance to the end user.)

             

            Enjoy! 

            • 3. Re: Sorting on multiple criteria based on user input
              Sorbsbuster
                

              ...and a scaled-down variation of jsalzer's idea is to use his 'Sort on global field' bit to answer the often-related question: "How can I let the user change the sort criterion for a portal?"

               

              Define the portal to display sorted according to the field 'gPortalSort', and set that global field using the same ValueList method suggested.

               

              Alan.

              • 4. Re: Sorting on multiple criteria based on user input
                comment_1
                  

                jsalzer wrote:

                Numbers are a bit tougher.  You need to take into account the highest possible value that might be entered into that field, determine how many decimal places to the left of the decimal are needed, and then take every number and append the appropriate number of zeros to it.


                Actually, numbers are a lot more tougher than that: values can go up to 10^400. In addition, they can be negative, or fractions (or both). By the time you accomodate all possible values, it's going to get quite slow (not that sorting on an unstored calculation is ever quick). 


                • 5. Re: Sorting on multiple criteria based on user input
                  raybaudi
                    

                  Hi asprings

                   

                  > I want users to be able to sort on up to three categories

                   

                  Why don't let your user sort using a Table View ?

                   

                  Remember that you can:

                   

                  1) Sort by clicking the name field

                  2) Sort multiple columns by Shift-Clicking them

                  3) Change the sort order of multiple fields by reordering columns so that the first on the left will sort for first.

                  • 6. Re: Sorting on multiple criteria based on user input
                    AlbertHinds

                    I have a similar situation.

                    Over 1,500.000 records and I don't want to deal with the user frustration when they sort before a find or sort in error by clicking a column heading.

                    For data display only, I plan to pre-sort the data for the know requirements and then creat a seperate Sort_Key field that is sorted by a script.