I have 4  numbers.. 25.25, 25.5, 22.25, 22,5

If variable a  is lower than 25.5 but not  any of the 4 numbers in the list,  then add $10.00  to B.

or

or if variable a is higher than the highest number in the list add $20.00

this needs to be in a calculation field.

and advice would be greatly appreciated. 

 

 

 

I have 4  numbers.. 25.25, 25.5, 22.25, 22,5

​How? From where? In what format?

If this is a list of related values, use Max(), Min() and FilterValues(); if this is a textual list, convert it into formats that you can Evaluate() with Max()/Min() and examine with FilterValues().

e.g. Evaluate ( "Max (" & Substitute ( "25.25, 25.5, 22.25, 22.5" ; ", " ; " ; " ) & ")" )

PS: “There must be a simpler way” – than what?

There's probably a better way to solve the overall  problem - but we won't know that for sure until you explain the purpose behind this exercise.

These numbers are simply  the standard depth of 4 different board sizes.  but if a customer wants to buy a board size that is 22.4" instead of  22.25  or instead of 22.5  then the board needs cut down, so their is an up charge for that.

so ideally it would be 

if board is less than 25.5  but not equal to  22.25  or 22.5 or 25.25  then add $10  if board is greater than 25.5 then add $20.00

I hope this makes sense. 

How about...

Let([

len = table::lengthRequested ;
standard.lengths = List ( 22.25 ; 22.5 ; 25.25 ; 25.5 ) ;
max.length = 25.5 ;

result = Case (
not isEmpty ( FilterValues ( standard.lengths ; len ) ) ; 0 ;
len > max.length ; 20 ;
10 )
] ;
result
)

Edited May 21, 201510 yr by David Jondreau

Perfect, nice technique  did not occur to me..... thanks a lot.

Question for David (just a newbie trying to learn), if you don't mind me asking:

What is the reason in your solution to declare a result variable using a case statement, instead of just using the case statement outside the variable declaration?

Does this work the same as yours?

Let([

len = table::lengthRequested ;
standard.lengths = List ( 22.25 ; 22.5 ; 25.25 ; 25.5 ) ;
max.length = 25.5

];

Case (
          not isEmpty ( FilterValues ( standard.lengths ; len ) ) ; 0 ;
          len > max.length ; 20 ;
          10 )
)

Didn't know if the way I wrote is just wrong (if so, why?), is a question of style, or some other reason.  Always trying to learn.....

Thanks

Steve

Perfect, nice technique  did not occur to me..... thanks a lot.

​This will work fine for as long as the "standard" is set in stone. In general, one tries to avoid hard-coding data in calculations, since it tends to change from time to time.

In your example, I would use a table of board sizes (with 4 records) and lookup the next higher value through a relationship matching on depth (I suspect that there be may more to a board than just depth - price, for example - so this could work for other purposes, too).  If the looked up depth is different from the given one, you need to add $10; if the lookup fails, you need to add $20 (and these amounts should not be hard-coded, either).

Edited May 21, 201510 yr by comment

Question for David (just a newbie trying to learn), if you don't mind me asking:

What is the reason in your solution to declare a result variable using a case statement, instead of just using the case statement outside the variable declaration?

Does this work the same as yours?

Let([

len = table::lengthRequested ;
standard.lengths = List ( 22.25 ; 22.5 ; 25.25 ; 25.5 ) ;
max.length = 25.5

];

Case (
          not isEmpty ( FilterValues ( standard.lengths ; len ) ) ; 0 ;
          len > max.length ; 20 ;
          10 )
)

Didn't know if the way I wrote is just wrong (if so, why?), is a question of style, or some other reason.  Always trying to learn.....

​It's style.

I do it that way because it makes it easier for me to write/debug a calc that way. I generally write calcs in the Data Viewer. This calc is pretty simple, but if it were more complicated I could drop different calc variables outside the variable declaration and see if the result matched my expectations. For example, I could have written the calc the following way, and if it wasn't returning the right result, debugged it, by, say making sure the "too.long" variable was correct:

Let([

len = table::lengthRequested ;
standard.lengths = List ( 22.25 ; 22.5 ; 25.25 ; 25.5 ) ;
max.length = 25.5

match.length = not isEmpty ( FilterValues ( standard.lengths ; len ) ) ;
too.long =  len > max.length ;

result = Case ( match.length ; 10 ;
too.long ; 20 ;
0 )

];
//result
too.long
)

Very interesting, thanks for your explanation!