Field using List Function

[roemere]

I need to find the sum of the 4 lowest scores from a range of scores. I have created a field called "o1scores" that uses the "List" function to store list of all of the individual scores from the various individual score fields (p1score, p2score, etc). How can I use this new "o1scores" field to find the for lowest scores and then sum them up?

 

For example:

 

I have a field called "o1scores" where o1scores = list (p1score ; p2score ; p3score ; p4score ; p5score ; p6score).

p1score, p2score, etc., are numeric fields that store an individual player's score

 

How can I sort "o1scores" from lowest to highest, pick out the 4 lowest scores, and find the sum?

 

Thanks!

[jbante]

First, get a custom function for sorting numbers, like this one. Then:

 

Let ( [

~sorted = NumberSort ( 01scores ) ;

~lowest = LeftValues ( ~sorted ; 4 ) ;

// strip trailing return

~end = Get ( UUID ) ;

~lowest = Substitute ( ~lowest & ~end ; [ ¶ & ~end ; "" ] ; [ ~end ; "" ] ) ;

~sumCalculation = Substitute ( ~lowest ; ¶ ; "+" ) ;

~sum = Evaluate ( ~sumCalculation )

] ;

~sum

)

[mr_vodka]

Seems to me like you may have a structural problem. Any reason why these aren't in a separate child table?

[roemere]

I am new to FMPro...what I have is an Excel spreadsheet that uses the SMALL function to find the four smallest values in a column and add them up. I have some other things I want to do with the data, and all the "Excel gurus" I have talked to keep telling me "Excel is not a database, you need a database to do that". So I figured I would try to create a database that does this.

 

The goal is to score a golf meet (6 players each team, lowest 4 scores count in team total), but also keep season-long statistics (multiple meets) for my golfers. Finding the 4 lowest scores is extremely easy in Excel using the SMALL function. Is there a similar function in FMPro? I see FMPro Advanced lets you create custom functions---how would I create a custom "SMALL" function similar to Excel's SMALL function?

[David Jondreau]

You don't really want to make a Small function. You want to use FileMaker like a relational database, not like a spreadsheet. That means having at least two tables, one for meets, and one for scores (and probably one for golfers too). Once you have those tables in place with proper relationships, a world of possibilities opens up.

[bruceR]

Absolutely the wrong question.

 

You are asking "How can I use FileMaker so that I don't understand it as a database and don't get any benefit from using it as a database."

 

Mr. Vodka (and David) are correct. You have a data structure problem and that is by far the more important thing for you to learn at this point.

 

You'll need to describe more about what you're doing. But it looks like you need a table of players; and a table of games; a table of game score Items; and a table of Player Game Scores.

 

The player table would have a player name and playerID. The Game table would have Game Name and GameID. The Game Score Items would have GameID, GSI_ID, and a description. The player game scores would have PlayerID; GameID, GSI_ID; and score.

[jbante]

Everyone's right about good data normalization, but in defense of roemere, that doesn't do much to solve the original calculation challenge except to change the calculation of o1score from List ( field1 ; field2 ; etc. ) to List ( relatedField ).