Multiple Results In Case Calc?

[Mark Reed]

I have the following formula:

Case(

Beginners _ Kids Magic  =  "Yes" ; "Beginner Magic"; 

Card Deck = "Yes" ; "Card Magic";

Card Magic = "Yes" ; "Card Magic";

Close_Up Magic = "Yes" ; "Close Up";

Collectibles _ Historical = "Yes" ; "General Magic";

Comedy Magic = "Yes" ; "Comedy Magic";

Easy To Demo _Shop_ = "Yes" ; "Beginner Magic";

Fire Magic = "Yes" ; "Fire Magic";

Gambling Magic = "Yes" ; "Gambling Magic";

Gospel Magic = "Yes" ; "Gospel Magic";

Magic For Kids = "Yes" ; "General Magic";

Magic Set = "Yes" ; "Magic Sets";

Magic Staples _ Consumables = "Yes" ; "Supplies";

Mentalism = "Yes" ; "Mentalism";

Money Magic = "Yes" ; "Money Magic";

Seasonal _ Christmas = "Yes" ; "Seasonal Magic";

Seasonal _ Halloween = "Yes" ; "Seasonal Magic";

Stage Magic = "Yes" ; "Stage Magic";

Street Magic = "Yes" ; "Street Magic";

"General Magic"

)

Basically I have a table with many fields - all having a Yes or No answer.

I want to create one master field that is based on those - the above works except when where are YES answers to more than one column.

I was expecting it to just take the last true hit and use that value, but instead I get a ? as the result when there are more than one true hit.

How can I fix this so it takes the last true hit and uses that value?

[comment]

I was expecting it to just take the last true hit and use that value, but instead I get a ? as the result when there are more than one true hit.

The Case() function evaluates each test in turn and returns the result of the FIRST test that evaluates as true. If two tests return true, the second test is never evaluated - so this cannot be the reason you get an error as a result.

[JerrySalem]

Mark

FMP evaluates a Case statement from the top down. When it hits the first True result it stops evaluating.

You should re order the evaluations to reflect this.

The '?' in the result is usually a result of a field not wide enough to display data. I am not sure why you are getting it in this case. It wouldn't be because there are more than one true statements.

Jerry

[Mark Reed]

Mark

FMP evaluates a Case statement from the top down. When it hits the first True result it stops evaluating.

You should re order the evaluations to reflect this.

The '?' in the result is usually a result of a field not wide enough to display data. I am not sure why you are getting it in this case. It wouldn't be because there are more than one true statements.

Jerry

Thanks guys - I never realized it showed a ? when the field was too short. Never ran into that before - dunno how! hahaha

I do see it working now taking the first true hit.

Thanks!

I should have took more time and dug into it before posting.

[comment]

I never realized it showed a ? when the field was too short.

This would not happen if the result type of the calculation were set correctly to Text.