How to Find Highest Score and Name ?

[jackoooooo]

             A             B

1       Peter          60

2       Tony           70

3      Johnny        80

4       Mary           50

 

 

IN Excel, I can use following forumla to fin out highest score person,

 

=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0))

 

But it will not work in filemaker, any solution?

[comment]

Well, Filemaker is not a spreadsheet, but a relational database. What this means - among other things - is that you can't just ask "how to get the name of the person with the highest score".  You must also say in which context is the score highest (within the current found set, in all records of the current table, or among the related records from another table) and for what purpose (for displaying it on the screen, for inserting it into a calculated value, for creating a relationship to or from this person, etc).

 

Otherwise there are just too many possible answers, which may or may not fit your situation.

 

Note also that "the highest score person" is not necessarily a single person - so you need to say how you want to handle that too.

 

--

 

P.S. Please update your profile to reflect your version and OS.

[jackoooooo]

ok, so if I have database which is include ( StudentName ; Mark )


StudentName Mark
Tony 60
Peter 70
Johnny 80
Mary 50

I use forumla                HighestMark = Max(Mark)                            to find out 80 is highest

But how can I find "Johnny" is the person who get it

[Aussie John]

Im sure there are better ways to do this but here goes…

 

This will require 4 fields

Mark, StudentName, MaxMark and your answer - ListMaxMarkStudent

 

Create two self join table occurrences

TableResult::Mark---X----TestResultMax::mark ("X" is an always join option but his can be altered to suit the structure of your database  e.g. if you are having multiple tests). This of course is fundamental to Comment's concerns of context.

 

TableResult::Mark----=----TestResultList::MaxMark

 

MaxMark would be a calculation Max(test result Max::mark) evaluated from TestResult (result as a number)

 

ListMaxMarkStudent is also a calculation  List(TestResult::StudentName) evaluated from TestResult List. (result as a number). To see an instant answer do not store the result (in storage options)

 

This field will list ALL students who equals the top mark.

 

 

Example file attached

[LaRetta]

Hey, John, no file attached!?

[LaRetta]

Hey there, Jack!  Welcome to FMForums!!

 

Comment asked some very important questions:

 

You must also say

 

- in which context is the score highest (within the current found set, in all records of the current table, or among the related records from another table)

- for what purpose (for displaying it on the screen, for inserting it into a calculated value, for creating a relationship to or from this person, etc).

- note also that "the highest score person" is not necessarily a single person - so you need to say how you want to handle that too.

 

Otherwise there are just too many possible answers, which may or may not fit your situation.

 

In providing these answers, we can make a suggestion as to the best approach otherwise we are simply using a dart board.  Seasoned FileMaker veterans (such as Comment), KNOW the correct questions to get the information they need to give you the best approach so it is helpful to answer questions specifically.  :-)

[Aussie John]

Hmm will try attaching again!!

TestResult.fmp12.zip