I want extract the Max Value of a list in a field

[Cannoso]

I want create a custom function to extract the maximun value of a list of values in a field in only one record.

example: if i have in field "data" the data

6.7

8.3

12.6

7.5

8.4

i want result 12.6

I do not finish understanding as the recursivity works in custom functions

any idea?

[transpower]

Use the built-in Max function; you don't need to construct a custom function. From FM Help:

Max function

Format

Max(field{;field...})

Parameter

field - any related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields.

Parameters in curly braces { } are optional.

Data type returned

text, number, date, time, timestamp

Description

Returns the highest valid value in:

a repeating field (repeatingField).

a field in matching related records specified by (table::field), whether or not these records appear in a portal.

several non-repeating fields in a record (field1;field2;field3...).

corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats.

several fields in the first matching record specified by (table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...).

[Ender]

transpower,

I suspect Cannoso's number list is in a return delimited text field and not a repeating field. In this case, Max() would not work.

Cannoso,

These functions seem to work as long as there's no blank lines in between the numbers in the list:

MaxValue(NumberList)=

//MaxValue(NumberList)

//by Mike Hackett

//11/8/04



//Given a return delimited list of numbers, returns the highest value.



Let(

NumberList = Trim(NumberList);

MaxValueSub( RightValues(NumberList; ValueCount(NumberList) - 1); GetAsNumber(LeftValues(NumberList;1)))

)

MaxValueSub(NumberList;MaxSoFar) =

//MaxValueSub(NumberList; MaxSoFar)

//by Mike Hackett

//11/8/04



Let(

[

FirstNumber = GetAsNumber(LeftValues(NumberList;1));

NumberList = Trim(RightValues( NumberList; ValueCount ( NumberList ) - 1))

];

If(

IsEmpty(FirstNumber); MaxSoFar; MaxValueSub( NumberList; If(FirstNumber > MaxSoFar; FirstNumber; MaxSoFar))

)

)

If there could be blank lines in the list, it wouldn't be too hard to adjust the function.

[transpower]

I see what you're saying. But I think it would be easier to create a repeating field (with at least five repetitions) and Max that. So, for instance:

Let ( [Repeat[1] = MiddleValues ( Data ; 1 ; 1 ) ;

Repeat[2] = MiddleValues ( Data ; 2 ; 1 ) ;

Repeat[3] = MiddleValues ( Data ; 3 ; 1 ) ;

Repeat[4] = MiddleValues ( Data ; 4 ; 1 ) ;

Repeat[5] = MiddleValues ( Data ; 5 ; 1 )

] ;

Max ( Repeat )

)

[Ender]

I think the advantage to using a text field with values separated by returns would be to allow a wide variance in the number of values. With a return separated list, you can keep adding numbers without worring about exceeding the size of the array. This benefit would be lost if you had to use a repeating field to perform functions on the list.

Of course, we don't really know why Canosso has chosen this structure, or how it will be used. For dynamic lists, portals are usually used.

[transpower]

With my method he can still use the text field (data) and a calculation field as I've presented it. The repeating field would be as large as necessary (with as many lines as necessary in the calculation field), and could be on a hidden Developer layout.

[Ender]

And somehow this is easier than a custom function?

[Cannoso]

Yes, i have a return delimited values list in the field, and i clean this previously if necesary with other function.

I dont want reapeating fields, and i found more elegant and easy the solution from Ender, but thank you very very much to both

This also serves to try to understand me the recursivity in custom

functions

It was trying to do it with only one function. It is possible?

Thanks again to both

[Ender]

It was trying to do it with only one function. It is possible?

Not without adding the MaxSoFar parameter to the initial function call (You could just call the MaxValueSub() function with the number list and zero as the parameters. But if you ever have all negative values in the number list, you'll have problems.) It's better to use the two functions together.

[transpower]

Forget using LET with actual fields; it can only be used with variables. So I come back to just making "data" a repeating field of numbers and using the Max function. Simplest is best. But, Cannoso, what is the reason you don't want to use a repeating field here?

[-Queue-]

I would probably use a repeating calc of

MiddleValues( textfield; Get(CalculationRepetitionNumber); 1 )

and then a simple calc of Max(repcalculation).

repcalculation can be defined to have 32000 repetitions. So set it with more than you anticipate needing and you should be okay.

[Cannoso]

I need to maintain this field like this for other purposes. With the

solution of the repeated field, the data is duplicated in both fields. Also

hatred the repeated fields.

I check the functions of Ender and work properly. Thanks

[TerenceM]

A little late to make this suggestion, but you can also do this as:

Evaluate ( "Max ( " & Substitute ( data ;

[Ender]

That's pretty clever. Unfortunately I can't get it to work when any of the numbers has a decimal. Any thoughts?

[TerenceM]

It works on my end with decimals, but there are definitely problems if you include blank lines. One solution is to remove any invalid characters using Filter, and either use a validation or substitution function to remove blank lines. I can't think of a way to remove blank lines without a recursive custom function, but checking for them in a Validation calculation is easy enough. See attached file for a working example.

-Terence

maxvalue.fp7.zip

[Fenton]

Some enterprising soul, in the ancient days before 7, figured out just how may

[Ender]

Got it working Terence. The file I had tested it in had a different language setting that was screwing with the decimals.

Fenton, I like your trick for filtering out blank lines!

[Fenton]

I can't take any credit for it. There was a fascinating (to math geeks) explanation of just why that is the optimal pattern; but I seemed to have lost it. I did find another bit; he said that this additional line, of 21, will do up to 460 returns (or whatever) in a row:

Substitute ( InputText,

[ "

[Lee Smith]

Hi Fenton,

The credit belongs to Bob Weaver. He posted it in response to this Thread. There is some other helpful information in the thread also that might be of interest.

Lee

[TerenceM]

What a useful tidbit to know -- I hate having extra spaces/paragraphs sitting around, and that's the perfect way to get rid of them. Now I'll have to sit down and justify the math to myself.

-Terence

[Søren Dyhr]

There was a fascinating (to math geeks) explanation of just why that is the optimal pattern; but I seemed to have lost it.

What a shame, unfortunately is my remembrance of inductive proofs of a similar hazy nature - Trying to grasp it again this afternoon didn't give much success. So if anyone here can explain it in a jif ...please don't hessitate.

--sd