1 2 3 Previous Next 41 Replies Latest reply on Jan 25, 2013 7:34 PM by philmodjunk

    Ranking?

    BeachedWhale

      Title

      Ranking?

      Post

           I have a sorted found set. Now I want to rank this set from 1 to last.

           I looked it up and came across this website for identifying unique records and then ranking entries in a subsummary report, here is the link:

           http://www.filemakerhacks.com/?paged=11

           So in order to rank in their method, you have to create a field called flag_unique whose unstored calculation looks like this:

           (I'm trying to rank the Product Types)

            

      Let ( x = GetNthRecord ( ProductType ; Get(RecordNumber) - 1 ) ;

      If ( ProductType <> x ; 1 ; "" )

      )   //   end let

           Here is the link: http://www.filemakerhacks.com/?p=25

           However instead of getting a 1 as flag_unique for the first instance of product type, I get a 1 in all instances. Is there something wrong with the field code?

        • 1. Re: Ranking?
          BeachedWhale

               If there is a better way to rank a list of Product Type subtotals, please let me know!

          • 2. Re: Ranking?
            davidanders

                 The above code will take a list of seven Joes and four Sallys and return the first Joe and the first Sally, both with a value of 1.

            http://www.filemakerhacks.com/?p=25
            Identifying Unique Records

                  

            http://www.filemakerhacks.com/?p=120
            Ranking Entries in a Summary Report

                 Post #2 in the series  -  Yesterday we looked at a simple method to flag unique entries in a found set. This time, we’re going to look at an additional use for this technique, using the same data set and demo file as last time.

            • 3. Re: Ranking?
              philmodjunk

                   If your data looks like this: (Each row is a different record:

                   Name     Score     Rank
                   Fred           5          1
                   Jim            4           2
                   Henry         4          2
                   James       3           4

                   Then you need a different getNthRecord expression:

                   If ( Get (RecordNumber ) > 1 ;
                        Let ( Sprev = GetNthRecord ( Score ; Get ( RecordNumber ) - 1 ) ;
                                If ( Sprev = Score ;
                                     GetNthRecord ( Rank ; Get ( RecordNumber ) - 1 ) ;
                                     Get ( RecordNumber )
                                   ) // if
                               ) // let
                       ; 1 )

                   The records must be sorted by score before this calculation will correctly rank the records. (Make this an unstored calcualtion.)

              • 4. Re: Ranking?
                BeachedWhale

                     How did you get the Rank field?

                • 5. Re: Ranking?
                  philmodjunk

                       Create a rank field of type number to get it into the table's field list. Then use the change button to convert it into a calculation field with:

                       If ( Get (RecordNumber ) > 1 ;
                            Let ( Sprev = GetNthRecord ( Score ; Get ( RecordNumber ) - 1 ) ;
                                    If ( Sprev = Score ;
                                         GetNthRecord ( Rank ; Get ( RecordNumber ) - 1 ) ;
                                         Get ( RecordNumber )
                                       ) // if
                                   ) // let
                           ; 1 )

                       as its calculation.

                       Or you can keep it as a number field and use this expression with Replace Field Contents to assign the rank to these records after sorting them by score.

                  • 6. Re: Ranking?
                    BeachedWhale

                         Do you mean set the rank field as type number and then in options, auto enter and specify calculation as the expression above? I tried that, and the rank field was empty. But in addition to my data being a different record for every row, I have different values for the same Product Type, which I subtotaled and that is what I'm ranking, so maybe its trying to rank every record instead of each subtotal.

                    • 7. Re: Ranking?
                      philmodjunk

                           Do not use auto-enter, change it to a field of type calculation. I believe that this will also need to be an unstored calculation.

                           What I have posted to date ranks a single found set of sorted records. I have no idea what you are doing with product types and sub totals here so you'll need to explain that part of this in much more detail.

                      • 8. Re: Ranking?
                        BeachedWhale

                             When I create the field, the change button is greyed out so it won't let me click it.

                             So i have types and subtotals for those types:

                             Instead of this,

                        Name     Score     Rank
                        Fred           5          1
                        Jim            4           2
                        Henry         4          2
                        James       3           4

                        I have,

                        Name     Score     Rank

                        Fred           5          

                        Fred            6

                                   total 11        1

                        Jim            4           

                        Jim             3           

                                      total 7         2
                        Henry         4          

                                      total 4          3


                        James       3           

                                     total 3           4

                        • 9. Re: Ranking?
                          philmodjunk

                               Is the field selected? Did you change the field type by selecting Calcualtion? (You have to make a change before change button is enabled.)

                               Do you have a table where you have one record for each product type? (I am guessing that "Name" in my example is "product type" in your database.)

                               What defines the found set of records present when you establish this ranking? (what do you do to produce this found set?)

                                

                          • 10. Re: Ranking?
                            BeachedWhale

                                 Ohh I see, I didn't know how to use the change button.

                                 No, I don't have a table with one record for each product type. That would be similar to making a flag_unique field, no? Like in the earlier post. I produce this found set by creating a part defined layout (by Subsummary of Name/Product Type) in List view. 

                            • 11. Re: Ranking?
                              BeachedWhale

                                   So I used the expression and replaced score with the subtotal. I'm getting a ranking of 1 for all product types though.

                              • 12. Re: Ranking?
                                philmodjunk

                                     As I stated previously, unless you have just one found set to be ranked from beginning to end--each record getting a different ranking value, the method I have described won't work.

                                     I would think that a table where you have one record for each product type would be very useful to you for a number of reasons. For such ranking, you could do the ranking from this related table instead of this table where you don't want to rank the records, but instead need to rank each group of records.

                                • 13. Re: Ranking?
                                  BeachedWhale

                                       I tried to create a field for that where it would produce a 1 for the first instance of that Product type and then blank for others, so that each Product type would have a 1 for the first instance. I did it with this expression but it didn't work and gave me a 1 in all instances.

                                       Expression I used:

                                        

                                  Let ( x = GetNthRecord ( ProductType ; Get(RecordNumber) - 1 ) ;

                                  If ( ProductType <> x ; 1 ; "" )

                                  )   //   end let

                                       How do I create a table with one record for each Product type?

                                  • 14. Re: Ranking?
                                    philmodjunk

                                         There are many ways.

                                         One way is to set up a unique values, validate always validation rule for the Product type field in this new table, then import all your records into this table. The validation limits the data import to one instance of each unique value.

                                         With such a table, you can set up a relationship to compute your subtotals for each product type, but have one and only one record for each product type to rank.

                                    1 2 3 Previous Next