9 Replies Latest reply on Feb 28, 2017 11:37 PM by ienalpe28

# Create Ranking by Calculation

Hi All

Im a beginner in filemaker and I just want to ask how to get ranking of fields. I have 10 fields which is:

Average1

Average2

Average3

...

Average10

Each of them has an average score. I want to get the top 5 from their score by putting it on another field calculation which is Top1, Top2... Top5.

I got the Top1 by this calculation:

Top1 = Max ( Average1, Average2, Average3 ... Average10 )

But I have a difficulty in getting the Top2 ... Top5

Here is an example:

Average1 = 5.0

Average2 = 3.2

Average3 = 8.5

Average4 = 6.5

Average5 = 4.8

Top1 should be 8.5

Top2 should be 6.5

Top3 should be 5.0

Top4 should be 4.8

Top5 should be 3.2

Thanks

• ###### 1. Re: Create Ranking by Calculation

Hello.

The Max function is only going to return the top value. So we have to look elsewhere.

What we need is something that will sort the values in descending order.  Do you have FIleMaker Pro Advanced? If so, you can use a Sort List Custom Function that will sort a list. Have you worked with Custom Functions before?

I built a demo and put in a list of all values in the 10 fields:

```Let ([
_theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
_SortedList = SortList ( _theList ; "Des"; "Number")
```

Then I simply culled the top 5 off that list.

I used the nice FileMaker function: Left Values, which takes a number of values from the left (top) of the list.

In my calculated field, I have this complete calc:

```Let ([
_theList = List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10;
_SortedList = SortList ( _theList ) ; "Des"; "Number")

];

LeftValues ( _SortedList ; 5)
)
```

This does as expected: returning the top 5 values.

---

The above works as you describe . . .

However, you should give some thought to putting those averages into a child table, one new record for each score. That would be the 'proper' way to do it. Anytime you have fields with the suffix of 1, 2, 3 . . . . you find a strong clue to using related child records. I'll put together a small demo to illustrate this.

• ###### 2. Re: Create Ranking by Calculation

IF you want one field per "TopAverage", then you'll have to do some work to cull from the list.

Review the functions LeftValues and MiddleValues.

Each TopAverageField would have the same calculation as my original example but use these functions to take one row, depending on the fieldName.

So Top1 would be

```Let ([
_theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
_SortedList = SortList ( _theList ; "Des"; "Number")

];

LeftValues ( _SortedList ; 5)
)
```

Top2 would use the MiddleValue function in line 7

```Let ([
_theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
_SortedList = SortList ( _theList ; "Des"; "Number")

];

MiddleValues ( _SortedList ; 2; 1)
)
```

Top3 would be:

```Let ([
_theList =  List ( Avg1;Avg2;Avg3;Avg4;Avg5;Avg6;Avg7;Avg8;Avg9;Avg10);
_SortedList = SortList ( _theList ; "Des"; "Number")

];

MiddleValues ( _SortedList ; 3; 1)
)
```

And so forth. Notice for Top2 and Top3, I'm using the middleValues, which asks me first for the list, then the starting row, and how many rows after that.

• ###### 3. Re: Create Ranking by Calculation

It would be simpler to put each average in a separate related record. Then simple sorting can return the top values. This can be thru a sorted found set, sorted portal, sorted relationship or a sorted ExecuteSQL function call.

• ###### 4. Re: Create Ranking by Calculation

See the attached pic. I've created a child table called Tests and have all the scores in there for a student in my example.

There's a relationship that sorts them by the average score descending. And then I can pick the values that I need out of a calculated field called "Top5Scores"

As I mentioned above and Phil said is 'much more simple' I would recommend this approach. Again, any data in fields that end in a number is better put in a child table.

This approach is all FM; that custom function i mentioned is not needed.

See my example.

• ###### 5. Re: Create Ranking by Calculation

Where do these averages come from?

Did they originate from other FileMaker data?

It would be most helpful if you would provide a lot more description.

• ###### 6. Re: Create Ranking by Calculation

Hi jbrown thank you for your response. I have not tried custom function in Filemaker although I have a Filemaker Pro 14 Advanced. Sorry but I'm really a beginner. Can you please give me a step by step instruction on how to integrate this custom function. I did it myself but it shows "Error: Check sort and type". I would be grateful for your assistance.

• ###### 7. Re: Create Ranking by Calculation

I got this error first as well. Check out my demo file in a response below and look at the table called RankAverages.

• ###### 8. Re: Create Ranking by Calculation

As they wrote, having 10 fields is not best design usually.

I played with SQL...

You need "id" (unique non-null) field to determin current record in SQL.

LeftValues(

ExecuteSQL("

SELECT average1 FROM sortinrec WHERE id=?

UNION ALL SELECT average2 FROM sortinrec WHERE id=?

UNION ALL SELECT average3 FROM sortinrec WHERE id=?

UNION ALL SELECT average4 FROM sortinrec WHERE id=?

UNION ALL SELECT average5 FROM sortinrec WHERE id=?

UNION ALL SELECT average6 FROM sortinrec WHERE id=?

UNION ALL SELECT average7 FROM sortinrec WHERE id=?

UNION ALL SELECT average8 FROM sortinrec WHERE id=?

UNION ALL SELECT average9 FROM sortinrec WHERE id=?

UNION ALL SELECT average10 FROM sortinrec WHERE id=?

ORDER BY 1 DESC

";"";"";id;id;id;id;id;id;id;id;id;id)

;5)

• ###### 9. Re: Create Ranking by Calculation

Hi sorry I was away for a few days. I just want to thank Jeremy Brown for giving the correct answer to my problem. Thanks buddy! If I can only buy you a drink I would. This project is for an appraisal system. My wife is doing it manually, the computation part is very tedious and consumes a lot of time when she is about to make her report. I want to surprise her by doing this solution so she can spend more time with me . Anyway thanks again guys for the help specially to Jeremy Brown. Thanks again buddy.