Is it possible to use a case statement in a list calculated field?

[JetLi]

Is it possible to use a case statement in a list calculated field?

Here is what I am trying to accomplish
iif(method = 'per hour' AND incdec ='+',1,0)
iif(method = 'per hour' AND incdec ='-',10,0)
iif(method = 'per minute' AND incdec ='+',2,0)
iif(method = 'per minute' AND incdec='-',20,0)

if this will be the formula in a calculated expression in a list then it has an error called "it cannot be parsed"

[Peter.Greulich]

Re: Is it possible to use a case statement in a list calculated field?

Alpha's UX calc fields are not JavaScript, but a weird proprietary syntax. You can only use the functions they give you in the dropdown list. But you might just build your CASE (i.e. "switch") as a JS function and call it from the calc field. The jiffy help gives you a couple of clues.

[CharlesParker]

Re: Is it possible to use a case statement in a list calculated field?

Not sure if your still working on this but I thought I would add in a little helpful code in case someone else needs an iif statement example that actually works.

Heres what you need to know. This is code that is used in a grid in the client side calculated field expression.
I have fields named "Is_Active" and "Test_Field" and if one of the conditions are met the "value" is returned OR if there is not a match of any of the conditions the iif statement will return 'novalue' as the result. The last value your name is kinda like an ELSE statement, if nothing else is true then use that value.

Things to watch out for, make sure that for every iif( there is a corresponding closing ) so you will notice in my case I have like 9 of them ))))))))) finishing out the statement! Don't forget a comma or to quote your return 'value'

So as you can see your iif statement was not structured correctly so it wouldn't "parse", and to be honest I think petes idea of a select case statement is better, I can also tell you that more than 9 iif clauses and my statement would fail - nobody ever figured out why, lol even though the structure was correct, and this indeed does work!

iif(Is_Active = 2 and Test_Field = 2,'value1',iif(Is_Active = 1 and Test_Field = 2,'value2',iif(Is_Active = 1 and Test_Field = 200,'value3',iif(Is_Active = 1 and Test_Field = 300,'value4',iif(Is_Active = 1 and Test_Field =  400,'value5',iif(Is_Active = 2 and Test_Field = 200,'value6',iif(Is_Active = 2 and Test_Field = 300,'value7',iif(Test_Field = 777,'value8',iif(Is_Active = 0 and Test_Field = 2,'value9','novalue')))))))))

Hopefully someone who needs an iif statement example will be helped.
You may want to put this into notepad to understand it and not see it on a single line!

This is a better view of the code but it needs to be like the code sample above:

iif(Is_Active = 2 and Test_Field = 2,'value1',
iif(Is_Active = 1 and Test_Field = 2,'value2',
iif(Is_Active = 1 and Test_Field = 300,'value3',
iif(Is_Active = 1 and Test_Field = 400,'value4',
iif(Is_Active = 1 and Test_Field = 500,'value5',
iif(Is_Active = 2 and Test_Field = 300,'value6',
iif(Is_Active = 2 and Test_Field = 400,'value7',
iif(Test_Field = 888,'value8',
iif(Is_Active = 0 and Test_Field = 2,'value9','novalue')))))))))

Cheers~

[CharlesParker]

Re: Is it possible to use a case statement in a list calculated field?

One other thing I forgot to mention AND probably the most important - this statement will cause Alpha to hang for HOURS but will parse and works super fast once published. I submitted a bug years ago about it and I was told I should use the select case statement instead - which I have done. I think I was somehow abusing the system with this statement, in othe rwords it was never intended to be used to this degree.

This example is just that an example of an iif statement that works. A smaller number of iif clauses and Alpha doesnt hang, it just works as expected - but again, you try an iff statement like this, it will work but you may be waiting overnight for it to parse, lol.

Odd but true!