Finding Highest value within multiple fields

[Zradene]

Hi guys, I have 8 numeric fields on a form with different values. I need to select the field with the highest value, and use this value in a calculated field.
Any idea's ?

Thanks
Fred

[Stan Mathews]

Re: Finding Highest value within multiple fields

Use max().

Hint: You can "nest" max() expressions like you can if()

highest = max(field1,max(field2,field3))

[Zradene]

Re: Finding Highest value within multiple fields

Thanks Stan, do I still have to create a massive expression to compare the 8 fields with all the various possibilities?
Or does this max allow me to just specify the 8 fields in the expression and it somehow works out the highest value.
Sorry to sound a bit thick, but I am not sure how this max function works.

Fred

[MikeC]

Re: Finding Highest value within multiple fields

Fred,
If you don't use the interactive editor, then definitely start!

Here is an example doing just that. The question mark prior is used just for the interactive editor to show a result==the actual expression is after it.

dim fld1 as n=11
dim fld2 as n=5
dim fld3 as n=8
dim fld4 as n=4
dim fld5 as n=55
dim fld6 as n=6
dim fld7 as n=8
dim fld8 as n=3

?max(fld8,max(fld7,max(fld6,max(fld5,max(fld4,max(fld3,max(fld1,fld2)))))))
= 55

I simply started with a single max() ---max(fld1,fld2), then used this result in a second max()---max(fld3,max(fld1,fld2)....and so on. You are just comparing the first result to the next.

Take a look at the online help too --

http://support.alphasoftware.com/alp...ions/MAX().htm

[Zradene]

Re: Finding Highest value within multiple fields

Hi Mike, I shall certainly take a look at the interactive editor window.
I shall give this a go right now.
Thanks for your help.

Fred

[Zradene]

Re: Finding Highest value within multiple fields

The interactive editor is great for testing out expressions & code.
Never used it before, but it really helped me sort out my problem.

Thanks for suggesting it to me.

[Zradene]

Re: Finding Highest value within multiple fields

Hi again Mike, is it possible to use this max condition to rank all 8 fields from Highest to lowest? Iv'e had a go at this, but with no success as it looks very difficult.

Fred

[Mike Wilson]

Re: Finding Highest value within multiple fields

Fred,
The attached contains a user defined function (UDF) named rank_list() that generates the a rank of the values in 8 fields on the form and displays it in a text box. I used the strategy of generating a list of the field values with attached field names, and then sorting the list based on the field value. See if this helps.

FUNCTION rank_list AS C ( )
	
dim vL as C=""
dim t as P
t = table.get("rank")

dim vFN as N
dim vFC as C
for i = 1 to 8
	vFN = eval("topparent:F"+i+".value")
	vFC = alltrim(str(vFN))
	vFC = padl(alltrim(vFC),4,"0")
	vL = vL + vFC + " - " + "Field-" + i + crlf()
next
vL = sortsubstr(vL,crlf())

dim vrank as C
for each foo in vL
	vrank = vrank + word(foo.value,2," - ",1) + " - " + stritran(word(foo.value,1," - ",1),"0","") + crlf()
next

parentform:text1.text = vrank
	
END FUNCTION

[Zradene]

Re: Finding Highest value within multiple fields

Thats great Mike. That sorts out the ranking order just fine.
My last task is to assign the values to fields which in turn will have a corresponding value.
ie..
Field 5 (Highest Rank) would = 2.16
Field 1 = 2.20
Field 2 = 2.24
Field 8 = 2.28
Field 3 = 2.32
Field 4 = 2.36
Field 6 = 2.40
Field 7 = 2.44

Any idea's Mike ?

[Mike Wilson]

Re: Finding Highest value within multiple fields

Fred,
You lost me, I think. But to get ascending order with two decimal places as you have there, change line 11 of the UDF to be:

vFC = alltrim(str(vFN,10,2))

and line 19 to:

vrank = vrank + word(foo.value,2," - ",1) + " - " + padr(stritran(word(foo.value,1," - ",1),"0",""),4,"0") + crlf()

[Tom Cone Jr]

Re: Finding Highest value within multiple fields

Fred,
<soapbox on> Whenever you start putting multiple fields in the same record and those fields all have something in common, you should begin to rethink your design. Here your problem becomes trivial if you put the numeric fields in a separate table, one numeric field per record. You can then sort that table as needed. Stacking fields horizontally, by adding them to new "columns" for each record is what spreadsheets do well. Relational databases shine if you use them as databases, and not as spreadsheets. <soapbox off>.

[Stan Mathews]

Re: Finding Highest value within multiple fields

I am not sure how this max function works.

Puzzling. Is the documentation not available to you or is there something in the documentation that isn't clear?

MAX()

Largest_Number as N = MAX( ListNumber as N, Number2 as N )

Argument   Description
 
ListNumber A number.
 
Number2    A number.
 
Description

MAX() returns the larger of two numbers: ListNumber or Number2.

[Zradene]

Re: Finding Highest value within multiple fields

Hi Stan, it's clear now...
Thanks again.

[Zradene]

Re: Finding Highest value within multiple fields

Hi Tom, the 8 fields I want to rank are actually the (tablesum) totals of 8 numeric fields, which are in just one table. I am trying to assign values to 8 other fields based on the ranking order of these 8 total fields.
Hope this makes sense.
Fred

[Zradene]

Re: Finding Highest value within multiple fields

Hi Mike, once I have ranked the 8 fields, which are actually tablesum totals, I then need to assign values to 8 other fields based on the ranking order.
Sorry for the confussion.
Fred