Transcription

We’ll show you how to write a calculation using one of FileMaker’s most useful and commonly used functions, called a case. A case statement is used to run a series of tests, returning a different result for each one depending on whether or not the test result is false.

A perfect example of this is setting a priority based off of due date. If the due date is today, for instance, you might make the priority Critical. If it’s due tomorrow, the priority level would be High.

Let’s use the data viewer to write a calculation. Start a new expression. We’ll begin by using the function called case. The first parameter that the case function requires is the test itself. We’re going to check the task due date and see if it is today. The next parameter will be the result if this test returns true. We’re going to make this “critical.” We could stop here and offer in a result if this returns false, or we could continue with a series of tests.

Next we’ll check to see if the task due date is within the next three days. We’ll see if the due date is greater than today and less than or equal to the get(CurrentDate) plus an extra three days. If this is true, then we’re going to return a result of High priority. Next we’re going to see if the due date is due any time within the next week. For that we will check if due date is greater than the current date and less than or equal to the get(CurrentDate) plus an extra 7 days. If that is true, we’re going to return a result of Medium priority. As a backup, when all else fails, everything returns false, we will return the result of Low priority. We’ll close out of our function with a parenthese.

One important thing to note is that the order in which you write these is very import. FileMaker will process these requests sequentially and return the first true result that it finds, meaning if the medium priority line had been written first, then it would’ve been the first true statement, and we would’ve gotten a result of Medium, even though the result we really were looking for was High. Just make sure, when writing your calculations, that you account for this workflow and write them accordingly.