I would use Case instead of If.
FieldName < 3500 ; 3500 ;
FieldName > 3500 and FieldName < 4000 ; 3750 ;
FieldName > amount and FieldName < amount ; AmountYouWantDisplayed ;
Keep repeating until you're done with all 14 ranges.
Edit: spelling (doing this via mobile... sorry)
Message was edited by: eshoshin
1 of 1 people found this helpful
Using set field script step:
ValueLow = table::lowValue ;
ValueHigh = table::highValue
ValueLow > 100 and ValueHigh <= 200 ; 150 ;
ValueLow > 200 and ValueHigh <= 300 ; 250 ;
ValueLow > 300 and ValueHigh <= 400 ; 382 ;
234234 // this is the defaullt result if all of the above fail
) // endCase
) // end let
That should work if there are no syntax errors ;-)
Thanks for the response E. I have altered my script to reflect your suggestion. However, I am still having a problem. As long as the perameters fall within the limits of the first case, the result completes correctly, Unfortunately, once the perameters fall into any of the other levels of the cases the result is the final default.
Any other suggestions?
Thanks for the feed back Darren.
I am just a little unclear on one thing. In your example, is the word "table" referring to the field I am referencing? I have not been able to get your example to work, but feel it is because I am doing something wrong. I will continue to see if I can figure it out, but would appreciate any other sights you may have.
I think I found my error. It seems to be working but I need to do some more testing.
Yes, I meant that you should substitute in the correct field names and whatnot to make the calc work in your solution.
I elected to use LET() because it allows you to specify a variable to use in the calculation, improving its readability.
This calc basically says this:"Set the Low and High value entered by the users to variables. Then check if the values are in a specified range and return the corresponding result for each range."
table is the name of the table that the data is in. When you create the Case statement, on the upper left of the calculation window is a list of fields. Click on the field you want. It will put it in the calculation. So for example if the table is mytable and the field is lowvalue it will look like mytable::lowvalue. In short it is tablename followed by :: and then field name.
Except there isn't a field mytable::lowvalue or mytable::highvalue. The case is based on a calculated field, so he'll have to specifiy in the code exactly what ranges are needed. At least, that's how I understood it from the orginal post. The two user input fields are used in the calculation, but it was not specified that the two fields are the low and high ranges.