7 Replies Latest reply on Feb 5, 2013 9:56 AM by FileMakerNovice

    Special Sort

    FileMakerNovice

      Title

      Special Sort

      Post

           Hello,

           I have a question relating to sorting in a "report".(Essentially a layout in list view.)  I have a grade that I'd like to display the top 10% of employees followed by their rank in their corresponding "bucket".  

           Let's say there are 10 employees.  The top 10% would be just one employee.  He would be ranked #1.  The remaining employees would be categorized into 3 buckets.(A, B and C)  This would not always be in ABC order.  Sometimes it would be BCA order or CAB or ABC.  This order would change every 6 months.  So:
           Name     Grade   Bucket
           John        100         A
           Karen       90          A
           Bob           98         A
           James      99         A
           Jim            70         B
           Jay            80         B
           Liz             99         B
           Kay            98         C
           Brian         50         C
           Jane          99         C

           In CAB:
           John
           Jane
           Kay
           Brian
           James
           Bob
           Karen
           Liz
           Jay
           Jim

        • 1. Re: Special Sort
          philmodjunk

               In the second list, John is still listed first because he has the top score. Seems like your top list should show some other value than A for that one top scoring record...

               You can use custom value lists to specify different arbitrary orders for sorting, but you'd need that first record to have some other value than "A".

               Thus you could have a set of custom value value lists--one for each possible order and a script can select the correct Sort Records step, that references one of these value lists, to get the desired record order.

          • 2. Re: Special Sort
            FileMakerNovice

                 Thanks for the quick reply.  Yes.  I have explored custom value lists but my main road block is the top 10%.  This is why John is at the beginning of the list.  So, if I understand you correctly, I need a new field that labels the top 10% of people.  Just as a point of reference, there is a total of around 150 employees.  The top 10% ends up being around 15 people.  

                 So "Field X", lets call it "Order".  How would I get something like:
            Name     Grade   Bucket   Order
            John        100         A         TOP 10%
            Karen       90          A          A
            Bob           98         A          A
            James      99         A            A
            Jim            70         B           B
            Jay            80         B            B
            Liz             99         B           B
            Kay            98         C           C
            Brian         50         C           C
            Jane          99         C           C

            I'm thinking something along the lines of Order being a calulation field but I can't quite figure the equation.
                  

            • 3. Re: Special Sort
              philmodjunk

                   Given the "top 10%" requirement, you probably cannot make that field a calculation. A script can sort your records by Grade, then Replace Field Contents can assign a value to the top 10% of your found set.

                   It could use this calculation:

                   If ( Get ( RecordNumber ) < get ( FoundCount ) / 10 ; "X" ; Bucket ) // "I'm using "X" where you are using "Top 10%"

                   You can't use this in a calculation field because changing the sort order will change the value returned by this calculation.

              • 4. Re: Special Sort
                FileMakerNovice

                     I also would like to know a way of getting a "Rank" regardless of bucket.  I currently have a running count that labels the rank but this changes when the list is sorted.  If I had a rank instead of this running count, I could do an equation like:
                     if(Rank<=EmployeeCount*0.1),"Top 25%",Bucket)

                • 5. Re: Special Sort
                  FileMakerNovice

                       Good thinking with the script.  I think my understanding of when to use a calculation vs script is lacking.  So... I could do the same thing with the "Rank" field.  

                       It looks like we posted at the same time, but I would also like a "Rank" that would be independent on sort order.  Would I just assign the current record number to a field and it would stay that way until I rerun the script?

                       If this is the case, should I use a calculation field for the "Top 10%"... Oh wait, I don't think I can since again, it would require a reference to count of employees.

                  • 6. Re: Special Sort
                    philmodjunk

                         You can use replace field contents to assign the rank to a number field in each table after first sorting the records in correct order for ranking. You then have a rank value that will persist with any sort order or found set you might have.

                    • 7. Re: Special Sort
                      FileMakerNovice

                           Awesome!  Two fields to be populated upon going to layout.  Done and done.  Thanks.