1 2 Previous Next 17 Replies Latest reply on Aug 23, 2012 4:20 AM by tomo17

    Ranking Script based on type



      Ranking Script based on type


      I have a carnival database that i am quite happy with but I am now trying to improve it to reduce errors in data entry by users not familiar with FMP.

      Currently i have a field where users manually enter the place given to an athlete based on their performance in that event.  What i would like to be able to do is have the database run a calculation to automatically enter the place.  The problem i have run into is that field events are ranked according to descending performance (ie 1.8 = 1st, 1.7 = 2nd etc) whereas track events are ranked according to ascending performance (12.5 = 1st, 13.6 = 2nd).

      All the results sit on an athlete result table. I have two event layouts that will apply the appropriate sort to the portal according to the performance based on the event type ie either track or field. Is there a way that i can get the the database to identify whether the event is track or field, apply the appropriate sort and then apply a rank within the portal?

      I have attached a picture of how the layout looks at present


        • 1. Re: Ranking Script based on type

          Yes.  You could put a radio button on your layout.  (Track / Field), then based on the selection it set the sort.   Or you could even name your radio buttons ascending / descending.   Then on the radio button you would have a script trigger onObjectModify to run script that sorts your records.

          Then you use the field that you linked to your radio button

          If [event="TRACK"]

            Sort Records[ascending]


           Sort Records[descending]

          End if

          The script will say restore instead of ascending / descending.  I did this to make it more understandable.  You have to specify your sort.

          • 2. Re: Ranking Script based on type

            Thanks for your reply. i do like your solution it is it different to what i use but i can get the sorting working out ok its just getting the rank to function correction that is causing the problem.  When it does the rank on events that are ascending it puts the empty ones at the top and allocates a rank to them which is not right.  It also allocates a rank in the master table according to the position in the master table rather than the event.

            • 3. Re: Ranking Script based on type

              Create another calculation field and sort the portal by it instead of the straight 'Score'.

              Call the calculation 'ScoreToSort' and have it:

              Case (

              EventName = "Long jump"
              EventName = "High jump"
              EventName = "Javelin" (etc) ;

              Score ;

              - Score


              It would be more flexible if you created a separate table for the easy addition of possible other events, but this will get you started.

              • 4. Re: Ranking Script based on type

                Here is a ranking calculation:

                Let ( r = Get ( RecordNumber ) ; //first record in portal or found set
                       Case ( r = 1 ; 1 ;
                                 GetNthRecord ( Score ; r - 1 ) = Score ; GetNthRecord ( Rank ; r - 1 ) ; //A tie. Give same rank as previous record.
                                r ) // in all other cases, record number = ranking

                • 5. Re: Ranking Script based on type

                  Thanks Phil and Sorbuster for your help.  I have been able to get the ranking working but have found that if i need to alter a record that changes the ranking order it doesnt update the points that are allocated - i have to go back and re-enter a part of the performance result for it to update. do i need to set up a script trigger or something similar.  A second related problem is that i wil import results from other users and when i do so it will have the overall record rank of the database (ie 1143) rather than the rank for that event.


                  Thanks again


                  • 6. Re: Ranking Script based on type

                    You may need to trigger a commit record, a refresh window or both after modifying a score. The order of the records is also critical so if you use a list view layout instead of a portal, you may need to resort your records.

                    If the data isn't working when imported from another file, you'll need to analyze why that is. You'll first need to make sure that it is correctly imported, then see if found sets, sort orders, etc need to be updated as your Imported data will produce a new, unsorted found set of all records imported.

                    • 7. Re: Ranking Script based on type

                      Thanks Phil

                      The results are in a portal that re-sorts when an object is modified it just doesnt change the rank or points - should the trigger happen when the field for the record is modified or when the object (i am assuming this means the portal) is modified? do i need to flush cached results as well?


                      I will have another look at the importing - i am sort of hoping that once i fix this issue up with the ranking it will go some way to fixing the import issue as well.

                      • 8. Re: Ranking Script based on type

                        I would experiment with an OnObjectSave trigger on any field that is used in your calculations that aren't updating.

                        I try the following combinations in this order:

                        Commit record

                        Refresh window

                        Commit Record
                        Refresh Window

                        Refresh Window [Flush cached join results]

                        If only the last option works, I'd reanalyze the entire set up to try to find a way that updates with out the refresh/flush step. This step can trigger major delays while everythign updates so it's a step best avoided and usually you can.

                        • 9. Re: Ranking Script based on type

                          Oh yes, before you try any of that, make sure that the ranking calc is an unstored calculation.

                          • 10. Re: Ranking Script based on type

                            Thanks Phil - that last piece of advice regarding the calc being unstored seems to have made a big difference with the records now seeming to update as needed.

                            I am very appreciative of your help - it is late here so i will explore further tomorrow.

                            Once again - thank you!

                            • 11. Re: Ranking Script based on type

                              Quick update.  The ranking and points allocation now works in the portal however the information is not updated in the table from which this information is recorded.  There are two tables - events and results.  The results table is a portal on a layout that is based on the events.  Your last suggestion has fixed the layout with the portal but the results table does not update with the changes made and is continue to show a rank that is an overall ranking of that record in the table of results rather than by renewing for each event.  

                              it seems that the change to the way the calc is stored has fixed one issue but created a new one.

                              Can i impose on you once again for any further suggestions

                              • 12. Re: Ranking Script based on type

                                It's inherent to how GetNthRecord works. It accesses a specific record in the found set. If you change the found set, you change the results.

                                If you perform a find to find on your results table to only find records for a given event, then sort them in the same order as your portal, you'll see the same results.

                                If you want to print out a report listing rankings/results for all your events, the getNthRecord calcualtion will need to be modified so that it returns a ranking of 1 each time the previous record is for a different event.

                                • 13. Re: Ranking Script based on type
                                  You are correct. Part of the end result is to print out a event summary report so that modification is required. Can I do this as part of the calc or would it best to do it as a script when the report is generated
                                  • 14. Re: Ranking Script based on type

                                    a further problem that i have with the sorting of results using this rank calculationis that some are sorted ascending whilst others are sorted descending depending on type of event. i get around this in layouts by having a different layout for each type of event ie field or track. is there a way that a "place" field can be set up that just gets the face value of the rank.  i could then use this to sort results by place.

                                    1 2 Previous Next