Here are typical examples of using functions to perform computational tasks.
You have a customer name field that was stored in uppercase. You want to reformat it into proper case for inclusion in a confirmation letter. To do this, you could create a computed field whose value was: PROPER(NAME). This would turn “JOHN SMITH” into “John Smith.” You could then export this new field instead of the original field.
You need to know the age of each transaction for the calculation of daily interest. You could include AGE(INV_DATE) in your calculation. This would return how old each transaction is in days, correctly accounting for month and year ends, as well as leap years.
You can combine functions. For example:
| • | The ROOT()function returns the square root of a number. However, this is undefined for negative numbers which is the usual representation of accounting credits. The ABS() function gives you the absolute value of a number, which is always positive. You can therefore combine these two functions to get an appropriate square root as ROOT(ABS(AMOUNT),2) |
| • | If you have an address field that is 20 bytes long with the entry: “123 Somewhere Street”, you can use the INCLUDE() function to create a field with only the number as INCLUDE("Address","0123456789") |
The result would be “123.” If you use this expression as a field, the new field is still 20 characters long.
If you want to create a smaller field of, for example, five bytes, you need to use the SUBSTRING() function:
SUBSTR(INCLUDE("Address","0123456789"),',5)
For more details on how Analyzer evaluates expressions, see Fixed-point Arithmetic.
To check whether the Address field has the character string “New York” in it, you would use the FIND() function. The function format is FIND(C,<field>) where C is the character string you want to find and field is the field in which you want to search. If you want to look through the whole record, omit the field parameter.
| 1. | Click [Edit View Filter] in the View. |
| 2. | In the Expression Builder enter FIND("New York",ADDRESS) in the Expression text box and click [OK]. |
Records that have “New York” in the Address field are displayed and records that do not have “New York” in the Address field are filtered out. See Filters for more information and examples of using Analyzer functions.