6 Replies Latest reply on Oct 27, 2015 4:31 PM by dudley@jps.net

# How to find 2nd Lowest

This question is about contest scoring for annual awards.  Nine monthly contests are held throughout the year.  Each contest has it's individual winner.  For season standings, we exclude the individuals two lowest scores.  Annual awards are based upon the best seven out of nine contests.  This allows for a bad day and/or missed contest.

I use the "Min" function will identify the lowest score out of the 9.  That number is a calculated result in the Field "Min 1".  I currently use a very  elaborate series of comparisons (shown below)  to identify the 2nd lowest number which is stored in Field "Min 2".  The Best 7 is then determined by a summary of all nine scores minus Min 1 minus Min2.  (Sum9 - Min 1 - Min 2)

It works but it ain't pretty.  I wondering if anyone has a simpler way to find "Min 2"?

If (Contests Flown < 9 ; 0 ;

Max(

Min(Apr;May;Jun;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;May;Jun;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;Jun;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jul;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Aug;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Sep;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Aug;Oct;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Aug;Sep;Nov) ;

Min(Mar;Apr;May;Jun;Jul;Aug;Sep;Oct)

)

)

Dudley

• ###### 1. Re: How to find 2nd Lowest

Is there a reason you can't put the nine monthly contests into a table as separate records? That would dramatically simplify the situation.

Another option would be to take the nine monthly results, parse them into a return delimited list, and use a Virtual List technique to turn them into a set of records. That would allow you simply to sort the list based on their values. It would be slower, but if you absolutely had to have the nine results all in a single table, it would be an option.

• ###### 2. Re: How to find 2nd Lowest

Mike_Mitchell wrote:

Is there a reason you can't put the nine monthly contests into a table as separate records? That would dramatically simplify the situation.

Exactly what I was thinking.

Another way:

Let ( [

myList = List ( Mar ; Apr ; May ; Jun ; Jul ; Aug ; Sep ; Oct ; Nov ) ;

theMin = Evaluate ( "Min ( " & Substitute ( myList ; ¶ ; ";" ) & ")" ) ;

newList = Substitute ( ¶ & myList & ¶ ; ¶ & theMin & ¶ ; "" )

] ;

Evaluate ( "Min ( " & Substitute ( newList ; ¶ ; ";" ) & ")" )

)

Decide if this functions correctly when you have more than one occurrence of lowest score … if you just want the pen-ultimate value, try:

Let ( [

myList = List ( Scores::Mar ; Scores::Apr ; Scores::May ; Scores::Jun ; Scores::Jul ; Scores::aug ; Scores::Sep ; Scores::Oct ; Scores::Nov ) ;

theMin = Evaluate ( "Min ( " & Substitute ( myList ; ¶ ; ";" ) & ")" ) ;

pos1 = Position ( ¶ & myList & ¶ ; ¶ & theMin & ¶ ; 1 ; 1 ) ;

pos2 = Position ( ¶ & myList & ¶ ; ¶ ; pos1 ; 1 );

newList = Replace ( myList ; pos1 ; pos2 - pos1 ; "" )

] ;

Evaluate ( "Min ( " & Substitute ( newList ; ¶ ; ";" ) & ")" )

)

While this works, it goes to show what contortions are (often) necessary with non-normalized data.

• ###### 3. Re: How to find 2nd Lowest

Well, as the saying goes; "There's more than one way to skin the cat".  I might add; they're all bloody.  I was hoping to find something simple.  Perhaps something in the basic FileMaker toolbox of functions that I'd overlooked.

I used the new layout approach some time ago, whereby the fields are made into records in a new layout. There, they can be easily sorted and moved back.  The monthly scores of each record must be "processed" or "washed" through that MacGyver approach.  Again, it works buy it ain't pretty.

So far, I've managed to come up with two cumbersome solutions.  I don't like either one of them because, well, they're ugly.

But maybe that's just the way the cookie crumbles.

There are some very creative minds out there and I appreciate the ideas.  Anxious to see if anyone has a "simple" approach.

Thanks for your interest and thoughtful ideas!

Dudley

• ###### 4. Re: How to find 2nd Lowest

Again, unless there's a compelling reason to keep all the scores as individual fields in the same record, database normalization principles would dictate that they be kept in a separate table. There wouldn't be a need to move them back and forth if this were the case; you would simply isolate the records in the table where they lived and find the information. (That's why proper normalization is done.)

So we would need to know if you have a business need to keep the records flattened in the parent table in order to recommend something other than the "right" way to do it.

• ###### 5. Re: How to find 2nd Lowest

I agree that it would be easier if the scores were in another table. That being said, sometimes, a guy just sees an interesting challenge.

You didn't specify "native FileMaker", so that can allow the use of custom functions and/or plugins. The attached file makes use of the free BaseElements plugin, which I think most people will agree is awesome. If you're going to test the attached file, download and BaseElements first. BaseElements Plugin | Goya Pty Ltd

The technique in the sample file is to make a list of the values in the 9 months for which you have contests. Then it uses a BaseElements function to sort that list and end up with no more than 7 values. It has a total score of the 7 HIGHEST values used, and an average of that. Unused scores turn red. The cutoff score is light green. Other scores are deep green.

Free and not horribly difficult. Other people will have great ideas, and I look forward to seeing them.

But again...a separate table would be easier.

• ###### 6. Re: How to find 2nd Lowest

Mike, thank you very much for your continued interest.  I sincerely mean that.

Your knowledge of FileMaker and databases in general, is WAAAAY over my head.  Some (most) of your terminology is beyond my pay grade.  I'm just a self taught FileMaker hobbyist.  I find problem solving an intriguing mental exercise and at 70, I need all the exercise I can get.

This not in any way related to a business.  I am (among other things) the scorekeeper of a radio control glider club in the Sacramento area.  www.svss.org  I was handed a FM scoring program when FM 4 was the version de jour.  I didn't know anything about databases and even less about FileMaker but soon became hooked.

The Year-To-Date scores are imported (lookup) from separate monthly contests as the year progresses.  So if I understand your terms, I guess my YTD file is a Parent Table that has monthly fields (Mar - Nov) that are populated via related tables, as our contest season progresses.  Maybe what I have is as easy as it gets.  But someone told me that in Excel, it's possible to find "nth smallest".  FM has a bazillion functions, only a few of which I'm familiar.  I was hoping that a similar function might be available in FM.  I lament the fact that there isn't a FM users group in Sacramento.  I'd love to be able to sit down with other FM users and learn from them.

On our club web site (which I also manage) you can see the end product of this exercise.  If you have a mind to, take a look at;

svss.org/ytd_td.html