Whats the quickest way to do this I'm having a mental block!

6 Fields in a record, each field contains a number, how can I use conditional formatting to highlight the top 3 Values?!?

Eg:

Field1 Field2 Field3 Field4 Field5 Field6

23       25        21       28       20       90

 

Highlight Fields 2, 4 and 6

 

 

Edited November 16, 20169 yr by liltbrockie

conditional format, calculation with a Let() that does this:

 

- use List(Field1 ; Field2; ... ) to create a list in a calc variable

- use any of the custom functions to sort the list

- then an evalation wither self = getvalue( sorted_list ; 1 ) or self = getvalue( sorted_list ; 2 ) or self = getvalue( sorted_list ; 3 )

 

 

As an aside: when you start using sequentially numbered fields, it usually means there is need to break that out into a related table.

 

This is going to be awkward any way you look at it, because Filemaker has no mechanism for sorting fields. Obviously, these values should be in individual records of a related table - and I am not saying this as an aside. Even a repeating field would be better than 6 individual fields.

If I had to do this with your current structure, I would compare each field with all its 5 siblings, e.g. Self > Field 2, Self > Field 3, etc. Then, if 3 or more of these comparisons returned true, the field is in the top 3 (leaving aside the problem of ties).

Edited November 16, 20169 yr by comment