Skip to main content

Building Your First Search

This guide walks through creating a saved search step-by-step.

Example: Open Sales Orders Report#

Business Requirement: Create a report showing all open sales orders with key details for the fulfillment team.

Step 1: Start New Search#

  1. Navigate to Lists > Search > Saved Searches > New

  2. Select Search Type: Transaction

  3. Click Sales Order (or select from dropdown)

Step 2: Name and Configure Basic Settings#

Search Title: Open Sales Orders - Fulfillment View

ID: customsearch_open_so_fulfillment

Public: Check (if sharing with team)

Available for: Select roles (e.g., Warehouse Manager, Fulfillment Team)

Owner: Your user account

Step 3: Set Criteria (Filters)#

Click the Criteria tab.

Add Filters:

  1. Type:

    • Field: Type
    • Operator: is
    • Value: Sales Order

  2. Status:

    • Field: Status
    • Operator: any of
    • Values:
      • Pending Approval
      • Pending Fulfillment
      • Partially Fulfilled

  3. Main Line:

    • Field: Main Line
    • Operator: is
    • Value: Yes
    • (This shows one line per transaction, not item lines)

  4. Ship Date:

    • Field: Ship Date
    • Operator: on or before
    • Value: Next 7 days
    • (Show orders due to ship soon)

Criteria Summary:

Type is Sales OrderAND Status is any of (Pending Approval, Pending Fulfillment, Partially Fulfilled)AND Main Line is YesAND Ship Date is on or before Next 7 days

Step 4: Configure Results (Columns)#

Click the Results tab.

Add Columns:

  1. Order Number:

    • Field: Document Number
    • Summary Type: Group
    • Label: Order #

  2. Customer:

    • Field: Name
    • Summary Type: Group
    • Label: Customer

  3. Order Date:

    • Field: Date
    • Summary Type: Group
    • Label: Order Date

  4. Ship Date:

    • Field: Ship Date
    • Summary Type: Group
    • Label: Ship Date

  5. Days Until Ship (Formula):

    • Field: Formula (Numeric)
    • Formula: {shipdate} - {today}
    • Summary Type: Group
    • Label: Days to Ship

  6. Amount:

    • Field: Amount
    • Summary Type: Sum
    • Label: Order Amount

  7. Status:

    • Field: Status
    • Summary Type: Group
    • Label: Status

  8. Sales Rep:

    • Field: Sales Rep
    • Summary Type: Group
    • Label: Sales Rep

  9. Location:

    • Field: Location
    • Summary Type: Group
    • Label: Ship From

  10. Memo:

    • Field: Memo
    • Summary Type: Group
    • Label: Notes

Sort By:

  • Primary: Ship Date (Ascending)
  • Secondary: Customer (Ascending)

Step 5: Add Highlighting (Optional)#

Click the Highlighting tab to add visual indicators.

Highlight Overdue Orders:

  1. Click Add
  2. Condition:
    • Field: Formula (Numeric)
    • Formula: {shipdate} - {today}
    • Operator: less than
    • Value: 0
  3. Style:
    • Background Color: Red
    • Text Color: White
    • Font: Bold

Highlight Rush Orders:

  1. Click Add
  2. Condition:
    • Field: Custom Body Field (Rush Order)
    • Operator: is
    • Value: T (True)
  3. Style:
    • Background Color: Orange
    • Font: Bold

Step 6: Configure Available Filters (Optional)#

Click the Available Filters tab to add end-user filters.

Add Filters:

  1. Customer:

    • Allows users to filter by specific customer

  2. Sales Rep:

    • Filter by sales representative

  3. Location:

    • Filter by ship-from location

  4. Date Range:

    • Custom date range selection

These filters appear at the top when users run the search, allowing them to narrow results without editing the search.

Step 7: Save and Test#

  1. Click Save

  2. The search will run automatically

  3. Review results:

    • Check data accuracy
    • Verify calculations
    • Confirm formatting
    • Test highlighting

  4. Make adjustments if needed

Step 8: Share and Deploy#

  1. Set Permissions:

    • Go to search settings
    • Configure audience
    • Grant view/edit rights

  2. Add to Dashboard:

    • Create dashboard portlet
    • Assign to roles

  3. Train Users:

    • Show how to run search
    • Explain available filters
    • Document purpose

Search Creation Patterns#

Transaction Searches#

Standard Structure:

Criteria:  - Transaction Type (required)  - Status filters  - Date range  - Main Line filter (usually needed)  Results:  - Document Number  - Customer/Vendor  - Date  - Amount  - Status  - Custom fields as needed

Customer/Vendor Searches#

