I am having difficulties creating a calculation using case. Perhaps I am making more complicated than needed and perhaps it needs to be an If function. Not quite sure.

I work with students in college. I am trying to create a blurb for my report.

Example of blub: Mary Margaret '18, a senior in the College of Engineering, is majoring in Computer Science and Economics. Mary can be reached at mm54@cornell.edu.

Issue: Students have more than one college and possibly more than one sub school.

Fields:

college (essentially college 1)

sub school

college 2

sub school 2

College_Sub School One Concat - calculation that combines the College and sub school

College_Sub School Two Concat -- calculation that combines the College 2 and sub school

Problem: The Case function below is almost perfect...I just cant seem to get it to come out right when I have a student that has only two colleges but no sub schools.

Blurb should read: Mary Margaret '18, a senior in the College of Engineering and College of Arts and Sciences, is majoring in Computer Science and Economics. Mary can be reached at mm54@cornell.edu.

Where is my error???

Here is my case "statement"

Case (College ≥ "a" & School_sub = "" & CollegeTwo ≥ "a" & School_sub2 = ""

; College & " and " & CollegeTwo

;College_Sub School One Concat = College_Sub School Two Concat

; College_Sub School One Concat

; College_Sub School One Concat ≠ CollegeTwo

; College_Sub School One Concat & " and" & CollegeTwo

; School_sub ≥ "a"

; College & ", " & School_sub

; CollegeTwo ≥ "a"

; College & School_sub & " and " & CollegeTwo & School_sub2

; College )

Thank you!

You have multiple problems here.

Logical (Boolean) expressions such as you might use in Case (or If) as well as many other places in a FileMaker Solution:

& is not and. The first concatenates (glues together) text, the second is the logical operator that requires the expression on both sides of it to be True for the expression to evaluate as True.

Then, if you want to test to see if a field is empty, Use the IsEmpty function or compare it to the empty string "". There are characters with a value less than "a".

So the first part of your case function might read:

Case ( Not IsEmpty ( College ) and IsEmpty ( School_sub ) and Not IsEmpty ( CollegeTwo ) and IsEmpty ( School_sub2 )

; College & " and " & CollegeTwo

But you could simplify that to be:

Case ( Not IsEmpty ( College ) and IsEmpty ( School_sub & School_sub2 ) and Not IsEmpty ( CollegeTwo )

It might be possible to simplify that further, but you haven't shared your "Concat" calculations. If they can be made to tolerate empty sub school fields, the number of possible combinations to handle in Case gets a lot less.

You really would NOT want to use If here. If is just a restricted form of Case.

And an even more radical change here would be to dispense with multiple fields for school and sub school. You could use a table of related records and work from a fully flexible number of schools and sub schools. A bit of added validation to prevent the entering of the same info twice would also simplify this process.