FM Function of the Week: Case

Logic lies at the core of every programming environment. Whether your programming in Java, Logo, or FileMaker, you must master the ability to determine an outcome based whether or not something is true. This is the classic if/then/else logic. FileMaker’s If function handles this neatly; its three parameters give you a place to plug-in what condition you’re testing for and what to display if the test is true or false.

If ( 5 < 6 ; "all is well" ; "uh oh" )

In the above example you’re effectively asking FileMaker if 5 is less than 6. If FileMaker determines that five is indeed less than six, it will show you the phrase “all is well”. Should FileMaker believe that 5 is not less than 6, it will display “uh oh.” All well and good when your needs are simple. But what do you do when your needs aren’t so simple? What can you do when you need to check for a variety of conditions? While it is possible to start nesting If functions inside of one another, it’s highly error prone and difficult to unravel.

Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )

Drop a Case function into a calculation and you’ll get something like you see above (this one has been reformatted a bit to improve readability). You can think of it as a way to list a series of Ifs. FileMaker will start with the first test. If the test is true, FileMaker will show the first result and ignore the rest of the function. If that first test is false, FileMaker will skip over the first result and look at the second test. If that second test is true, FileMaker displays the second result, if it’s false it moves on.

FileMaker examines each test/result pair until it finds a test that is true. There is no hard limit to the number of test/result pairs a Case function can include. Should FileMaker get through all the tests and find that none of them are true, it will display a default result if you’ve chosen to include one. Let’s look at a real example.

Case (

  DayName ( Get ( CurrentDate ) ) = "Monday";
  "Macaroni!";

  DayName ( Get ( CurrentDate ) ) = "Tuesday";
  "Taquitos!";

  DayName ( Get ( CurrentDate ) ) = "Wednesday";
  "Waldorf Salad!";

  DayName ( Get ( CurrentDate ) ) = "Thursday";
  "Thumbprint Cookies!";

  DayName ( Get ( CurrentDate ) ) = "Friday";
  "Frozen Yogurt!";

  DayName ( Get ( CurrentDate ) ) = "Saturday";
  "Sangria!";

  DayName ( Get ( CurrentDate ) ) = "Sunday";
  "Grilled Cheese!";

  "What calendar are you using!?"

)

Here we have seven test/result pairs and a default result. Basically FileMaker works it’s way through the days of the week. When it reaches the current day, it shows the special of the day. If it gets through all 7 days and none of match the English word for the current day, it displays a message asking what calendar you’re using.

You can copy and paste this calculation, as is, into FileMaker and see it in action.