Skip to main content

Search Results

Understanding the Results Tab#

The Results tab controls what columns appear in your search output, how data is calculated, formatted, and organized.

Column Basics#

Adding Columns#

  1. Click Add in Results tab
  2. Select Field
  3. Configure column properties:
    • Field name
    • Summary type
    • Label (custom column header)
    • Function
    • Formula

Column Properties#

Field:

  • Select from available fields
  • Standard or custom fields
  • Formula fields

Summary Type:

  • How to aggregate data
  • Required for summary searches
  • Options: Group, Sum, Count, Average, etc.

Custom Label:

  • Override default column header
  • Make headers user-friendly
  • Keep concise but clear

Function:

  • Additional calculations
  • Month, Year, Day from dates
  • Text manipulation

Formula:

  • Custom calculations
  • Complex logic
  • Conditional values

Summary Types#

Group#

Use when: Grouping/organizing data by this field

Example: Group by Customer

Field: Name (Customer)Summary Type: GroupResult: One row per unique customer

Common Group Fields:

  • Customer/Vendor names
  • Item names
  • Sales Rep
  • Department
  • Date (for time grouping)
  • Status

Sum#

Use when: Adding up numeric values

Example: Total Sales

Field: AmountSummary Type: SumResult: Total of all amounts

Common Sum Fields:

  • Amount
  • Quantity
  • Cost
  • Revenue
  • Balances

Count#

Use when: Counting occurrences

Example: Number of Orders

Field: Internal IDSummary Type: CountResult: Count of records

Useful for:

  • Transaction counts
  • Customer counts
  • Item counts
  • Activity counts

Average#

Use when: Calculating mean values

Example: Average Order Value

Field: AmountSummary Type: AverageResult: Mean of all amounts

Common Average Fields:

  • Order amounts
  • Delivery times
  • Quantities
  • Prices

Maximum#

Use when: Finding highest value

Example: Largest Order

Field: AmountSummary Type: MaximumResult: Highest amount value

Useful for:

  • Latest date
  • Highest price
  • Maximum quantity
  • Peak values

Minimum#

Use when: Finding lowest value

Example: Earliest Order Date

Field: DateSummary Type: MinimumResult: Earliest date

Useful for:

  • First occurrence
  • Lowest price
  • Minimum quantity
  • Starting values

Formula Columns#

Formula Types#

Formula (Numeric):

  • Mathematical calculations
  • Returns numbers
  • Can use math operators

Formula (Currency):

  • Monetary calculations
  • Returns formatted currency
  • Respects currency settings

Formula (Percent):

  • Percentage calculations
  • Returns with % symbol
  • Formatted as percent

Formula (Text):

  • String manipulation
  • Text concatenation
  • Conditional text

Formula (Date):

  • Date calculations
  • Date manipulation
  • Date formatting

Formula (Boolean):

  • True/False values
  • Checkbox display
  • Logical operations

Common Formula Patterns#

Basic Math:

Field: Formula (Numeric)Formula: {quantityshippedfulfilled} / {quantity}Summary: AverageLabel: Fill Rate %

Conditional Values:

Field: Formula (Text)Formula: CASE WHEN {amount} > 10000 THEN 'Large'              WHEN {amount} > 5000 THEN 'Medium'              ELSE 'Small' ENDSummary: GroupLabel: Order Size

Date Calculations:

Field: Formula (Numeric)Formula: {today} - {trandate}Summary: GroupLabel: Days Old

Text Concatenation:

Field: Formula (Text)Formula: {entity.firstname} || ' ' || {entity.lastname}Summary: GroupLabel: Full Name

Percentage:

Field: Formula (Percent)Formula: ({amount} - {custbody_cost}) / {amount} * 100Summary: AverageLabel: Margin %

Formula Functions#

Mathematical:

  • ROUND(value, decimals) - Round number
  • ABS(value) - Absolute value
  • CEIL(value) - Round up
  • FLOOR(value) - Round down
  • MOD(dividend, divisor) - Modulo

String:

  • SUBSTR(string, start, length) - Substring
  • LENGTH(string) - String length
  • UPPER(string) - Uppercase
  • LOWER(string) - Lowercase
  • TRIM(string) - Remove spaces
  • || - Concatenate

Date:

  • TO_DATE(string, format) - Convert to date
  • TO_CHAR(date, format) - Format date
  • ADD_MONTHS(date, number) - Add months
  • MONTHS_BETWEEN(date1, date2) - Month difference
  • LAST_DAY(date) - Last day of month

