Tech Tuesday: Including Extender Fields in SmartLists using a Calculated Field in SmartList Builder

The Tech Tuesday post today is inspired by real-world events. Every once in a while I see someone who has added a simple field or two using Extender and now wants to include this information in a calculation on a SmartList using SmartList Builder. What happens inevitably is that some of the records may not have a record in the Extender side of things. When you pull the field onto the SmartList you will see that you have a value of 0 (for numbers or blank for strings). The actual value behind the scenes is NULL because there was no record.

The behavior that you see in your calculated field is that a value of 0 or blank will always be returned. You can correct your calculation simply by adding a CASE statement to account for the records that don't have values. Below you will find a sample:

CASE

    WHEN ({Extender Window:Value}) IS NULL THEN ({GP Table:Amount} – ({GP Table:Cost}))

     ELSE ({GP Table:Amount} – ({GP Table:Cost} + {Extender Window:Value})) 

END

The CASE statement in calculated fields can be very helpful to get the desired results in your SmartLists. Have fun with this one and let me know if you have additional ideas for calculated fields you would like to see.

Thanks,

Chris Dew

Director of Product Management

Curious to learn more about SmartList Builder or Extender? Reach out to us at sales@eonesolutions.com.