1 Reply Latest reply on Feb 26, 2015 3:40 PM by philmodjunk

# Setting a field value based on another field value

### Title

Setting a field value based on another field value

### Post

I have two fields, the first field value is based on a range of values from the second field. For example the second field may have a value of 0 to 360, and the first field will assign a value of "0" for 0 to 37, "1" for 38 to 111, "2" for 112 to 185, "3" for 186 to 259 and "4" for 260 to 360. Below is an example I have tried but without success.

If ( QUIZ::Reds Score ≤ 360 or QUIZ::Reds Score ≥ 260 ; QUIZ::Reds = 4 ;
If ( QUIZ::Reds Score ≤ 259 or QUIZ::Reds Score ≥ 186 ; QUIZ::Reds = 3 ;
If ( QUIZ::Reds Score ≤ 185 or QUIZ::Reds Score ≥ 112 ; QUIZ::Reds = 2 ;
If ( QUIZ::Reds Score ≤ 111 or QUIZ::Reds Score ≥ 38 ; QUIZ::Reds = 1 ;
If ( QUIZ::Reds Score ≤ 37 or QUIZ::Reds Score ≥ 0 ; QUIZ::Reds = 0 ; 0)))))

• ###### 1. Re: Setting a field value based on another field value

A case function can be much easier to work with than a series of nested if functions.

But I don't follow the logic to your range checking. Any value that you choose will be either less than or equal to 360 or greater than or equal to 260.  The value 0, for example is less than 360 so your first expression is true and you get a result of 4. The value of 1 million will be greater than 260 and thus your first expression is still true and you get a result of 4.

I think that you need to use AND where you have used OR.

Let ( S = Quiz::Reds Score ;
Case ( S < 361 and S > 259 ; 4 ;
S > 185 ; 3 ;
S > 11 ; 2 ;
S > 37 ; 1 ;
0
) // case
) // let

Note that Quiz::Reds = 4 is a Boolean expression that evaluates to a value of either 1 (True) if the value of Reds is 4 or 0 (False) if it is not.

And even better approach would be to store these values and ranges in a table and use a look up scheme to look up the values 0...4 depending on the value of Reds Score used to look them up. With that approach, you can change both the ranges and the values returned by editing a table instead of modifying your calculation.