Using Nested @Functions()

IDEA Tech Tip

One of the most powerful tools in IDEA is the ability to create equations, save them as “.eqx” files, and share them with colleagues. By utilizing the list of @functions in the Equation Editor, you can filter data via Criteria or append new fields to your database. Learning to combine the many different functions in creative ways can open a wealth of new capabilities that you may not have known your IDEA software is capable of.
 

Syntax

 
All of the functions used in the IDEA Equation Editor start by typing the “@” symbol followed by the function name and a set of parentheses which contain the parameters that must be entered for the function to work. The parameters are different for every function. Fortunately, the equation editor provides you with a detailed description of each function, including the parameter definitions and examples of use.

To view the description for an @Function, open the Equation Editor and select the one you are interested in. The description will appear in a window on the right.

The first parameter is usually the value being evaluated. In the screenshot above, the @Ctod() function asks for a string. This could be a Character field or could be just written text.
 

Nesting

 
You can use an @Function in place of a string or parameter inside another @Function. This is commonly called “nesting.” Nesting is very useful when the string’s or parameter’s value would change from record to record.

When building a nested IDEA equation, it may be helpful to think of it as being very similar to an algebraic equation since they use many of the same rules. For example, the “Order of Operations” rule in algebra dictates that operations within parentheses occur before those outside. This is true when writing IDEA equations as well.
 

Example:

 
Let’s say you have three Numeric fields called MONTH, DAY, and YEAR. You’ll need to concatenate those fields into a single Date field if you want to do any sort of aging or other date related analytics. It’s important to note that Numeric fields cannot be concatenated, so the data in the three numeric fields must first be converted to character data, then concatenated. Once concatenated, the resulting value would need to be converted to a date data type. All these steps can be performed with one nested equation similar to this:

@Ctod(@Str(MONTH, 2, 0)+@Str(DAY, 2, 0)+@Str(YEAR, 4, 0), “MMDDYYYY”)

As noted above, the syntax for the @Ctod function is @Ctod(String, Mask). This part of the equation, @Str(MONTH, 2, 0)+@Str(DAY, 2, 0)+@Str(YEAR, 4, 0), serves two purposes. It converts the numeric values in the Month, Day and Year fields to character data type and is used as the String in the @Ctod function. This part of the equation, “MMDDYYYY”, is the Mask. The equation only works because the operations inside the inner most parentheses were completed first.

We hope this will be very helpful to your team. And, as always, if you have questions about this Tech Tip or other IDEA questions, please give our help desk a call at: