problem using the If function with an OR operator

[glj]

For our workshop database, I'm trying to write a calculation, using

logical functions, that will fill in the lodging cost according to what

kind of room was requested ($525 for a single, $375 for a double, etc.)

-- but only for records whose status field does not read either

"cancelled" or "waiting." Can anyone help me diagnose why the following

formula does not work?

If ( status ="cancelled" or "waiting"; "0"; Case ( lodging request =

"single" ; "525"; lodging request = "double" ; "375"; lodging request =

"commuter" ; "150" ))

My intention is for the formula to return the value "0" if the status

field says "cancelled" or "waiting"; otherwise to evaluate the Case

function to fill in the correct lodging cost. It almost works.

Cancelled records are ignored, but not waiting ones. Using parentheses

in the If function -- ("cancelled" or "waiting") -- does not work

either. I'd be grateful for your advice.

Many thanks in advance.

[Lee Smith]

Hi gli,

Someone will probably come along with a more elegant approach, but this seems to do what you described.

Case(status ="cancelled" or "waiting", "0",

lodging request = "single", 525,

lodging request = "double", 375,

lodging request = "commuter", 150, ""

)

HTH

Lee

[-Queue-]

Unless version 7 has changed this functionality, you cannot do a comparison like status = "cancelled" or "waiting". You have to specify both tests, as in status = "cancelled" or status = "waiting". Otherwise it's a boolean test of status being cancelled or "waiting" being true (or not equal to zero), which doesn't make sense in this situation.

You could also use Position( "cancelledwaiting", status, 0, 1 ) in place of status = "cancelled" or status = "waiting". This tests whether the data in status exists in the text string "cancelledwaiting", returning a zero if false and a 1 if true.

[MoonShadow]

Your problem is here...

status ="cancelled" or "waiting";

Should be Status= "Cancelled" or Status = "Waiting" ...

Hi Queue! You are fast!! Nope ... works the same in 7. But now If() will quit evaluating after it hits true! So structure your calculation with that in mind - put first status check (which results in the most number of records) at the beginning - or probably "Waiting" but I like your Position() calc better anyway.

[-Queue-]

MoonShadow, does Case short-circuit as well?

[Lee Smith]

Hi -Queue-,

Your suggestion of status = "cancelled" or status = "waiting" does make "waiting" work properly, I forgot to check that when I wrote the calculation. My calculation as amended whould be:

Case(status ="cancelled" or status ="waiting", "0",

lodging request = "single", 525,

lodging request = "double", 375,

lodging request = "commuter", 150, ""

)

And it didn't seem to "short-circut" either.

[MoonShadow]

Queue said ...does Case short-circuit as well?

Oh yes indeed, JT!!! I've done extensive speed tests on both Case() and If() My Case() test saved 26 seconds on 180,000 records when I placed the 80% records first over placing 10% records first.

What the test (changing order of Case() expressions) should show is that the order does matter to FM7, where as in FM6 the order should not make any difference. Placing the 'most' records first is called branch prediction which, you being a math major, already know I'm sure!

Case(), Choose(), If(), AND, OR all do it now. Only in vs. 7, Lee.

[-Queue-]

That's quite an impressive difference. You should work on writing the white paper for short-circuiting calculations.

[MoonShadow]

Speed and efficiency fascinates me - that's why I devour lean calcs (and love yours, The Shadow's and Ray's - you 'math calc people'). Comparing Choose() and Case() also produces huge speed savings. But then, you know that!!! You are the King of Choose()

[glj]

Thank you all for this very educational discussion.

[Lee Smith]

-Queue- said:

You could also use Position( "cancelledwaiting", status, 0, 1 ) in place of status = "cancelled" or status = "waiting". This tests whether the data in status exists in the text string "cancelledwaiting", returning a zero if false and a 1 if true.

Hi Queue,

You changed the Position calculation around. You used Position( "cancelledwaiting", status, 0, 1 ), shouldn't it be Position(status, "cancelledwaiting", 0, 1). If not, could you explain why. TIA.

BTW, I can't get the calculation to complete using either one, so maybe you could show it in the calculation too.

Thanks,

Lee

[Lee Smith]

Hi Queue,

Upon further review, your calculation by itself [ Position( "cancelledwaiting", status, 0, 1 )] Puts nothing in the field when empty, and a 1 for "cancelled" and 10 for "waiting".

When written Position(status, "cancelledwaiting", 0, 1) it porduces a 0 for either "cancelled" or "waiting" and nothing for a blank field.

Lee

[-Queue-]

Hi Lee. No, Position(status, "cancelledwaiting", 0, 1) wouldn't work unless status contained "cancelledwaiting". Position( "cancelledwaiting", status, 0, 1 ) is what should work, if status contains either option. I was a bit haphazard in stating that it would return a boolean. It doesn't specifically, but it does logically. Any number other than zero is considered true, thus either 1 or 10 is relatively 'boolean' and should work. If you wanted to force it to be specifically boolean, you would need to prepend 'not not' to it, though it will still return null if the field is blank, which doesn't seem to be a problem here (and isn't the case in version 7 anyway).

So, the full calc would be

Case(Position( "cancelledwaiting", status, 0, 1 ), 0,

lodging request = "single", 525,

lodging request = "double", 375,

lodging request = "commuter", 150)

and semicolons would replace the commas in version 7.

[Lee Smith]

Hi JT,

Thanks for taking the blinders off on the Position Function. I have aways thought of the "Text" as meaning a field, DUH.

Lee

[-Queue-]

No problem, Lee. Thanks for helping me realize my stupidity regarding the boolean assertion when I was posting too quickly for my own good.