Case function in formula fields

 

At times when building a saved search it can be advantageous to change the display of a field based on the values present in another. For example, if a search showing inventory on hand can be modified to display a text-based status valued based on the inventory quantity present. Inventory values of 0 could be displayed as “Out of Stock”, anything under 10 could be “Low-Stock”, values of 10-100 could be “Sufficient”, while inventory levels above 100 would be displayed as “Overstock”. This functionality is available via formula fields and the SQL “Case” function.

In this example, we will modify an existing search to add the field we want to display a stock status.

Tip Image 1
Click image to enlarge

Add a new “Formula (Text)” field to the search results.

Tip Image 2
Click image to enlarge

Enter the function value by clicking on the formula column.

Tip Image 3
Click image to enlarge

Use the case statement to build the logic needed.

Tip Image 4
Click image to enlarge

If you need to know the internalid of a field, use the “Field” drop-down and select a value. This will add it to the formula you are building.

Tip Image 5
Click image to enlarge

Now you have added a new field that displays data conditionally upon the value in another field.

Tip Image 6
Click image to enlarge

Full Formula:

case
When {quantityonhand} = 0 Then ‘Out of Stock’
When {quantityonhand} < 10 Then ‘Low Stock’
When {quantityonhand} < 100 Then ‘Sufficient’
When {quantityonhand} > 100 Then ‘Overstock’
End