Conditional:

  • CASE WHEN ... THEN ... ELSE ... END
  • NVL(value, default) - Null value replacement
  • DECODE(field, val1, result1, val2, result2, default)

Aggregate:

  • SUM(field) - Total
  • AVG(field) - Average
  • COUNT(field) - Count
  • MAX(field) - Maximum
  • MIN(field) - Minimum

Example Formulas#

Days to Ship:

Formula: {shipdate} - {today}Type: NumericSummary: Group
Displays negative for overdue, positive for future

Year-Month:

Formula: TO_CHAR({trandate}, 'YYYY-MM')Type: TextSummary: Group
Groups by year and month

Margin Amount:

Formula: {amount} - NVL({custbody_cost}, 0)Type: CurrencySummary: Sum
Calculates profit, handles null costs

Status Indicator:

Formula: CASE           WHEN {status} = 'Open' THEN '🟢 Open'          WHEN {status} = 'Pending Approval' THEN '🟡 Pending'          WHEN {status} = 'Closed' THEN '⚫ Closed'          ELSE {status}         ENDType: TextSummary: Group
Adds visual indicators

Fill Rate:

Formula: CASE           WHEN {quantity} > 0           THEN ROUND(({quantityshippedfulfilled} / {quantity}) * 100, 1)          ELSE 0          ENDType: NumericSummary: Average
Calculates percentage filled, avoids divide by zero

Sorting Results#

Sort Configuration#

Primary Sort:

  • First level of sorting
  • Most important organization

Secondary Sort:

  • Breaks ties from primary
  • Additional organization

Tertiary Sort:

  • Further refinement
  • Up to 3-4 sort levels

Sort Direction#

Ascending:

  • A to Z
  • 0 to 9
  • Oldest to newest
  • Lowest to highest

Descending:

  • Z to A
  • 9 to 0
  • Newest to oldest
  • Highest to lowest

Common Sort Patterns#

By Date (Newest First):

Primary Sort: Date (Descending)

By Amount (Highest First):

Primary Sort: Amount (Descending)

By Customer, Then Date:

Primary Sort: Customer Name (Ascending)Secondary Sort: Date (Descending)

By Status, Priority, Date:

Primary Sort: Status (Custom Order)Secondary Sort: Priority (High to Low)Tertiary Sort: Due Date (Ascending)

Column Organization#

Column Order#

Arrange columns logically:

Transaction Searches:

  1. Document Number
  2. Date
  3. Customer/Vendor
  4. Amount
  5. Status
  6. Additional details

Customer Lists:

  1. Customer Name
  2. Contact Info (Email, Phone)
  3. Sales Rep
  4. Classification
  5. Financial summary
  6. Last activity date

Item Lists:

  1. Item Name/Number
  2. Description
  3. Quantity Available
  4. Price
  5. Vendor
  6. Category

Grouping Columns#

For summary searches, group fields control subtotals:

Sales by Rep by Customer:

Results:- Sales Rep (Group) ← First level- Customer (Group) ← Second level- Amount (Sum) ← Calculated- Order Count (Count) ← Calculated
Output shows:  Sales Rep 1    Customer A: $10,000 (5 orders)    Customer B: $15,000 (8 orders)  Sales Rep 1 Subtotal: $25,000 (13 orders)    Sales Rep 2    Customer C: $20,000 (10 orders)  Sales Rep 2 Subtotal: $20,000 (10 orders)  Grand Total: $45,000 (23 orders)

Formatting Results#

Column Width#

Set appropriate widths:

  • IDs: Narrow (60-80px)
  • Names: Medium (150-200px)
  • Descriptions: Wide (250-300px)
  • Amounts: Medium (100-120px)
  • Dates: Medium (100px)

Number Formatting#

Currency:

  • Automatically formats with symbol
  • Respects locale settings
  • Shows 2 decimal places

Percentages:

  • Shows % symbol
  • Formatted appropriately
  • Can set decimal places

Thousands Separators:

  • Automatically added for readability
  • 1,000,000 vs 1000000

Date Formatting#

Default Format:

  • Follows user preferences
  • MM/DD/YYYY or DD/MM/YYYY

Custom Format (Using Formula):

TO_CHAR({trandate}, 'Mon DD, YYYY')Result: Jan 15, 2025
TO_CHAR({trandate}, 'YYYY-MM')Result: 2025-01
TO_CHAR({trandate}, 'Day, Month DD, YYYY')Result: Monday, January 15, 2025

Example Result Configurations#

Sales Order Report#

