Find Greatest Value in a record

[jhomer]

if I have a record with 30 fields and i wanted to know which field had the largest numeric value (i would like to know the value and the name of the field containing it) how would i go about doing so?

[Ender]

You could use a script to loop through the fields on a layout, setting a global with the max value, but it's simpler to do comparisons and reporting of multiple values if they are in separate (related) records.

If you used a portal for such fields, you could simply sort the portal by the field.

[jhomer]

Thanks for the input, I was thinking of going the script route but i wanted to post and see if any unconventional methods surfaced, any other ideas out there?

[comment]

Getting the largest value is relatively easy, if tedious:

Max ( field1 ; field2 ; field3 ... )

Knowing which field it came from is more complicated. You COULD write a similarly long Case() calc like:

Case (

maximum = field1 ; "field1" ;

maximum = field2 ; "field2" ;

...

)

Note that field names are hard-coded into the calc as text strings - if a field name is changed, the calc must be updated. Overall, I too think that placing the values in a related table would be preferable.

[Søren Dyhr]

As Mike suggests is the bare number of fields per record, perhaps is the issue calling out for a more structuralized approach.

I have therefore made a template, that here are using portals, which fortunately can be cut up surrounding each field in a seemless manner. The entire point is to facilitate the solution with enough prefab. empty records each with a field that can act in the particular layout.

I could have chosen to use double criteria relations, from the autoenter and the propper "spacing" of 30 values more, and then incremented with the same 30. But they are still a slower option than equi-joins, eventhough it isn't as bad as it were with fm7. But Daniele Raybaudi kind of stumbled over this convenient way autoenter in series of 30.... in an tread some time ago.

--sd

otherway.zip