If you have all these records in a single found set, sorted in descending order by Total, then this unstored calculation named "Rank" can report the rank:
Case ( get ( RecordNumber ) = 1 ; 1 ;
Total = GetNthRecord ( Total ; Get ( RecordNumber ) - 1 ) ) ; GetNthRecord ( Rank ; Get ( RecordNumber ) - 1 ) ) ;
Get ( RecordNumber )
You have to get sneaky to define this field.
Add a new number or text field named Rank and click Create. That adds "Rank" to the field list so that the calculation can refer to itself.
Then change the field type to Calculation and add in the above expression. Be sure to click storage options and specify the "do not store..." option.
This produces the rank number but not the ordinal suffix. Get the above calculation working for you first, then add this calculation field to properly format the Rank:
cFormattedRank: Rank & If ( rank < 4 : Choose ( Rank ; "" ; "st" ; "nd" ; "rd" ) ; "th" )
i created a new field called" Rank" and change the field type to calculation ,then in the specify calculation dialogue box i copied and paste the above expression exactly as it appears but when I click Ok it gives me an error saying " An operator (e.g. +,-,*,....) is expected here then returns back to the specify calculation box. Is the first expression above a custom function? I may be doing something wrong If possible help me with a sample file
i'm still struggling with it but can't get it work
There is no custom function in this calculation, but I could have included a typo in my sample calculation. How is the expression highlighted when you get that error message?
This part get highlighted in the expression you gave me above ; GetNthRecord ( Rank ; Get ( RecordNumber ) - 1 ) ) ; Get ( RecordNumber ) ) // From this post. http://forums.filemaker.com/posts/2b39b2625a I got this but it only works when the records are sorted in descending order by Total. If ( Get ( GetNthRecord ) = 1 ; 1 ; Let ( [PrevRec = Get (GetNthRecord )) - 1 ; PrevTotal = GetNthRecord ( AMOUNT ; PrevRec ) ; PrevRank = GetNthRecord ( RANK ; PrevRec ) ] ; If ( PrevTotal = AMOUNT ; PrevRank ; Get (GetNthRecord )) ) ) ) But when I unsort it back the rank doesn't follow each total I could have love it to work without sorting all the time Don't get tired pls. I'm just a beginner
but it only works when the records are sorted in descending order by Total
That is correct. I stated that limitation in my original post.
To get the ranks to "stick", you can find/sort your records in the needed order and use Replace field Contents to assign these ranks to a simple data field, but then you will lose the ability to see them change when you edit data that produces a new total for that individual. After each edit of a value, you'd have to run a script or manually do the steps to repeat the update process.
thanks phil I will do it just as yoou said Thanks again