Choosing greatest number in multiple fields

[J.P.]

Hi,

I need to know if its possible to create a caculation field that compares mutiple of number fields and copies the greatest number to itself.

Example, I have "5" on "field_1", "10" on "field_2" and "15" on "field_3" last calculation field identifies which field contains highest number and copies into itself.

Please give me some idea how this caculation field can be created.

Thanks in advance!

[Vaughan]

Stuff like this is much easier when done with related records.

Many situations that use multiple fields to hold similar values are usually better off relational, because it's usually a many-to-many relationship.

Looping through related records is dead easy to script. It may even be possible to get the result with a calculation or summary field which will give the result instantly without having to generate it each time something changes.

[danjacoby]

Another, rather kludgy way is to use the "Case" statement -- it's a way of stringing together multiple "If" statements. If you only have three or four fields to compare with each other, it's fairly quick. If you have, say, 10 fields, I'd say Vaughan is right: find a way to use relationships.

[Thom]

Use the Max() function:

maxNum(calc,number) = Max(field_1, field_2, field_3)