Using filter functions

Filter functions are basically used to retrieve a value from somewhere and return it as a string enclosed in single quotation marks. This applies to these filter functions:

The sole purpose of the filter functions is to construct phrases for SQL WHERE-clauses, which are used in the FILTER() keyword. So it follows that the only use of the standalone FILTER() function is when constructing a phrase for a SQL WHERE-clause using an expression that is not a PROPERTY, V (variable), E (event parameter), SV (session variable), GC (global constant), or METHODPARM (method parameter), because all of those have corresponding filter functions that you could use without bothering about FILTER() itself.

For example, a parameter named Prefix is passed to your application event. This application event is designed to do something to all the items whose item ID code begins with the prefix. You can load those items using a Load IDO Collection action with the following parameters:

IDO("SLItems")
FILTER( SUBSTITUTE("Item LIKE {0}", FILTER( E(Prefix) + "*" ) ) )
PROPERTIES("Item, Description") 

Now suppose your application event fires and is passed a prefix of AL.

The expression inside the first FILTER() keyword works like this (from the inside out):

  • E(Prefix) + "*" evaluates to: AL*
  • The FILTER() around that places single quotes around it: 'AL*'
  • The SUBSTITUTE() function turns that into: "Item LIKE 'AL*' "

This resulting string is an ideal SQL WHERE-clause, because the IDO Runtime Service turns the asterisk into a percent-sign that SQL Server understands.

Note:  On the other hand, an FE(Prefix) expression would evaluate to " 'AL' ", which is not conducive to getting the asterisk inside the single-quotes where we need it. So, in effect, we are postponing the wrapping of the single-quotes until just the right time.

The same effect could be implemented using another level of SUBSTITUTE(), or string concatenations using quoted quotes, but FILTER() is cleaner.