If then else in Calculated Field in SmartList Builder? Try CASE
OK, so you want to use an If …then…else in a Calculated field on your SmartList… Sorry, ’If …then…else’ is not a SQL function. But, have no fear, let me show you the CASE function. Case can do everything a if statement can do and more!
Here is the most basic way to use the CASE function (line breaks are for clarity – not needed in the Calculated field screen):
CASE
WHEN statement to evaluate* (see below)
THEN what value to use when statement true
ELSE what value to use when statement is false
END
Such as: CASE WHEN {Voided} = 1 THEN ‘Yes’ ELSE ‘No’ END
OR: CASE WHEN {DocumentType} = ‘CreditMemo’ THEN -{DocumentAmount} ELSE {DocumentAmount} END
But you can also have additional When sections (which is ugly with If, then – you need to nest your statements!)
CASE
WHEN statement to evaluate THEN what value to return when statement true
WHEN next statement to evaluate THEN what value to return when this statement true
ELSE what value to return when none of the statements are true
END
It will only look at the second WHEN if the first WHEN is false and so on… (you could have as many as you need…)
Such as a quick calculation for Shipping rates based upon a weight field (a Shipping Weight of 30 would return 15.75):
CASE
WHEN {ShippingWeight} < 10 THEN 5.40
WHEN {ShippingWeight} < 25 THEN 7.76
WHEN {ShippingWeight} < 40 THEN 15.75
ELSE 35.25
END
Now if you are going to evaluate the same field in all of your statements (just have different WHEN for each value your field is equal to) there is an easier way to write that:
CASE field to look at
WHEN first value THEN value to return when field is equal to first value
WHEN second value THEN value to return when field is equal to second value
WHEN third value THEN value to return when field is third value …
ELSE value to return when field is not any of the values above
END
Example:
CASE {SOPTYPE}
WHEN 1 THEN ‘Quote’
WHEN 2 THEN ‘Order’
WHEN 3 THEN ‘Invoice’
WHEN 4 THEN ‘Back Order’
WHEN 5 THEN ‘Return’
WHEN 6 THEN ‘Fulfillment Order’
ELSE ‘Unknown’
END
A important note is that when your field is an integer, long integer or currency you just put in your values. All string data must be included in quotes !), whether that data is part of your statements to evaluate or the values you wish to return. These are the quotes by your Enter key – NOT the fancy slanted quotes used by MS Word and my blog…sorry. Both single or double quotes seem to work (but I have had some feedback from MS support that single quotes are preferable – since SQL really only uses single quotes).
Also note that fields from your tables (in {} in my examples) can be included in all parts of the functions.
*Statement to evaluate – generally statements are:
- a field then
- = (equal to), < (less than), >(greater than), <= (less than or equal to), >= (greater than or equal to)
- a fixed value to compare or another field
Your statement to evaluate can be several statement separated with AND (all must be true) or with OR (only one must be true). (Example: WHEN {DocumentType} = ‘CreditMemo’ AND {Voided} = 0 THEN …)
Again, if you need additional information, do a web search and look at other examples on this blog, you will get the hang of it!
Try it, you will learn to love the CASE statement.
Happy Building!
Devon
