Similar to IF-Statements a CASE statement evaluates a test condition (x) and returns a specific value if the condition is met. CASE is especially useful in scenarios where you want to test multiple condition against the same field. How might you use this? A few example include:

✓ Return currency type based on country or geography (ex. Mexico = Pesos)

✓ Return the day name based on the day number (ex. 0=Sunday)

Let’s say you want to provide determine the named-day of the week (ex. ‘Monday’) based on a date field. You could write a CASE-statement to find out. Here are the steps to set up your “Day Name” formula field:

1. Go to the table that contains your [Date] field and add a new field with the field type “Formula Text*.

EDITORS NOTE: The field type should correspond to your final output. For instance, if you want to return “Saturday”, use a Formula – Text Field. If you want to return a date or a number, you would use a Formula – Date field or a Formula – Number field

2. Title the new field “Day Name” and go to edit the properties of the field
3. Enter the formula: Case(DayOfWeek([Date]), 0, “Sunday”,1, “Monday”,2, “Tuesday”,3, “Wednesday”,4, “Thursday”,5, “Friday”,6, “Saturday”)

Now the field will reflect the day name for any date entered in the [Date] field. So let’s look under the hood to see what’s going on:

First, we have our field [Date] which we are testing conditions against. You’ll notice that [Date] is wrapped in another formula called [DayOfWeek]. This simple but powerful formula takes a date and returns 0-6 depending on which day of the week a date fall on (ex Sunday = 0).

This converts the [Date] field to a number 0-6.

The following six lines each evaluate the resulting number (0-6) and assign a day based on that number. For example the second line of the formula is: 0, “Sunday”

This means that, if the DayOfWeek([Date] = 0, then the day is “Sunday”

EDITORS NOTE: If no corresponding value is found CASE will return ‘NULL” meaning the field will not return any value.

4. Save your field and, if necessary, add the field to your form. You will now see a “Day Name” for each record containing a [Date]. Ex:

To view articles about if statements click here: IF-Statements

If you would like to test how the case formula works in a live app please visit: https://quandarycg.quickbase.com/db/bn22wn4cx?a=dr&r=d&rl=csg