Equals if not

[Heathbo]

How can I get this calculation working?

Case ( Card Type = "Queen" and Expansion (does not equal sign) "Stone Age" or "Renaissance" or "1800";"Qu";)

Basically what I need to happen is, if Card Type says Queen and Expansion doesn't say Stone Age, Renaissance, or 1800 then this calculation field should say Qu.

[LaRetta]

Hi Heathbo,

If(

Expansion = "Stone Age" or

Expansion = "Renaissance" or

Expansion = "1800";

"";

If(

Card Type = "Queen"; "Qu")

)

It can probably be shortened.

LaRetta

[Heathbo]

But what if Expansion says Stone Age and Card Type says Queen?

[-Queue-]

Then nothing is put into the field. Isn't that what you wanted?

I would shorten it, btw, to

Case( not PatternCount( "|stone age|renaissance|1800|", "|" & Expansion & "|" ) and Card Type = "queen"; "Qu" )

But that's just me.

[Heathbo]

Thank you soo much. You are Awesome. It works perfectly. Thanks again.

[-Queue-]

You're welcome. Now, go to bed! It's 3 AM there already! Sheesh!

[LaRetta]

Hi Heathbo,

vs. 7 short circuits If() and Case() calcs (stops evaluating when it hits the first true) so the first three tests eliminate ALL Expansion records listed (that you want to omit) from the process first. Then only the REMAINING records (those without data matching the text) are tested for the Queen.

JT, what a pretty way to group-test the text. Can you clarify it for my Principles db?

PatternCount(text;searchString)

Text: The pipes create unique search groups within the text listed so it can be used with phrases with spaces (or anything).

SearchString: Add pipe to the comparison field contents (Expansion) to also make them unique for the comparison. ie, it won't find renaissance man for instance or a single Stone (instead of Stone Age).

Case(

not PatternCount( "|stone age|renaissance|1800|";

"|" & Expansion & "|") and

Card Type = "queen"; "Qu" )

not PatternCount() by itself will produce false (Boolean 0) if there is a text match. Then the Case() test applies (since it's wrapped around all of it) and you have a simple test: If not (phrases to omit) and Type = Queen, produce Qu.

Is this correct thinking? Oh, it's so good to have you back!

BTW, am I correct that when there are only one or two tests, If() would work just as well? I used to always use Case() but, since vs. 7, I find myself using If() again when there are only max two possibilities. It's just two less letters to type. They both produce null if the second result isn't specified. Any particular reason you used Case() or is it just another preference?

LaRetta

[-Queue-]

Yes, the pipes help ensure exact matches only. You could use carriage returns or underscores, if you like. I just chose to use pipes for this one. I probably wouldn't have included them, though, if it were not for the space in "stone age". If it were a unique list of single words and only one could be chosen (such as, via radio button), then using spaces in place of pipes would be fine, though it still would be more reliable to force exact matches.

You could also use If. I prefer omitting null results, if they are not absolutely necessary. Case( test; result ) versus If( test; result; "" ) just appears cleaner and more simplified to me.

It's nice to be back. I missed you too.

[LaRetta]

Case( test; result ) versus If( test; result; "" ) just appears cleaner

vs. 7 now automatically produces null if the second result isn't specified same as Case(). So If(test; result) would also work. At least that's my understanding and what proves correct in my calculations. Is my thinking incorrect?

That's the only reason I've started using If() again. I included it in the first calculation because I needed to force the blank result on the text to eliminate (I think)! Thanks JT!

LaRetta

[-Queue-]

You are correct. I had not even noticed that a false result is no longer required. But you do not need it to force a blank in the calculation, since no result will be returned if the test fails anyway. You only need to flip the order of your results.

If(

Expansion <> "Stone Age" and

Expansion <> "Renaissance" and

Expansion <> "1800";

If( Card Type = "Queen"; "Qu" )

)

should work also.

[LaRetta]

That's where my thinking collapsed ... I considered <> but with OR ... which would allow a true (and pass) on the test. <> AND would have done it for sure! Your calc is downright hot however - so don't anyone consider using mine. Use JT's!! He's just helping me improve my thinking on mine ... not suggesting my calcs should be used.

[-Queue-]

It can be confusing, but the general idea is that

not (A or = not A and not B

Additionally, if A is a statement of equality, then not A is the same as A <> "something".

So my transformation of your calculation is performed like so:

If(

Expansion = "Stone Age" or

Expansion = "Renaissance" or

Expansion = "1800";

"";

If( Card Type = "Queen"; "Qu" )

)

becomes

If(

not (Expansion = "Stone Age" or

Expansion = "Renaissance" or

Expansion = "1800");

If( Card Type = "Queen"; "Qu" )

)

becomes

If(

not Expansion = "Stone Age" and

not Expansion = "Renaissance" and

not Expansion = "1800";

If( Card Type = "Queen"; "Qu" )

)

becomes, finally

If(

Expansion <> "Stone Age" and

Expansion <> "Renaissance" and

Expansion <> "1800";

If( Card Type = "Queen"; "Qu" )

)

using the rules of logic.

Just FYI: there is a similar rule for negated compound AND statements. not (A and : = not A or not B

You can verify both of these equations by using truth tables.