I have several if statements I'd like to join together into one statement in a calculated value

[julesnye]

e.g.

If(AND(Category<"28", Category>="21", InitVal>="50000000” ),"Cat4","")
If(AND(Category>="21",Category<"28",InitVal<"50000000" ),"Cat3","")
If(AND(Category<"28", Category>="21", InitVal<"50000000”, InitVal>="5000000” ),"Cat3","")
If(AND(Category<"28", Category>="21", InitVal<"5000000”, InitVal>="1000000” ),"Cat3","")
If(AND(Category<"28", Category>="21", InitVal<"1000000” ),"Cat3","")

What I'm trying to do, is have just one calculated value on my form incorporating the above if statements.

Does anyone know how to do this.

I want it to work like:

if it's this or this then blahblah

  or

    if it's this or this then blahblah etc.

To behave like a case statement ideally

I've tried quite a few ways of getting the result but it just freezes the form when reloaind it.

Any help would be greatly appreciated.

Kind regards

Julian

[sojanm]

Hi Julian

Try this one

If(Category<"28" && Category>="21" && InitVal>="50000000” ,"Cat4","") ||
If(Category>="21" && Category<"28" && InitVal<"50000000" ,"Cat3","") ||
If(Category<"28" && Category>="21" && InitVal<"50000000” && InitVal>="5000000” ,"Cat3","") ||
If(Category<"28" && Category>="21" && InitVal<"5000000” && InitVal>="1000000” ,"Cat3","") ||
If(Category<"28"&& Category>="21" && InitVal<"1000000” ,"Cat3","")

But I am not sure if the comparison operation is correct , you can use use = sign to compare string.

But > < can be used for integer or decimal types.

[lakshminarayana]

Hi ,

Use following formula. Use named controls for category and InitVal. And test it for all cases.

If(category < 28 && category >= 21 && InitVal >= 50000000, "Cat4",
If(category >= 21 && category < 28 && InitVal < 50000000, "Cat3"),
If(category < 28 && category >= 21 && InitVal < 50000000 && InitVal >= 5000000," Cat3"),
If(category < 28 && category >= 21 && InitVal < 5000000 && InitVal >= 1000000, "Cat3"),
If(category < 28 && category >= 21 && InitVal < 1000000, "Cat3"), "")

Result:  

Thanks,

Lakshmi Narayana C‌

[MegaJerk · Accepted solution]

Being that several of your conditions never really change, you could take that into consideration. 

if the Category is below 21 or over 28, then it is out of range. 

Likewise, if the Category is in range, then, if the InitVal is below 50000000 then it will be Cat3, otherwise it will be Cat4

Now the problem has been reduced to only a few conditions can be written out as follows: 

If(Category < 21 || Category > 28, "", If(InitVal < 50000000, "Cat3", "Cat4"))‍‍

(or in pure javascript) 

((Category < 21 || Category > 28) ? "" : ((InitVal < 50000000) ? "Cat3" : "Cat4"))‍

I hope that this helps. 

[julesnye]

Thank you all for the great response.

It's very much appreciated.

[EuanGamble]

Hi ‌,

Thats a really interesting rule you are building there. It would be good to run some new features past you. Would you be open to a call with our UX team?

Cheers,

Euan