Skip to main content

CASE

The CASE function is a useful tool for performing conditional logic It evaluates a list of conditions and returns one of multiple result expressions.

The function takes a key value and an array of objects to return the value of the object that matches the key value input. If there is no match the function returns null. You can include an optional elseval to supply the default value to return if no object matches the key value.

Syntax​

object CASE(object key, param KVAL[] values)
object CASE(object key, object elseVal, params KVAL[] args)

Example​

CASE(CategoryID, KV(1,"One"), KV(2,"Two"), KV(3,"Three"))

Will return "One" when the CategoryID is equal to 1, "Two" when it is equal to 2 and "Three" when it is equal to three.

or

CASE(Product, WHEN("D", "Data Sync"), WHEN("O","Ouvvi"), WHEN("S","Server Edition"))

Will return "Data Sync" when the Product is D, "Ouvvi" when the product is O, and "Sever Edition" when the product is S.

or

Convert the salutation for Doctor into a small set of choices.

CASE(Salutation,Salutation // Default if no values found
    WHEN("Doctor", "Dr.")
    WHEN("Dr", "Dr.")
    WHEN("MD", "Dr."))

or

This function can be used to return boolean expressions as well, for example:

CASE(InvoiceStatus,WHEN("Paid", true), WHEN("Outstanding", false)

This will return true for any paid invoices and false for and outstanding invoices due payment.

or

Setting a default value to be returned if not found:

CASE(ProductCategory, "Misc", WHEN("S", "Software"), WHEN("H","Hardware"))

Which will return "Misc" if the ProductCategory is not "S" or "H", or it will return "Software" when the ProductCategory is S and "Hardware" when it is H.