Filters & Operators
Master the filter operators to create precise, efficient searches.
Filter Basics
FILTER STRUCTURE
═══════════════════════════════════════════════════════════════════════════════
Every filter has three parts:
┌─────────────────┬─────────────────┬─────────────────────────────────────────┐
│ FIELD │ OPERATOR │ VALUE(S) │
├─────────────────┼─────────────────┼─────────────────────────────────────────┤
│ What to filter │ How to compare │ What to compare against │
├─────────────────┼─────────────────┼─────────────────────────────────────────┤
│ Date │ is within │ This Month │
│ Amount │ greater than │ 1000 │
│ Status │ is any of │ Open, Pending │
│ Name │ contains │ Corp │
└─────────────────┴─────────────────┴─────────────────────────────────────────┘
Text Operators
| Operator | Description | Example |
|---|---|---|
| is | Exact match | Name is "Acme Corp" |
| is not | Not exact match | Name is not "Acme Corp" |
| contains | Contains text | Name contains "Corp" |
| does not contain | Text not found | Name does not contain "Test" |
| starts with | Begins with text | Name starts with "A" |
| does not start with | Doesn't begin with | Name does not start with "Test" |
| is empty | No value | Notes is empty |
| is not empty | Has any value | Notes is not empty |
TEXT OPERATOR EXAMPLES
═══════════════════════════════════════════════════════════════════════════════
Field: Company Name = "Acme Corporation"
is "Acme Corporation" ✓ Match (exact)
is "Acme" ✗ No match (partial)
is "acme corporation" ✓ Match (case insensitive)
contains "Corp" ✓ Match
contains "Inc" ✗ No match
starts with "Acme" ✓ Match
starts with "Corp" ✗ No match
is empty ✗ No match (has value)
is not empty ✓ Match
Number Operators
| Operator | Description | Example |
|---|---|---|
| equal to | Equals value | Amount equal to 1000 |
| not equal to | Not equals | Amount not equal to 0 |
| less than | Below value | Quantity less than 10 |
| less than or equal | At or below | Quantity less than or equal 10 |
| greater than | Above value | Amount greater than 5000 |
| greater than or equal | At or above | Amount greater than or equal 5000 |
| between | In range (inclusive) | Amount between 1000 and 5000 |
| not between | Outside range | Amount not between 0 and 100 |
| is empty | No value | Discount is empty |
| is not empty | Has value | Commission is not empty |
NUMBER OPERATOR EXAMPLES
═══════════════════════════════════════════════════════════════════════════════
Field: Amount = 2500
equal to 2500 ✓ Match
equal to 2500.00 ✓ Match
greater than 2000 ✓ Match
greater than 2500 ✗ No match
greater than or equal 2500 ✓ Match
less than 3000 ✓ Match
between 2000 and 3000 ✓ Match
between 3000 and 4000 ✗ No match
Date Operators
| Operator | Description | Example |
|---|---|---|
| on | Exact date | Date on 12/25/2024 |
| not on | Not that date | Date not on 01/01/2024 |
| before | Before date | Date before 01/01/2025 |
| on or before | On or before | Due Date on or before 12/31/2024 |
| after | After date | Date after 01/01/2024 |
| on or after | On or after | Date on or after 01/01/2024 |
| is within | Within period | Date is within This Month |
| is not within | Outside period | Date is not within Last Year |
| is empty | No date set | Ship Date is empty |
| is not empty | Has date | Close Date is not empty |
Relative Date Values
RELATIVE DATE VALUES
═══════════════════════════════════════════════════════════════════════════════
TODAY/YESTERDAY/TOMORROW
┌─────────────────────────────────────────────────────────────────────────────┐
│ today Current date │
│ yesterday Previous day │
│ tomorrow Next day │
└─────────────────────────────────────────────────────────────────────────────┘
WEEKS
┌─────────────────────────────────────────────────────────────────────────────┐
│ this week Current week (Sun-Sat or Mon-Sun based on settings) │
│ last week Previous full week │
│ next week Coming full week │
└─────────────────────────────────────────────────────────────────────────────┘
MONTHS
┌─────────────────────────────────────────────────────────────────────────────┐
│ this month Current calendar month │
│ last month Previous calendar month │
│ next month Coming calendar month │
└─────────────────────────────────────────────────────────────────────────────┘
QUARTERS
┌─────────────────────────────────────────────────────────────────────────────┐
│ this quarter Current fiscal quarter │
│ last quarter Previous fiscal quarter │
│ next quarter Coming fiscal quarter │
└─────────────────────────────────────────────────────────────────────────────┘
YEARS
┌─────────────────────────────────────────────────────────────────────────────┐
│ this year Current fiscal year │
│ last year Previous fiscal year │
│ next year Coming fiscal year │
└─────────────────────────────────────────────────────────────────────────────┘
FISCAL PERIODS
┌─────────────────────────────────────────────────────────────────────────────┐
│ this fiscal period Current accounting period │
│ last fiscal period Previous accounting period │
│ next fiscal period Coming accounting period │
└─────────────────────────────────────────────────────────────────────────────┘
ROLLING DATES
┌─────────────────────────────────────────────────────────────────────────────┐
│ last 7 days Past 7 days including today │
│ last 30 days Past 30 days including today │
│ last 60 days Past 60 days including today │
│ last 90 days Past 90 days including today │
│ last 365 days Past 365 days including today │
│ │
│ next 7 days Coming 7 days from today │
│ next 30 days Coming 30 days from today │
│ next 90 days Coming 90 days from today │
└─────────────────────────────────────────────────────────────────────────────┘
CUSTOM N DAYS
┌─────────────────────────────────────────────────────────────────────────────┐
│ N days ago Specific number of days in past │
│ N days from now Specific number of days in future │
│ N weeks ago Specific number of weeks in past │
│ N weeks from now Specific number of weeks in future │
└─────────────────────────────────────────────────────────────────────────────┘
List/Select Operators
| Operator | Description | Example |
|---|---|---|
| is any of | Matches any selected | Status is any of Open, Pending |
| is none of | Matches none selected | Type is none of Credit Memo |
LIST OPERATOR EXAMPLES
═══════════════════════════════════════════════════════════════════════════════
Transaction Type Filter:
"is any of" [Sales Order, Invoice]
→ Returns Sales Orders AND Invoices
"is none of" [Credit Memo, Customer Refund]
→ Returns all types EXCEPT Credit Memos and Refunds
Status Filter:
"is any of" [Open, Pending Approval, Pending Fulfillment]
→ Returns transactions with ANY of these statuses
Checkbox Operators
| Operator | Description | Example |
|---|---|---|
| is | Yes or No | Main Line is Yes |
CHECKBOX FILTER EXAMPLES
═══════════════════════════════════════════════════════════════════════════════
Main Line is Yes → Only header/main line rows
Main Line is No → Only line item rows
Inactive is No → Only active records
Inactive is Yes → Only inactive records
Tax Line is No → Exclude tax lines
Shipping is Yes → Only shipping records
Filter Expressions
For complex logic, use Use Expressions.
FILTER EXPRESSIONS
═══════════════════════════════════════════════════════════════════════════════
Default Logic (without expressions):
Filter 1 AND Filter 2 AND Filter 3 AND Filter 4
All filters must match
With Expressions:
( 1 AND 2 ) OR ( 3 AND 4 )
Either group can match
EXAMPLE: Find open invoices OR overdue sales orders
Filters:
1. Type is Invoice
2. Status is Open
3. Type is Sales Order
4. Due Date before today
Expression: ( 1 AND 2 ) OR ( 3 AND 4 )
Result: Returns open invoices OR overdue sales orders
Expression Syntax
EXPRESSION SYNTAX
═══════════════════════════════════════════════════════════════════════════════
Operators:
AND Both conditions must be true
OR Either condition must be true
NOT Negates the condition
Grouping:
( ) Group conditions together
Examples:
┌─────────────────────────────────────────────────────────────────────────────┐
│ 1 AND 2 AND 3 All three must match │
│ 1 OR 2 OR 3 Any one must match │
│ ( 1 AND 2 ) OR 3 Both 1&2, or just 3 │
│ 1 AND ( 2 OR 3 ) 1 must match, plus either 2 or 3 │
│ ( 1 AND 2 ) OR ( 3 AND 4 ) Either first pair or second pair │
│ NOT 1 Everything except filter 1 │
│ 1 AND NOT 2 Filter 1 but not filter 2 │
└─────────────────────────────────────────────────────────────────────────────┘
Special Filter Fields
Main Line
MAIN LINE FILTER
═══════════════════════════════════════════════════════════════════════════════
Main Line = Yes (T)
┌─────────────────────────────────────────────────────────────────────────────┐
│ Returns ONE row per transaction │
│ Contains header-level totals │
│ Use for: Summary reports, transaction counts │
└─────────────────────────────────────────────────────────────────────────────┘
Main Line = No (F)
┌─────────────────────────────────────────────────────────────────────────────┐
│ Returns ONE row per LINE ITEM │
│ Contains line-level details (items, quantities) │
│ Use for: Item-level analysis, detailed reports │
└─────────────────────────────────────────────────────────────────────────────┘
⚠️ No Main Line Filter
┌─────────────────────────────────────────────────────────────────────────────┐
│ Returns BOTH header AND line rows │
│ DANGER: Causes duplicate totals in reports! │
│ AVOID: Unless you specifically need both │
└─────────────────────────────────────────────────────────────────────────────┘
Transaction Type
TRANSACTION TYPE FILTER
═══════════════════════════════════════════════════════════════════════════════
When searching "Transaction" record type, specify which types:
Sales Transactions:
Sales Order Sales orders
Invoice Customer invoices
Cash Sale Cash transactions
Credit Memo Customer credits
Purchase Transactions:
Purchase Order POs to vendors
Bill Vendor bills
Bill Credit Vendor credits
Inventory Transactions:
Item Receipt Receiving inventory
Item Fulfillment Shipping inventory
Inventory Adjustment Quantity adjustments
Transfer Order Location transfers
Financial Transactions:
Journal Entry GL journal entries
Deposit Bank deposits
Customer Payment Customer payments
Vendor Payment Vendor payments
Status Values
STATUS FILTER VALUES
═══════════════════════════════════════════════════════════════════════════════
Status values are TYPE-SPECIFIC. Format: RecordType:Status
Sales Order Statuses:
┌─────────────────────────────────────────────────────────────────────────────┐
│ SalesOrd:A Pending Approval │
│ SalesOrd:B Pending Fulfillment │
│ SalesOrd:C Cancelled │
│ SalesOrd:D Partially Fulfilled │
│ SalesOrd:E Pending Billing/Partially Fulfilled │
│ SalesOrd:F Pending Billing │
│ SalesOrd:G Billed │
│ SalesOrd:H Closed │
└─────────────────────────────────────────────────────────────────────────────┘
Invoice Statuses:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CustInvc:A Open │
│ CustInvc:B Paid In Full │
└─────────────────────────────────────────────────────────────────────────────┘
Purchase Order Statuses:
┌─────────────────────────────────────────────────────────────────────────────┐
│ PurchOrd:A Pending Supervisor Approval │
│ PurchOrd:B Pending Receipt │
│ PurchOrd:C Rejected by Supervisor │
│ PurchOrd:D Partially Received │
│ PurchOrd:E Pending Billing/Partially Received │
│ PurchOrd:F Pending Bill │
│ PurchOrd:G Fully Billed │
│ PurchOrd:H Closed │
└─────────────────────────────────────────────────────────────────────────────┘
Filter Performance Tips
FILTER PERFORMANCE BEST PRACTICES
═══════════════════════════════════════════════════════════════════════════════
DO:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✓ Always filter by Type for transaction searches │
│ ✓ Always set Main Line filter │
│ ✓ Add Date range filter to limit results │
│ ✓ Use indexed fields (ID, Date, Type, Status) for primary filters │
│ ✓ Put most restrictive filter first │
└─────────────────────────────────────────────────────────────────────────────┘
AVOID:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✗ Searching without Type filter │
│ ✗ "Contains" on large text fields │
│ ✗ Complex formula filters on large datasets │
│ ✗ "is not empty" as primary filter (scans all records) │
│ ✗ Nested OR expressions with many conditions │
└─────────────────────────────────────────────────────────────────────────────┘
INDEXED FIELDS (fastest filtering):
┌─────────────────────────────────────────────────────────────────────────────┐
│ Internal ID, Date, Type, Status, Entity, Account, Subsidiary, Department │
└─────────────────────────────────────────────────────────────────────────────┘
Next Steps
| Goal | Go To |
|---|---|
| Configure result columns | Result Columns → |
| Add formula calculations | Formulas → |
| Set up alerts | Advanced Features → |