Home > Calculated Fields, General - Builders > If then else in Calculated Field in SmartList Builder? Try CASE

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

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s