SWITCH
SWITCH(value, match, result, …, [default])
Compares one value with a list of matches and gives the result paired with the one it equals, such as a weight for each status. Text matches ignore case, and a status or task type is checked against its real values when you save. With no match and no default the result is blank, and when the value itself is blank the result is blank too, even if you gave a default.
Arguments
| Argument | What to give it |
|---|---|
| value | The value to look up: [Status], [Phase], [Type]. |
| match | A value of the same kind to compare with. For a status, one of its statuses. A bare number against a duration counts as working days. |
| result | What to give when value equals the match before it. Every result must be the same kind. |
| default | Optional. A last, unpaired value for everything that matched nothing. |
Examples
Formula=SWITCH([Status], "Complete", 1, "In progress", 0.5, 0)
A weight for each task's status: 1 when complete, a half in progress, 0 otherwise.
Formula=SWITCH([Phase], "Civil", "Groundworks", "MEP", "Services", [Phase])
Renames two phases and keeps every other phase as it is: the default can be a field.
Formula on relationships=SWITCH([Type], "FS", "Finish to start", "SS", "Start to start", "FF", "Finish to finish", "Start to finish")
On a relationship: its type spelled out.
Reach for something else when
Common questions
What does “Completed isn't a status” mean?
The match isn't one of the statuses, and the editor lists the ones there are (Not started, In progress, Complete). A misspelt match would never match, so it is refused rather than left to give the default on every row.
The editor refuses this=SWITCH([Status], "Completed", 1, 0)
What does "SWITCH compares a Status with Number" mean?
A match has to be the same kind of value as the one you look up. Write the statuses in quotes.
Why doesn't the default apply to tasks with no phase?
A blank value gives a blank, not the default. To give those rows a value too, fill the blank first: SWITCH(COALESCE([Phase], "None"), …).