A - Advanced Accounting Filter Functions

The following are some functions that you can use when specifying a filter in one of the File Utilities. All expressions that make up a filter must be able to resolve to True or False. If True then the process continues. If False the program will search for the next record. Don't forget, you can also use .A. (and) .O. (or) and .N. (not) to create a complete expression.

If you use a function improperly, or refer to a field that is not used in the procedure you will get an error that refers to a problem with compiling the expression. The program will continue, however, the filter will not work.

You can also use a function as part of another function. This means you can use the UP() function within another, such as:

LOC('ABCD',UP(field_name))

Also, you can specify the function names in either upper or lower case. The same applies to the field names.

CTOD(date_string)

If you want to compare to a date field within the record you must use this function. It will convert a date in alphanumeric format to the proper internal format. For example, if you want to get only those records in the sales order file where the entry date is before 3/15/95 you would use:

bkar.inv.orddte<ctod('03/15/95')

Note that you must surround the date value with quotes to make it a date_string. Also, it must be in standard date format.

LOC(value_to_search_for,name_of_field_to_search,start_loc)

Use this function to determine whether certain characters exist in a field. For example, if you want to check for the characters 'ABC' within the customer code you would use:

LOC('ABC',BKAR.CUSTCODE)>0

Note the use of <>0 at the end. The LOC() function returns a value. If the value_to_search_for is a part of the name_of_field_to_search then this function returns a value > 0. If the characters could not be found it returns 0. You can use another field for the value_to_search_for instead of an alphanumeric constant.

The start_loc is the character position to start checking in the name_of_field_to_search. In most instances where you will use this function you will want to start checking with the first character and you can leave this out of the function.

MID(field_name,from_chr,num_chrs)

Use this function to take a part of a field. You would use this when you want to check the characters in a specific portion of a field. For example, if you know that the state characters in the customer code are always in character position 6 and 7 (the first character in a field is position 1) and you want to check for the state NC you might use:

MID(BKAR.CUSTCODE,6,2)='NC'

Note that the num_chrs value is the number of characters to get, not the through position value. Why would you use this instead of LOC('NC',BKARCUSTCODE)>0? What if the customer had INC as part of their code? The LOC() function will tell you it exists but it may not be where you want to check.

TRIM(field_name,what_to_trim)

Use this function to trim the spaces from a field. The what_to_trim value can be 'L' (leading) or 'T' (trailing). You would do this when you want to compare an alphanumeric field to a string constant. For example:

trim(bkar.custcode,'t')='ABCD'

The field_name must be a field that is accessed by the File Utility Program. The what_to_trim value must be a single character surrounded by quotes.

UP(field_name)

Use this function to force the characters in the field name into upper case. You would do this when you want to compare an alphanumeric field to a string constant and want to make sure that what you're comparing to is all upper case. For example, if you want to check for the characters 'INC' within the customer name you would use:

LOC('INC',UP(BKAR.CUSTNAME))<>0

Since you don't know whether the name would be in upper or lower case by using the UP() function you can be sure it's in upper case all the time. Also, this function does not change the actual field. It creates a temporary field, upcases the characters in that field and then finishes the rest of the expression. Note that the UP() function is embedded in the LOC() function. This would be a typical us for this function.

Boolean Operators

There are also 3 Boolean operators that can be used with the filter functions above:

.AND. - And two values together, both have to be true.

.OR. - Or two values, one must be true for the filter to work

.NOT. - Reverse the value of the following filter. If it would normally be false it will now be true.

Parentheses

You can also use parentheses to group items. Then, everything within the beginning "(" and ending ")" parens must evaluate to true.

Page url: http://www.cassoftware.com.com/adv7docs/index.html?appendixa_advancedaccounti.htm