A more "elegant" CASE for multiple fields?

January 16, 201213 yr

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

January 16, 201213 yr

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:

January 16, 201213 yr

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 )

January 16, 201213 yr

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)

January 16, 201213 yr

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.

January 16, 201213 yr

Author

@ 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! :)