Skip to main content

Search Criteria

Understanding Criteria#

Criteria (filters) determine which records appear in your search results. Think of criteria as the WHERE clause in a SQL query - they define what data to include or exclude.

Criteria Tab Basics#

Filter Structure#

Each filter consists of:

  1. Field: What to filter on
  2. Operator: How to compare
  3. Value: What to compare against

Example:

Field: AmountOperator: greater thanValue: 5000

Filter Logic#

AND Logic (Default): All criteria must be true

Type is Sales OrderAND Status is OpenAND Amount > 5000Result: Only records matching ALL conditions

OR Logic: At least one criterion must be true

Use parentheses () to group OR conditionsOr use "any of" operatorOr create expression

Common Operators#

Equals Operators#

is

  • Exact match
  • Single value
  • Example: Status is Approved

is not

  • Excludes exact match
  • Example: Status is not Cancelled

any of

  • Matches any in list
  • Multiple values
  • Example: Status is any of (Open, Pending Approval, Pending Fulfillment)

none of

  • Excludes all in list
  • Example: Status is none of (Cancelled, Closed, Billed)

Comparison Operators#

greater than (>)

  • Numeric/date values
  • Example: Amount > 5000

greater than or equal to (>=)

  • Inclusive comparison
  • Example: Amount >= 5000

less than (<)

  • Example: Due Date < Today

less than or equal to (<=)

  • Example: Due Date <= Next Week

between

  • Range of values
  • Example: Amount between 1000 and 10000

Text Operators#

contains

  • Substring match
  • Case insensitive
  • Example: Name contains "Corp"

does not contain

  • Excludes substring
  • Example: Name does not contain "Test"

starts with

  • Prefix match
  • Example: Document Number starts with "SO-"

is empty

  • No value present
  • Example: Email is empty

is not empty

  • Has value
  • Example: Email is not empty

Date Operators#

on

  • Specific date
  • Example: Date on 01/01/2025

on or before

  • Up to and including
  • Example: Ship Date on or before Today

on or after

  • From date forward
  • Example: Order Date on or after First Day of This Month

before

  • Prior to date (exclusive)
  • Example: Due Date before Today

after

  • Following date (exclusive)
  • Example: Created Date after Last Month

within

  • Date range
  • Example: Date within This Month

not within

  • Outside date range
  • Example: Date not within Last Year

Special Operators#

User Operators:

  • is {current user}
  • is not {current user}
  • Example: Created By is {current user}

