A more "elegant" CASE for multiple fields?

[2ninerniner2]

Is there a more "elegant" method to test multiple fields with a CASE statement?

Example:

I have a layout with 51 fields whose value I have to check for "Pass" or "Fail"; ANY One or More "Fail" values out of the 51 is what I am "trapping". What I have done that works as intended is:

Case (

Field1 = 1 ; "Fail" ;

Field1 = 2 ; "Fail" ;

.

.

.

Field51 = 1 ; "Fail" ;

"Pass" )

Just wondering if thers is a "shortcut" to achieving the desired result

BTW, in "case" you are wondering what type of "test" in which just ONE "Fail" results in a "Fail", it is for a Line Check for 737 Captains!

Cheer!

Lyle

[LaRetta]

Hi Lyle, you will hate me for this but ... the best way to eliminate your problem is to have a related table instead of 51 'like' fields. Someone once told me that, if I did not have something positive to say not to say anything. However, I think that IS positive input because your life will be much easier. Seriously ...

Tell Steve hi! :wink3:

[Vaughan]

Agree with LaRetta, related records rather than fields would have been MUCH better.

Imagine, this: instead of pass or fail, the Score field in the Questions records contained 1 or 0. You could work out the person's Grade with just one calculation:

Grade = Sum( Questions::Score ) / Count( Questions::Score )

[comment]

Although there is a simpler way, with 51 fields nothing is going to be simple. Even a repeating field with 51 repetitions would have been a better solution.

Once you have the scores in individual records of a related table, you can determine the Pass/Fail status simply by =

IsEmpty ( FilterValues ( "1¶2" ; List ( Scores::Values ) ) )

(this is assuming 1 and 2 are below the passing limit - your example is somewhat ambiguous in that respect)

[normanicus]

All you need when 'Fail' is 1 and otherwise 0 is:

If [ Sum( YourTable::YourField]

as, if they are all 0 the result will be False, otherwise True.

[2ninerniner2]

@ LaRetta - Steve says "howdy"

Yes, a related table is what I had expected, and what I had initially started to do. However, "they" told me, after I had started, that this particular Check is applicable to all crew on all aircraft types and will "rarely if ever change" (we've all heard THAT one before!). So I figured I would do it "quick & dirty" for now as they need this "yesterday". I will work this "mess" into the other area of the app for other checks where it will be done "properly"

@comment - the "Fail" score is 1 and 1 only. A score of 2 to 4 is a "Pass".

Thanks everyone for the advice and calcs! :)

[comment]

@comment - the "Fail" score is 1 and 1 only. A score of 2 to 4 is a "Pass".

Well, then this one isn't right:

Case (

Field1 = 1 ; "Fail" ;

Field1 = 2 ; "Fail" ;

Anyway, the same method would still work =

IsEmpty ( FilterValues ( "1" ; List ( Scores::Values ) ) )

[2ninerniner2]

oops! ... changed the wrong "1" to "2" after pasting a copy of the first one :idot: