Multiple values in a single field, how to get the highest

[aharown07]

The Max function is not behaving as I expected. What I have is a "month" field that is number field. It accepts multiple values using checkboxes in the layout.

But I have a script in which I want to take only the latest (highest number) from that field, assign to a variable and do stuff with it.

But if the field has, say, 1 2 3 4 5 6 7 8 9 in it, Max gives me 123456789 rather than 9.

So how can I consistently get the high number out of there?

I've played with GetValue a bit, but that solution is a bit fragile, because the user could select the month number checkboxes in any order... meaning the highest is not always in a particular location in the series.

I haven't done much with multivalue fields before this so... I'm probably just missing a concept or two.

(I'm in FMP advanced 14)

[comment]

This is not a trivial problem. The Max() function is behaving as expected; it is designed to process values in multiple fields or multiple related records or multiple repetitions of a repeating field - not a list of return-separated values.

Here's one way you could solve it =

Let ( [
countValues = ValueCount ( YourCheckboxField ) ;
sortedValues = FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueList" ) ; Months )
] ;
GetValue ( sortedValues ; countValues )
)

Note that this does not really get the highest value in the checkbox field; it gets the last value, in the order of the value list - but we assume here that your value list lists the months in ascending order.

 

1 hour ago, aharown07 said:

What I have is a "month" field that is number field. It accepts multiple values using checkboxes in the layout.

If it accepts multiple months, then it needs to be a Text field.

 

Edited September 10, 2016 by comment

[aharown07]

Thanks. I believe your value list approach will do the job in a pretty bulletproof way. Only changing the value list could break it.

FM should edit the help documents a bit on the Max function. It reads 'Returns the highest valid value in a field.'

This is not really quite what it does.... maybe "Returns the highest valid value among a set of fields" or something...

All the examples are multiple field scenarios, but I was hoping the single-field-multiple-values scenario might have simply been omitted for brevity or maybe was too simple to need examples. Alas...

Anyway, thanks.

 

Edited September 10, 2016 by aharown07