8 Replies Latest reply on Mar 28, 2013 8:19 PM by LeonLay

    Ranking records without ascending order

    LeonLay

      Title

      Ranking records without ascending order

      Post

           I've tried to search the forum to find out a way to rank a field without shorted the field, but I didn't see it.

           Here's my request: 

      Table 1 is info about client, like

           NAME │CLIENT_ID │ADDRESS │PHONE │etc.

           Tom    │0715           │XXX            │1234      │etc.

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

      Table 2 is info about users pay info

           CLIENT_ID │ QUANTITY │AMOUNT │ RANK

           0715           │16                │$19.99      │rank number

           And now put them in one layout(Table 1) to show the Client's overview, but when I put the RANK field into Table 1's layout, the RANK field desn't work( seems it only worked in Table 2).

           All I want is to short with the NAME field while it shows each NAME's Ranking number.

           Could this possible?

        • 1. Re: Ranking records without ascending order
          schamblee

               Why not use a portal to pull the clients pay info then sort the portal by Rank number? Your layout can still be sorted by Name. 

                

          • 2. Re: Ranking records without ascending order
            LeonLay

                 Hi, S Chamblee

                 I've tried use a portal, and it doesn't work, I didn't know where goes worng, can you tell me more details? Thank you!

                  

                 P.s Sorry, I click a worng link taht "Report Abuse".

            • 3. Re: Ranking records without ascending order
              philmodjunk

                   How are you determining the rank number? Is this a calculation that use GetNthRecord to compare values to the previous record or are you using a different method?

                   One option is to use a script to find and sort your records to get the correct rank number, then use Replace Field Contents to copy the computed ranks into a simple number field--which can be in the related Table 1 table. Now you have a copy of the current ranking that does not rely on the current found set and sort order.

                   BUT, the drawback to this method is that the rank number in this number field will not automatically update when data in table 2 is updated, you have to run your script each time the data changes or at least each time that you pull up this layout to see the current rankings.

              • 4. Re: Ranking records without ascending order
                LeonLay

                     Thanks, PhilModJunk,

                     Yes, I rank the filed numbers by using GetNthRecord function, it works fun in Table 2, but it doesn't work in Table 1. I'm worndering if there is any way to automatically covert the Ranking filed in to an actual number in a new filed, then I can directly use this field to Table 1.

                     The Ranking field was calculated by this:

                     If ( Get ( GetNthRecord ) = 1 ; 1 ;
                              Let ( [PrevRec = Get (GetNthRecord )) - 1 ;
                                      PrevTotal = GetNthRecord ( AMOUNT ; PrevRec ) ;
                                      PrevRank = GetNthRecord ( RANK ; PrevRec ) ] ;
                                      If ( PrevTotal = AMOUNT ; PrevRank ; Get (GetNthRecord )) )
                                    )
                         )

                • 5. Re: Ranking records without ascending order
                  philmodjunk

                       As I mentioned in my last post, you can use Replace Field Contents to copy the results of your posted ranking calculation into a number field to store the current rank so that you can reference this value without needing the context of a sorted found set of records in Table 2.

                  • 6. Re: Ranking records without ascending order
                    LeonLay

                         Hi, Phil

                         I got you idea, but I don't know how to use this Replace Content Filed function. Would you mind if you could tell me more details about that, thanks.

                    • 7. Re: Ranking records without ascending order
                      philmodjunk

                           In a script where your ranking is based on all records in the table:

                           Freeze Window
                           Go to Layout [Table2 layout]
                           Show All Records
                           Sort [Restore ; no dialog]
                           Replace Field Contents [no dialog; Table1::RankNumber ; Table2::RankCalculationField ]
                           Go To Layout [original layout]

                           If the ranking is not based on all records in the table, perform a find instead of Show All Records.

                           It's possible, to replace "Table2::rankcalcualtionField" with the actual calculation used in that field. If so, you may not need the calculation field at all.

                      • 8. Re: Ranking records without ascending order
                        LeonLay

                             Thanks Phil,

                             I've followed your direction, and it's works fine.