Role Operators:

  • is {current user's role}
  • is not {current user's role}

Department/Location:

  • is {current user's department}
  • is {current user's location}

Date Criteria#

Relative Dates#

Common Relative Values:

  • Today
  • Yesterday
  • Tomorrow
  • This Week
  • Last Week
  • Next Week
  • This Month
  • Last Month
  • Next Month
  • This Quarter
  • Last Quarter
  • This Year
  • Last Year

Usage Examples:

Orders This Month:

Field: DateOperator: withinValue: This Month

Invoices Due Soon:

Field: Due DateOperator: withinValue: Next 7 Days

Historical Data:

Field: DateOperator: withinValue: Last Year

Absolute Dates#

Specific Dates:

Field: DateOperator: onValue: 01/15/2025

Date Ranges:

Field: DateOperator: withinFrom: 01/01/2025To: 12/31/2025

Date Formulas#

Calculate Relative Dates:

90 Days Ago:

Field: Formula (Date)Formula: {today} - 90Operator: on or after

Days Until Due:

Field: Formula (Numeric)Formula: {duedate} - {today}Operator: less thanValue: 0(Shows overdue)

Numeric Criteria#

Amount Ranges#

Exact Amount:

Field: AmountOperator: isValue: 5000

Minimum Amount:

Field: AmountOperator: greater than or equal toValue: 1000

Range:

Field: AmountOperator: betweenValue: 1000 and 10000

Multiple Ranges (OR logic):

Use expressions:(Amount >= 1000 AND Amount < 5000)OR (Amount >= 10000 AND Amount < 25000)

Percentage Criteria#

Discount Greater Than:

Field: Discount PercentOperator: greater thanValue: 10

Quantity Criteria#

Low Stock:

Field: Quantity AvailableOperator: less than or equal toValue: {Reorder Point}

Text Criteria#

Name Searches#

Exact Match:

Field: NameOperator: isValue: Acme Corporation

Partial Match:

Field: NameOperator: containsValue: Acme

Multiple Names:

Field: NameOperator: any ofValues: Acme Corporation, Beta Inc, Gamma LLC

Email Criteria#

Has Email:

Field: EmailOperator: is not empty

Domain Match:

Field: EmailOperator: containsValue: @company.com

Status Criteria#

Single Status:

Field: StatusOperator: isValue: Pending Approval

Multiple Statuses:

Field: StatusOperator: any ofValues: Open, Pending Approval, Pending Fulfillment

Exclude Status:

Field: StatusOperator: none ofValues: Cancelled, Closed, Voided

Formula Criteria#

Formula (Boolean)#

Returns TRUE or FALSE to include/exclude records.

Syntax:

Field: Formula (Boolean)Formula: {your formula}Operator: isValue: Yes

Examples:

Credit Limit Exceeded:

Formula: {balance} > {creditlimit}

High Value Orders:

Formula: {amount} > 10000 AND {status} = 'Pending Approval'

Overdue:

Formula: {duedate} < {today} AND {status} = 'Open'

Complex Logic:

Formula: ({amount} > 5000 AND {entity.custentity_tier} = 'Premium')OR({amount} > 10000 AND {entity.custentity_tier} = 'Standard')

Formula (Numeric)#

Calculate numeric value for comparison.

Days Overdue:

Field: Formula (Numeric)Formula: {today} - {duedate}Operator: greater thanValue: 30

Margin Percentage:

Field: Formula (Numeric)Formula: (({amount} - {custbody_cost}) / {amount}) * 100Operator: less thanValue: 20

Inventory Days Supply:

Field: Formula (Numeric)Formula: {quantityavailable} / {custitem_avg_daily_usage}Operator: less thanValue: 30

Formula (Text)#

Generate text value for comparison.

First Letter of Name:

Field: Formula (Text)Formula: SUBSTR({companyname}, 1, 1)Operator: isValue: A

Year from Date:

Field: Formula (Text)Formula: TO_CHAR({trandate}, 'YYYY')Operator: isValue: 2025

Formula (Date)#

Calculate date for comparison.

Last Day of Month:

Field: Formula (Date)Formula: LAST_DAY({trandate})Operator: isValue: {today}

Advanced Filter Patterns#

Combining AND/OR Logic#

Using Expressions:

Create an expression for complex OR logic:

Expression 1:  (Amount > 10000 AND Customer Tier = 'Premium')
Expression 2:  (Amount > 25000)
Result: Records matching Expression 1 OR Expression 2

Nested Logic:

Main Criteria (all must be true):  - Type is Sales Order  - Status is Open  Expression (at least one must be true):  - (Amount > 10000 AND Ship Date < Today + 7)  - (Rush Order = Yes)

Multiple Value Matching#

Any Match:

Field: Sales RepOperator: any ofValues: Rep1, Rep2, Rep3

All Match (Multiple Criteria):

Criteria 1: Has Tag "VIP"Criteria 2: Has Tag "Active"Criteria 3: Has Tag "Priority"

Exclude Multiple:

Field: StatusOperator: none ofValues: Cancelled, Voided, Closed

Range Filters#

Date Range:

Field: DateOperator: withinFrom: 01/01/2025To: 03/31/2025

Numeric Range:

Field: AmountOperator: betweenValue: 5000 and 10000

Multiple Ranges:

Expression:   (Amount >= 1000 AND Amount < 5000)  OR (Amount >= 10000 AND Amount < 25000)  OR (Amount >= 50000)

Transaction-Specific Criteria#

Main Line Filter#

Transaction Level (One Row Per Transaction):

Field: Main LineOperator: isValue: Yes

Item Lines (Multiple Rows Per Transaction):

Field: Main LineOperator: isValue: No

Transaction Type#

Single Type:

Field: TypeOperator: isValue: Sales Order

Multiple Types:

Field: TypeOperator: any ofValues: Sales Order, Invoice, Cash Sale

Transaction Status#

Common Status Filters:

Open Transactions:

Field: StatusOperator: any ofValues: Open, Pending Approval, Pending Fulfillment

Closed Transactions:

Field: StatusOperator: any ofValues: Billed, Closed, Paid in Full

Exclude Cancelled:

Field: StatusOperator: is notValue: Cancelled

Entity-Specific Criteria#

Customer Criteria#

Active Customers Only:

Field: StatusOperator: isValue: Active - Customer

By Classification:

Field: Category (or custom classification)Operator: isValue: Wholesale

Has Credit Hold:

Field: Credit HoldOperator: isValue: On Hold

By Sales Rep:

Field: Sales RepOperator: isValue: John Smith

Vendor Criteria#

Active Vendors:

Field: StatusOperator: isValue: Active - Vendor

By Category:

Field: CategoryOperator: any ofValues: Category1, Category2

Item Criteria#

Active Items:

Field: InactiveOperator: isValue: No

By Type:

Field: TypeOperator: any ofValues: Inventory Item, Non-Inventory Item, Service

Has Stock:

Field: Quantity AvailableOperator: greater thanValue: 0

Custom Field Criteria#

Using Custom Fields#

Custom Body Field:

Field: Main (Custom Body Field Name)Example: Approval StatusOperator: isValue: Approved

Custom Entity Field:

Field: Customer: Custom Field NameExample: Customer: Customer TierOperator: isValue: Premium

Custom Line Field:

Field: Item: Custom Field NameExample: Item: Product CategoryOperator: isValue: Electronics

Custom Field Examples#

Project Code:

Field: Project Code (Custom Body Field)Operator: is not empty

Risk Rating:

Field: Customer: Risk RatingOperator: any ofValues: Medium, High

Product Line:

Field: Item: Product LineOperator: isValue: Premium Products

Best Practices#

Criteria Design#

Start Specific:

Good Order:1. Type (most restrictive)2. Status (very restrictive)3. Date Range (restrictive)4. Other filters (less restrictive)
This order processes fastest

Use Indexed Fields:

Fast:  - Type  - Status  - Date  - Internal ID
Slower:  - Formula fields  - Custom fields (some)  - Text contains searches

Avoid Open-Ended:

Avoid:  - No date filter on transactions  - No type filter  - Very broad text searches
Use:  - Specific date ranges  - Specific types  - Targeted text searches

Performance Optimization#

Limit Result Set:

Add restrictive criteria first:  - Date within last 90 days  - Status is Open  - Type is specific type

Use Direct Matches:

Fast:  Status is "Open"  Slower:  Status contains "Open"

Minimize Formula Criteria:

If possible, use direct field comparison instead of formula

Testing Criteria#

Test Incrementally:

  1. Add one criterion
  2. Run search
  3. Verify results
  4. Add next criterion
  5. Repeat

Test Edge Cases:

  • Empty/null values
  • Boundary values
  • Special characters
  • Large numbers

Test Combinations:

  • Multiple criteria together
  • AND vs OR logic
  • Conflicting criteria

Common Issues#

No Results Returned#

Check:

  1. Criteria too restrictive?
  2. All criteria compatible?
  3. Date range correct?
  4. Values spelled correctly?
  5. Data actually exists?

Debug:

  • Remove criteria one at a time
  • Start with minimal criteria
  • Add back gradually

Too Many Results#

Solutions:

  1. Add more specific criteria
  2. Narrow date range
  3. Add status filters
  4. Limit by type

Wrong Results#

Verify:

  1. Operators correct?
  2. Values correct?
  3. Logic (AND/OR) correct?
  4. Formula syntax correct?
  5. Field references correct?

Slow Performance#

Optimize:

  1. Add restrictive criteria first
  2. Use specific date ranges
  3. Limit formula criteria
  4. Use indexed fields
  5. Set result limits

Criteria Examples by Use Case#

Financial Analysis#

Aged Receivables (30+ Days):

Type: InvoiceStatus: OpenFormula (Numeric): {today} - {duedate}  Operator: greater than or equal to  Value: 30Main Line: Yes

High Value Transactions:

Type: any of (Sales Order, Invoice, Cash Sale)Amount: greater than 50000Date: within This YearMain Line: Yes

Sales Analysis#

Won Opportunities:

Type: OpportunityProbability: is 100%Close Date: within This Quarter

Pipeline by Stage:

Type: OpportunityStatus: is not Closed LostStatus: is not Closed Won

Inventory Management#

Reorder Needed:

Type: Inventory ItemInactive: is NoFormula (Boolean): {quantityavailable} <= {reorderpoint}

Slow Moving:

Type: Inventory ItemFormula (Numeric): {today} - {lastpurchasedate}  Operator: greater than  Value: 180Quantity Available: greater than 0

Operations#

Overdue Tasks:

Type: TaskStatus: any of (Not Started, In Progress)Due Date: before Today

High Priority Open Cases:

Type: Support CaseStatus: is not ClosedPriority: any of (High, Highest)