Using the CASE functions | INTERMEDIATE
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:
- 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
- Title the new field “Day Name” and go to edit the properties of the field
- 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.
- 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
- Author: Jake Rattner (firstname.lastname@example.org)
- Date Submitted: 11/28/2018