G'morning, all. I'm sure there's a better way to do the following other than having a litany of Case statements in a calculation. 

There are three fields for three tests: Math, Reading, and Writing. Currently, if there's no value for each test then a Calculation (text) field displays (for the Math test) "Our records show that you have not passed the Math portion of the COMPASS exam." A separate, but identical, calculation/sentence applies to Reading and Writing so I have three, separate calculated fields to use as merge variables later. The problem is that in a letter, having the same sentence listed three times in a row with the same text (save for the different value) is unprofessional looking so I'd like to modify the calc to include any of the missing tests in one sentence, e.g., "Our records show that you have not passed the Math and Reading portion(s) of the COMPASS exam." 

I'm sure there's an elegant way of coding that so I don't have to resort to multiple Case statements--what would it be?

TIA for your help!

Edited June 23, 20178 yr by WF7A
Grammar police

3 hours ago, WF7A said:

I'm sure there's an elegant way of coding that so I don't have to resort to multiple Case statements

You do need multiple Case (or If) statements in order to put together the basic list of missing subjects - something like:

List (
If ( IsEmpty ( Math ) ; "Math" ) ;
If ( IsEmpty ( Reading ) ; "Reading" ) ;
If ( IsEmpty ( Writing ) ; "Writing" )
)

Once you have this, you can replace the last separator with an "and" and change the other separators to a comma, before merging it with the rest of the text:

Let ( [
list = List (
If ( IsEmpty ( Math ) ; "Math" ) ;
If ( IsEmpty ( Reading ) ; "Reading" ) ;
If ( IsEmpty ( Writing ) ; "Writing" )
) ;
lastCR = Position (  list ; ¶ ; Length ( list ) ; - 1 ) ;
replace = If ( lastCR ; Replace ( list ; lastCR ; 1 ; " and " ) ; list )
] ;
If ( 
not IsEmpty ( replace ) ;
"Our records show that you have not passed the " & Substitute ( replace ; ¶ ; ", " ) & " portion(s) of the COMPASS exam." 
)
)

Finally, you could reuse the same test to control the plurality of "portion(s)":

Let ( [
list = List (
If ( IsEmpty ( Math ) ; "Math" ) ;
If ( IsEmpty ( Reading ) ; "Reading" ) ;
If ( IsEmpty ( Writing ) ; "Writing" )
) ;
lastCR = Position (  list ; ¶ ; Length ( list ) ; - 1 ) ;
replace = If ( lastCR ; Replace ( list ; lastCR ; 1 ; " and " ) ; list ) ;
s = If ( lastCR ; "s" ) 
] ;
If ( 
not IsEmpty ( replace ) ;
"Our records show that you have not passed the " & Substitute ( replace ; ¶ ; ", " ) & " portion" & s & " of the COMPASS exam." 
)
)

 

 

Edited June 23, 20178 yr by comment

Wow! Thank you VERY much for all that. You're a prince among men.  : )