Columns:1. Document Number (Group)2. Date (Group)3. Customer (Group)4. Sales Rep (Group)5. Ship Date (Group)6. Formula: Days Until Ship ({shipdate} - {today})7. Amount (Sum)8. Status (Group)9. Memo (Group)
Sort:- Primary: Ship Date (Ascending)- Secondary: Customer (Ascending)
Summary: Group by all except Amount (Sum)

Customer Sales Analysis#

Columns:1. Customer Name (Group)2. Sales Rep (Group)3. Formula (Currency): Current Year Sales   SUM(CASE WHEN TO_CHAR({transaction.trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY')        AND {transaction.type} = 'Invoice'        THEN {transaction.amount} ELSE 0 END)4. Formula (Currency): Prior Year Sales   SUM(CASE WHEN TO_CHAR({transaction.trandate}, 'YYYY') = TO_CHAR(ADD_MONTHS({today}, -12), 'YYYY')        AND {transaction.type} = 'Invoice'        THEN {transaction.amount} ELSE 0 END)5. Formula (Percent): YoY Growth   CASE WHEN [Prior Year] > 0        THEN (([Current Year] - [Prior Year]) / [Prior Year]) * 100        ELSE 0 END6. Formula (Numeric): Order Count   COUNT(CASE WHEN {transaction.type} = 'Invoice' THEN 1 END)7. Formula (Currency): Average Order Value   [Current Year Sales] / [Order Count]8. Last Order Date (Maximum)
Sort:- Primary: Current Year Sales (Descending)
Summary: Group by Customer, Sales Rep

Inventory Status Report#

Columns:1. Item Name (Group)2. SKU/Part Number (Group)3. Category (Group)4. Quantity Available (Sum)5. Reorder Point (Group)6. Formula (Numeric): Days of Supply   CASE WHEN {custitem_avg_daily_usage} > 0        THEN {quantityavailable} / {custitem_avg_daily_usage}        ELSE 999 END7. Quantity On Order (Sum)8. Preferred Vendor (Group)9. Last Purchase Price (Maximum)10. Formula (Text): Stock Status    CASE WHEN {quantityavailable} <= 0 THEN 'Out of Stock'         WHEN {quantityavailable} <= {reorderpoint} THEN 'Reorder Needed'         WHEN {quantityavailable} > {custitem_max_stock} THEN 'Overstock'         ELSE 'Normal' END
Sort:- Primary: Stock Status (Custom: Out of Stock, Reorder Needed, Normal, Overstock)- Secondary: Days of Supply (Ascending)
Summary: Group by Item details

Best Practices#

Column Selection#

Include Only Necessary Columns:

Too Many:- 30+ columns- Hard to read- Slow performance- Difficult to export
Right Amount:- 8-12 core columns- Key information- Easy to scan- Good performance

Logical Order:

Good Flow:1. Identifier (ID, Number)2. Name/Description3. Dates4. Amounts/Quantities5. Status/Category6. Additional details
Easy to read and understand

Formula Best Practices#

Keep Formulas Simple:

Good:  {amount} - {cost}
Avoid:  CASE WHEN {field1} > (SELECT AVG(field2) FROM table WHERE condition1 AND condition2)  THEN complex_calculation ELSE other_complex_calculation END
If too complex, consider:- Breaking into multiple formulas- Using custom fields- Script-based calculations

Handle Null Values:

Always use NVL for fields that might be empty:  NVL({custbody_discount}, 0)  Instead of:  {custbody_discount}  (Might cause errors if null)

Avoid Division by Zero:

Always check denominator:  CASE WHEN {quantity} != 0        THEN {amount} / {quantity}       ELSE 0 END
Instead of:  {amount} / {quantity}  (Will error if quantity is 0)

Performance#

Minimize Formula Columns:

  • Each formula adds processing time
  • Use when necessary
  • Consider pre-calculating in custom fields

Use Appropriate Summary Types:

  • Don't use Group when you need Sum
  • Use Count for counting, not Sum of 1s
  • Choose most direct option

Limit Result Rows:

  • Set maximum rows if needed
  • Use criteria to filter
  • Consider pagination

Common Issues#

Formula Errors#

Syntax Error:

Problem: Missing parentheses, wrong field referenceSolution: Verify formula syntax, check field names

Type Mismatch:

Problem: Comparing text to numberSolution: Use appropriate type conversion

NullAdd 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

Slow Performance#

Too Many Formulas:

Solution: Reduce formula columns, use saved fields

Complex Calculations:

Solution: Simplify formulas or pre-calculate

Too Many Result Rows:

Solution: Add more restrictive criteria