Case caculation help needed!

[ethecat]

I have a text field that contains one of four possible values: A, B, C, or D

Based on those values, I would like to create a field that would assign a result of either Yes or No to those values. A value of A or D would return a result of No, a value of B or C would return a value of Yes.

I think I need to calculate it using a Case Function, but I don't know how the formula/syntax should read.

Thanks in advance for any help! I'm new to these caluculation functions.

[SteveB]

Case(textfield= "A" or textfield= "D","Yes", "No")

This assumes that "B" and "C" are the only choices besides "A" and "D".

Steve

[ethecat]

Steve,

Thanks for the response. Yes, B & C are the only choices besides A & D.

I am still having a problem. When I use the calculation you provided, FM highlights the OR and pops up with: "A number, text constant, field name or "(" is expected here."

What now?

Thanks for the help...

[ethecat]

Solved FM's pop-up I had a comma inserted after "A". (oops)

NEW PROBLEM- Everything (A, B, C, D) is returning a value of "No" using this calculation.

Is this a syntax problem?

[Lee Smith]

Case(TextField="A", "No",

TextField="B", "Yes",

TextField="C", "Yes",

TextField="D","No")

[ethecat]

I may missing something really obvious here-

When I use Lee's calculation, I get nothing returned at all. Just a blank. (This applies to all A,B,C, & D values)

I can't seem to wrap my mind around what is going on with this calculation!

Keep those replies coming and THANKS!!

[Lee Smith]

Is your result for the calculation Text?

[ethecat]

Not sure what you are asking- I am getting just a blank space. I want a return of text- "yes" or "no". Nothing returned (blank) is not an option I want.

Does that make sense?

[Fitch]

Is the textfield formatted as checkboxes? If so, then it may actually contain values such as AD, CB, etc. That would explain why your first calc gave you "No" for everything, and your second returned nothing -- in the first, "No" was the default, and the second, has no default value.

Solution is to either format the field as radio buttons or a popup, or use the PatternCount function.

Case(PatternCount (TextField, "A"), "No",

etc.

[ethecat]

I'm not sure how to describe this. The textfield is a single value extracted (by calculation) from a long text string. The textfield is calculated by it's position in the string. I am getting this string from an outside source, so it's format is predetermined.

I am just trying to return a result of either Yes or No to the only four possible values in the textfield which are A, B, C, or D.

[Lee Smith]

The calculation I gave you need the results to be text. That is an option at the bottom of the calculation description box. It usually defaults to number, you need to make sure it is "Text"

Lee

[ethecat]

Yep, it's text. Still no returns.

This really seemed like it should be a simple calculation. I can't quite figure what is not working.

I appreciate all the help I am getting from you FM experts. Keep it coming and I'll keep plugging it in.

[Lee Smith]

[color:"blue"] The textfield is a single value extracted (by calculation) from a long text string.

I think the problem lies in this calculation results. I suspect that you are ending up with extra space in it and therefore the A, B, C, and D are actually space A, or A space, which isn't exactly "A".

Try using the Trim Function with it.

Lee

[Fitch]

I agree with Lee, there's something in the field besides a single letter. Trim() will work with extra spaces, or you could go ahead and use the formula I already gave you:

Case(PatternCount (TextField, "A"), "No",

PatternCount (TextField, "D"), "No",

PatternCount (TextField, "B"), "Yes",

PatternCount (TextField, "C"), "Yes")

Typically I'd only check for 2 of these strings and then just make the default value Yes or No. But here I think it's better to be cautious since something is clearly a little funky and so I've explicitly tested for each string.

[ethecat]

Thanks for all the help guys! After a good night's sleep and double checking both my calculation fields, I had a DOH! moment. In going back to double check the calculation result

in BOTH calculations (Thanks Lee!) I found the results of the text string calculation had defaulted to number. After I fixed that, everyone's calculations worked.

I had always suspected it was some little thing that I had out of whack, because the calculations you all gave me I knew should work.

I really appreciate all the help in getting me thinking in the right direction and showing me so many ways to accomplish the same result. Great work!!