Tutorial - Page 12
Contents
The previous page 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 help file) 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 customers with a last 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 abit faster too). Here is what that filter would look like:
UPPER(SUB( NAM:state ,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 information about hardware. You are trying to print a report for any references to SONY brand equipment. Remember that you can't trust that people typed in SONY in capital letters; they may have spelled it Sony or sony, so we have to convert the memo field first to UPPER case, then check for the word SONY. Here is what the filter would look like:
INSTRING( 'SONY', UPPER( NAM:Notes ) )
Again, we are using a function inside a function (the function UPPER() inside of the function INSTRING())..