Standard Structure:

Criteria:  - Entity Type  - Status: Active  - Classification filters  Results:  - Name  - Email  - Phone  - Classification  - Financial summary fields  - Last transaction date

Item Searches#

Standard Structure:

Criteria:  - Item Type  - Inactive: No  - Category/Class filters  Results:  - Item Name  - SKU  - Quantity Available  - Reorder Point  - Price levels  - Vendor information

Activity Searches#

Standard Structure:

Criteria:  - Activity Type (Task, Event, etc.)  - Status  - Date range  - Assigned to  Results:  - Title/Subject  - Assigned To  - Due Date/Start Time  - Status  - Company  - Priority

Step-by-Step: Customer Sales Analysis#

Let's create a more complex search.

Goal#

Analyze customer sales performance with year-over-year comparison.

Step 1: Setup#

Search Type: Customer Title: Customer Sales Analysis - YoY Public: Yes Audience: Sales Team, Management

Step 2: Criteria#

Criteria:  1. Type: Customer  2. Status: Active  3. Formula (Numeric): {salesreadonly} > 0     (Only customers with sales)

Step 3: Results with Formulas#

Column 1: Customer Name

  • Field: Name
  • Summary: Group

Column 2: Current Year Sales

  • Field: Formula (Currency)
  • Formula: 
    CASE WHEN TO_CHAR({transaction.trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY')     AND {transaction.type} = 'Invoice'     THEN {transaction.amount}     ELSE 0 END
  • Summary: Sum
  • Label: 2025 Sales

Column 3: Prior Year Sales

  • Field: Formula (Currency)
  • Formula:
    CASE WHEN TO_CHAR({transaction.trandate}, 'YYYY') = TO_CHAR(ADD_MONTHS({today}, -12), 'YYYY')     AND {transaction.type} = 'Invoice'     THEN {transaction.amount}     ELSE 0 END
  • Summary: Sum
  • Label: 2024 Sales

Column 4: YoY Growth %

  • Field: Formula (Percent)
  • Formula:
    CASE WHEN [2024 Sales] > 0     THEN (([2025 Sales] - [2024 Sales]) / [2024 Sales]) * 100     ELSE 0 END
  • Summary: Group
  • Label: YoY Growth %

Column 5: Number of Orders (Current Year)

  • Field: Formula (Numeric)
  • Formula:
    CASE WHEN TO_CHAR({transaction.trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY')     AND {transaction.type} = 'Invoice'     THEN 1 ELSE 0 END
  • Summary: Sum
  • Label: 2025 Orders

Column 6: Average Order Value

  • Field: Formula (Currency)
  • Formula: [2025 Sales] / [2025 Orders]
  • Summary: Group
  • Label: Avg Order Value

Column 7: Last Order Date

  • Field: Formula (Date)
  • Formula:
    MAX(CASE WHEN {transaction.type} = 'Invoice'    THEN {transaction.trandate} END)
  • Summary: Maximum
  • Label: Last Order

Column 8: Sales Rep

  • Field: Sales Rep
  • Summary: Group

Column 9: Customer Tier

  • Field: Custom Entity Field (custentity_tier)
  • Summary: Group

Step 4: Highlighting#

High Growth (>50%)

  • Condition: YoY Growth % > 50
  • Style: Green background

Declining (-10% or more)

  • Condition: YoY Growth % < -10
  • Style: Red background

At Risk (No orders in 90 days)

  • Condition: Last Order Date < {today} - 90
  • Style: Orange background

Step 5: Sorting#

  • Primary: 2025 Sales (Descending)
  • Secondary: Customer Name (Ascending)

Step 6: Available Filters#

  • Sales Rep
  • Customer Tier
  • Minimum Sales Amount

Creating Different Search Types#

Transaction Line Search#

For item-level detail on transactions.

When to Use:

  • Analyze individual items sold
  • Quantity analysis
  • Line-level custom fields
  • Product performance

Example: Items Sold This Month

Search Type: Transaction (Lines)Criteria:  - Type: Invoice  - Date: This Month  - Main Line: No (show item lines)  Results:  - Item Name  - Quantity (Sum)  - Amount (Sum)  - Number of Transactions (Count)  - Average Price  Summary:  - Group by Item  - Sort by Quantity descending

Joined Search#

Connect multiple record types.

When to Use:

  • Need data from related records
  • Cross-record analysis
  • Complex relationships

Example: Customers with Open Orders and Overdue Invoices

Search Type: CustomerCriteria:  - Transaction (Sales Order): Status = Open  - Transaction (Invoice): Status = Open AND Due Date < Today  Results:  - Customer Name  - Open SO Count  - Open SO Amount (Sum)  - Overdue Invoice Count  - Overdue Amount (Sum)  - Days Overdue (Average)

Summary Search with Multiple Group Levels#

Example: Sales by Year, Quarter, Month

Search Type: TransactionCriteria:  - Type: Invoice  - Date: Last 2 Years  - Main Line: Yes  Results:  - Year (Formula: TO_CHAR({trandate}, 'YYYY'))  - Quarter (Formula: 'Q' || TO_CHAR({trandate}, 'Q'))  - Month (Formula: TO_CHAR({trandate}, 'Month'))  - Sales Amount (Sum)  - Order Count (Count)  Summary:  - Group by Year, Quarter, Month  - Show subtotals at each level

Best Practices#

Naming Conventions#

Clear, Descriptive Names:

Good:  - "Open Sales Orders - Fulfillment View"  - "Customers - Top 100 by Revenue"  - "Items Below Reorder Point"  - "Employee Tasks - Due This Week"
Avoid:  - "My Search"  - "Test 123"  - "Sales Report"  - "Search 1"

Search Organization#

Use Prefixes:

By Department:  - SALES - Top Customers  - FIN - Aged Receivables  - OPS - Open Purchase Orders  - HR - Employee Directory
By Purpose:  - RPT - Monthly Sales Report  - LIST - Active Customer List  - ALERT - Low Inventory Alert  - DASH - Dashboard Metrics

Documentation#

Description Field: Always fill in the description:

Example:"Shows all open sales orders with ship dates in the next 7 days.Used by fulfillment team for daily planning.Highlights overdue orders in red, rush orders in orange.Updated: 2025-01-15Owner: Operations Manager"

Performance Optimization#

Start Specific:

Do:  - Add restrictive criteria first  - Use specific date ranges  - Filter by type and status  - Limit result count
Avoid:  - Open-ended searches  - No date restrictions  - Too many formula columns  - Unlimited results

Testing Process#

  1. Test with Sample Data:

    • Create test records
    • Verify calculations
    • Check edge cases

  2. Test Performance:

    • Run with full data set
    • Check execution time
    • Monitor user experience

  3. Test Filters:

    • Try all available filters
    • Test filter combinations
    • Verify default values

  4. Test Exports:

    • Export to Excel
    • Check CSV format
    • Verify PDF output

  5. User Acceptance:

    • Have end users test
    • Gather feedback
    • Make adjustments

Common Mistakes to Avoid#

Criteria Mistakes#

Too Broad:

Problem: No date filter on transaction searchResult: Searches all history, very slow
Solution: Add date range criteria

Missing Main Line:

Problem: Transaction search without main line filterResult: Multiple rows per transaction
Solution: Add "Main Line is Yes" for transaction-level data

Wrong Operators:

Problem: Using "is" instead of "any of" for multiple valuesResult: No results or incorrect filtering
Solution: Use correct operator for multiple selections

Results Mistakes#

Too Many Columns:

Problem: 50+ columns in resultsResult: Slow performance, hard to read
Solution: Only include essential columns

Wrong Summary Type:

Problem: Using "Group" instead of "Sum" for amountsResult: Incorrect totals
Solution: Use appropriate summary for each field

No Sorting:

Problem: Results in random orderResult: Hard to analyze
Solution: Add meaningful sort order

Formula Mistakes#

Syntax Errors:

Problem: {ammount} (typo in field name)Result: Formula fails
Solution: Verify field names carefully

Division by Zero:

Problem: {field1} / {field2} when field2 might be 0Result: Error
Solution: Use CASE to handle zeros:CASE WHEN {field2} != 0 THEN {field1} / {field2} ELSE 0 END

Date Format Issues:

Problem: Comparing dates as stringsResult: Incorrect comparisons
Solution: Use proper date functions

Troubleshooting#

Search Returns No Results#

Check:

  1. Criteria too restrictive?
  2. Date range excludes data?
  3. Status filters correct?
  4. Record type matches?
  5. Data actually exists?

Search is Very Slow#

Solutions:

  1. Add more specific criteria
  2. Reduce number of formula columns
  3. Limit result rows
  4. Use indexed fields in criteria
  5. Consider scheduled search

Wrong Data Appears#

Check:

  1. Main line filter correct?
  2. Transaction type filter?
  3. Formula logic correct?
  4. Summary types appropriate?
  5. Join conditions correct?

Formula Not Working#

Debug Steps:

  1. Test formula in simple search
  2. Check field references
  3. Verify syntax
  4. Test with known data
  5. Check for nulls