SWITCH function

  • 21 December 2021
  • 3 replies

Userlevel 4
Badge +2



Compares one value against a list of values, and returns a result corresponding to the first match. If there is no match, return an optional result.



SWITCH(expression, value1, result1 [, value2, result2...]...[,result to return if there's no match])


Return type

Same type as the returned values (all values should have the same type)




Return Type

SWITCH('Status', 0, "Inactive", 1, "Active", "Deleted")


SWITCH('Code', "A", 1, "B", 2, 0)



Excel equivalent: SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

See also: IF

3 replies

Badge +2

The following give me an error : SWITCH('Finished Goods'.Code, "111", '111-Main-Floral-Wall', "112", '112-Main-Floral-Not Wall', 0).

Any idea why ? I only get a “formula error” message.


Notes :

  • It works with imbricated IF formulas (but … 🙄)
  • 111-Main-Floral-Wall and 112-Main-Floral-Not Wall are metric numbers.


Thanks in advance !

Userlevel 4
Badge +3

Hi Camille,

Not sure about this specific context but I reckon you have to be explicit when using the SWITCH function. What can be done is pasting most parts of your function (e.g. Entities.Allocation Rule, ‘Allocation - A’, ‘Allocation - B’) and make sure they all have the same structure (same dimensions returned).

Making sure the target format was right was also a good idea!

Badge +2

Hi François,

It seems that the metrics are causing the error If i change from 

SWITCH('Finished Goods'.Code, 111, '111-Main-Floral-Wall', 112, '112-Main-Floral-Not Wall', 0)


SWITCH('Finished Goods'.Code, 111, 0.4, 112, 0.6, 0)

It works fine.