6 Replies Latest reply on Dec 10, 2012 11:08 AM by linuxpad

Calculate Value based on a number..

Title

Calculate Value based on a number..

Post

Developers..

Bare with me as I am new here..

I am looking to change a field based on totals of another field.  I have all the formulas working execpt the last one.

If ( weight_lifted  <="1095";"0 Degree Angle";If (weight_lifted <= "1252";"22 Degree Angle"; If (weight_lifted  <= "1506";"30 Degree Angle";If (weight_lifted  <= "2122";"45 Degree Angle";If (weight_lifted  <= "3240";"60 Degree Angle";If (weight_lifted  <= "5790";"75 Degree Angle";If (weight_lifted  <= "8583";"80 Degree Angle";If (weight_lifted  >= "8584";"To Much Weight"))))))))

Here is what I am attemping to do..  if the value of the field is less than 1095 show 0 degree angle..

Here is the problem  If the value is 11095 is still shows 0 degree angle..  Any advice?

JP

• 1. Re: Calculate Value based on a number..

I had a similar problem (im also new to FM) and I solved it with the "case formula"
So maybe like:

Case ( weight_lifted>0 AND weight_lifted<=1095; "0 Degree Angle";
weight_lifted>1095 AND weight_lifted<=1095; "22 Degree Angle";        etc....

Im just "guessing" the actual sytax, but I think you get the idea.

I also read somewhere else (sorry forgot the link now), where you can created another table
MinWeight
Max Weigt
Comment

And it somehow knew how to find the correct response  (maybe it was the "lookup" function??)
Sorry I can't be clearer, as Im beginner as well.

Cheers!

• 2. Re: Calculate Value based on a number..

Case goes with the first expression to evaluate as true, so you can use a case function written like this:

Case ( weight_lifted  <="1095";"0 Degree Angle";
weight_lifted <= "1252";"22 Degree Angle";
weight_lifted  <= "1506";"30 Degree Angle";
weight_lifted  <= "2122";"45 Degree Angle";
weight_lifted  <= "3240";"60 Degree Angle";
weight_lifted  <= "5790";"75 Degree Angle";
weight_lifted  <= "8583";"80 Degree Angle";
"Too Much Weight" ) // this last value is returned if none of the comparisons evaluate as True.

But there are also other approaches that do not use a calculation, but rather look up the angle from a related table. Using a related table allows you to update your values without needing to modify a calculation--thus making the update a data entry task instead of a database redesign task.

• 3. Re: Calculate Value based on a number..

I tried the using case instead of the If statement but I get the same results..  It's like it does not take the other number colums into consideration..??
anything over 10K basically just starts over..

If this a FM bug?

• 4. Re: Calculate Value based on a number..

Go and clear ALL those quotes around numbers like:

weight_lifted  <= 1095 ; "0 Degree Angle"

because "2" ( as text ) > "11" ( as text )

• 5. Re: Calculate Value based on a number..

Good catch raybaudi, I was a bit too quick to copy and paste your original expression and failed to delete the quotation marks. The corrected expression would be:

Case ( weight_lifted  <=1095;"0 Degree Angle";
weight_lifted <= 1252;"22 Degree Angle";
weight_lifted  <= 1506;"30 Degree Angle";
weight_lifted  <= 2122;"45 Degree Angle";
weight_lifted  <= 3240;"60 Degree Angle";
weight_lifted  <= 5790;"75 Degree Angle";
weight_lifted  <= 8583;"80 Degree Angle";
"Too Much Weight" ) // this last value is returned if none of the comparisons evaluate as True.

• 6. Re: Calculate Value based on a number..

Thanks Phil and Raybaudi..  Works like a charm..