The Advanced Filter window is a somewhat simpler variation of the rather technical filter window used by releases of ACCOUNTS prior to release 2.24, which was actually supplied by the programming environment used to create ACCOUNTS. It can only be reached by clicking the Advanced Filter button from the Simple Filter window. You can do anything in this window that can be done in a Simple Filter window, and much more, but you have to know what to type, rather than being able to use the drop-down lists.
Please be sure to read the Simple Filter window's Help as well, if you have not yet done so, because it has more overall information about filters and what you can do with them.
We recommend using this window only if you were already very familiar with filtering with releases of ACCOUNTS prior to 2.24, and prefer to keep doing things that same way, or if you hit the rare requirement that cannot be done with just the Simple Filter window. Such requirements are the ones we will particularly explain in this Help topic.
Here's a specific example. If you click Advanced Filter in the Simple Filter window for the Reports ⇒ Listing ⇒ Revenue and Expense Accounts by Fund report when you have not yet specified a simple filter, you will see the following window with an empty text box under Filter Criteria, where you can type your desired filter criteria. (Filter criteria can also be called filter expressions.)
The list of available columns for filtering is in the box at the top left, and is the same list as is in the Columns drop-down list on the Simple Filter window for that same report. As explained in that window's Help, while the names displayed won't exactly match the column headings on the report, you should be able to easily figure out which is which in almost all reports. (If you aren't sure what a column name means, you can always try filtering on it and see what happens!)
If you have already built one or more rows of Simple Filter, or if the Simple Filter window showed an Input Filter Criteria area showing a previously created advanced filter, it will be displayed in the Filter Criteria box for you to continue working on it. We recommend that you first try building a Simple Filter, and if you decide you need something a bit more complex, click Advanced Filter after that so that your initial work will be displayed in this window.
There are some rules for entering values in this window that are stricter than in the Simple Filter window:
•Numbers cannot contain dollar signs or commas.
•Dates must be in the exact format YYYY-MM-DD, and should not be quoted.
•Values for IN or NOT IN must be in parentheses (round brackets), like:
accountnumber in (1001, 1002)
•Text values must be in either single or double quotation marks. If a value to be quoted itself includes a single quote (or apostrophe, which is the same) or a double quote, you must use the other type of quotes to quote it. For instance, "Bob's Garage" would work but 'Bob's Garage' would not.
•Multiple text values for IN, NOT IN, or BETWEEN must be individually quoted, like:
accountname in ('General Donations', 'Library Donations')
or:
name between 'A' and 'ZZZZZ'
Any filter criteria that do not follow those rules will be invalid, and unfortunately the program is not able to tell you what is wrong, only that it is not valid.
How to Use this Window
If you are typing your Filter Criteria from scratch, there are a couple of helpful shortcuts available.
First, double-clicking a Column name will insert it into that Filter Criteria area, at the current cursor position.
Double-clicking a Function name in the top-right box will insert it, followed by parentheses (round brackets) at the current cursor position. If something like a column name is selected (highlighted) in the editing area, double-clicking a Function name surrounds that selected text with the function name and the parentheses. (Functions are described in detail below.)
After typing your desired Filter Criteria, you can click Verify to have the program tell you whether or not the filter is valid. Clicking OK will first verify the filter, and if it is valid, immediately apply it to the report. Clicking Cancel or pressing ESC closes this window and goes back to the Simple Filter window. Clicking Help shows this help topic.
Changing a filter to be entirely empty (no text shown) then clicking OK will remove the filter from the report.
Functions
There are a lot of functions that are actually available for filter criteria, but to keep things simple this window only shows the four that we think you are most likely to find a use for in the program. Each function name is followed by parentheses, inside which you should put a column name, or possibly a more complex expression.
IsNull: An expression like:
IsNull(transaction_type)
means that that field (Type) is empty.
IsNumber: An expression like:
IsNumber(transaction_number)
means that what is in that field is a number, rather than text (in this case, presumably an actual cheque number).
Number: An expression like:
Number(refno)
converts a transaction reference number (which can include characters other than just numeric digits) to a number. If the value has some digits followed by non-digits, it converts only the digits. If the value doesn't start with a digit, it converts it to 0.
Upper: This converts whatever is within the parentheses to upper case. So suppose you are looking for account names about office stuff, but aren't sure how that word is capitalized. Using the expression:
Upper(account_name) Like "%OFFICE%"
would match any account name that included the word "office", with any capitalization. (Of course, when you are comparing the upper-case value of a column to a quoted text value, that quoted text value must always be all capital letters, or it could not match!)
The very similar Specify Sort Expression window used for advanced sorts has one additional function available, IF, that is unlikely to be needed here for filters, so it is described in that Help section instead.
Operators
The available operators are exactly the ones in the Simple Filter window, but the versions of them that appear in its Output Filter Criteria window.
The case-sensitivity of the various operators (whether they care about matching upper or lower case) is as mentioned in the main Filtering Reports Help page, in the section headed "Dictionary Order".
When using operators that take multiple values, like "In" and "Between", those values must each follow the rules mentioned above about formatting and quoting. For instance:
transaction_date between 2019-04-01 and 2019-06-30
or:
account_name in ('General Donations', 'Library Donations')
And / Or / Not
You can use the logical operators "And" and "Or" to separate multiple criteria, and "Not" to turn a criterion into its opposite.
A number of the Operators on the Simple Filter window already have variations available using "Not", specifically "Not Equals" (which in advanced is just the operator "<>") or "Not In" and "Not Like" (which are actually exactly the same in advanced). You can also precede any criterion with "Not" to reverse its sense, as in:
Not IsNull(transaction_type)
to mean that the Type field has something in it (is not empty).
Many cases where you could use "Not" don't need it. The following:
Not (total_amount > 100)
could instead be replaced by:
total_amount <= 100
Parentheses (round brackets) should always be used around each individual criterion that is separated by "And" or "Or" or uses "Not", to make sure that everything is evaluated properly. For instance:
(total_amount > 100) and (account_name = "General Donations")
Cases when Advanced Filters are Needed
One significant case where you would need to use an Advanced Filter is if you need to mix and match "And" and "Or" logical operators. For instance, in Reports ⇒ Details ⇒ Transactions by Date:
((debit >= 100) or (credit >= 100)) and (account_name = "General Donations")
This will select all transactions for the account "General Donations", where either the credit amount or debit amount is at least a hundred. (Realistically, a donation amount is likely to only be a credit, but this is just to give you an idea of things that can be done.) Note that it is very important that extra parentheses were put around the two criteria about the amount columns that are separated by "or". If that was not done, it would not be clear how the three criteria would be grouped, and you could get entirely the wrong results.
Another case where you would need an Advanced Filter would be if you need to do something like compare the values of two columns to each other. Suppose you use a report that shows two years of data, like Reports ⇒ Summary ⇒ Income Statement Yr/Yr Comparison. That includes two amount fields, which show up as the column names "amount1" (for the latest year) and "amount2" (for the previous year). Let's restrict that report to accounts where this year's amount was less than last year's. To do that, you would use this complex filter expression:
amount1 < amount2
Other cases where you need to use Advanced Filter are of course ones where you need to use any of the functions as described in examples above.
This topic was last edited on Mar 23, 2023