The previous lesson got your feet wet on creating a filter; here we are going to dig a bit deeper in creating some more filters.
As mentioned before, you can use the Dictionary (or this User's Guide) to see what the different functions and operators are that can be used in creating a filter for your reports. Some functions will be used more often than others; here is a short list of them:
|•||Clip() - remove blank space from the end of text|
|•||Upper() - convert text to all uppercase|
|•||Lower() - convert text to all lowercase|
|•||Sub() - retrieve a portion of a text string for comparison|
|•||Instring() - check for text inside a larger piece of text|
We already covered how to compare a database field containing text against a text string. However, there are some more tricks that you can do to spruce up your reports. For example, let's assume that you need to print a report of all respondents with a name starting with the letter "F". You would use the sub() function to test the value of the first letter of the last name; to be safe you would also use the upper() function to make sure that letter was uppercase (also makes the filter simpler, and run a bit faster too). Here is what that filter would look like:
UPPER(SUB( Res:Name ,1,1 ) ) = 'F'
Notice that we used two functions at the same time, one inside the other with the field in the middle.
Here's another good example; in this case trying to locate information inside a large memo field. The situation is your database has a memo field that contains answers to a Verbatim perceptual question. You are trying to print a report for any references to your morning man, Joe Blow. Remember that you can't trust that people typed in Joe Blow in upper and lower case letters; they may have spelled his name joe blow or JOE BLOW, so we have to convert the memo field first to UPPER case, then check for the words JOE BLOW. Here is what the filter would look like:
INSTRING( 'JOE BLOW', UPPER( rsp:Text ) )
Again, we are using a function inside a function (the function UPPER() inside of the function INSTRING())..
To recap; in this lesson you learned:
|•||How to create more complex filters by using functions|
|•||How to use more than one function inside of a filter|