Skip to main content

Advanced Saved Search Features

Joined Searches#

Understanding Joins#

Joins allow you to include fields from related records in your search, similar to SQL JOIN operations.

Join Types#

Direct Relationship Joins:

  • Customer to Transaction
  • Transaction to Items
  • Employee to Transactions
  • Vendor to Items

Multi-Level Joins:

  • Transaction to Customer to Sales Rep
  • Item to Vendor to Vendor Category
  • Order to Customer to Customer Category

Creating Joined Searches#

Example: Customer with Transaction Data

Base Search Type: Customer

Add Transaction Criteria:

Criteria Tab:  Add Filter:    Field: Transaction: Type    Operator: is    Value: Invoice      Add Filter:    Field: Transaction: Date    Operator: within    Value: This Year

Add Transaction Results:

Results Tab:  Column 1: Name (Group) - Customer name  Column 2: Transaction: Document Number (Group)  Column 3: Transaction: Date (Group)  Column 4: Transaction: Amount (Sum)  Column 5: Transaction: Status (Group)

Result: Each customer shown with their invoice details

Common Join Patterns#

Customer with Sales Data:

Base: CustomerJoin: Transaction (Invoice)Purpose: Customer sales analysis
Results:- Customer Name- Total Sales (Transaction: Amount Sum)- Order Count (Transaction: Internal ID Count)- Average Order (Formula)- Last Order Date (Transaction: Date Maximum)

Items with Purchase History:

Base: ItemJoin: Transaction (Purchase Order)Purpose: Vendor purchasing analysis
Results:- Item Name- Quantity Purchased (Transaction: Quantity Sum)- Average Cost (Transaction: Rate Average)- Last Purchase Date (Transaction: Date Maximum)- Preferred Vendor

Sales Orders with Customer Details:

Base: Transaction (Sales Order)Join: Customer fields automatically availablePurpose: Order analysis with customer context
Results:- Order Number- Customer Name- Customer: Credit Limit- Customer: Balance- Customer: Category- Order Amount- Available Credit (Formula)

Multi-Level Joins#

Transaction → Customer → Sales Rep:

Base: TransactionCriteria:  Type: Invoice  Date: This Year  Results:  - Document Number  - Customer (Name)  - Customer: Sales Rep (Name)  - Customer: Sales Rep: Supervisor  - Amount

Item → Vendor → Vendor Category:

Base: ItemCriteria:  Type: Inventory Item  Inactive: No  Results:  - Item Name  - Preferred Vendor (Name)  - Vendor: Category  - Vendor: Terms  - Last Purchase Price

Transaction Line Searches#

When to Use Line Searches#

Use when you need:

  • Individual line item details
  • Item-specific analysis
  • Line-level custom fields
  • Quantity and pricing breakdown

Transaction Line Example#

Items Sold Analysis:

Search Type: Transaction (Lines)
Criteria:  - Type: Invoice  - Date: This Month  - Main Line: No (critical - shows item lines)  - Item Type: any of (Inventory Item, Non-Inventory Item)  Results:  - Item: Name (Group)  - Item: Display Name (Group)  - Quantity (Sum)  - Amount (Sum)  - Transaction Count (Formula Count)  - Average Price (Formula: Amount / Quantity)  - Gross Profit (Formula: Amount - Cost)
Sort:  - Amount (Descending)

Line-Level Custom Fields:

Search Type: Transaction (Lines)
Criteria:  - Type: Sales Order  - Status: Pending Fulfillment  - Main Line: No  Results:  - Document Number (Group)  - Item Name (Group)  - Quantity (Sum)  - Line: Custom Field (Project Code)  - Line: Custom Field (Ship To Location)  - Line: Custom Field (Special Instructions)

Main Line vs. Line Level#

Main Line = Yes:

  • One row per transaction
  • Transaction-level data only
  • Use for transaction totals
  • Header-level custom fields

Main Line = No:

  • Multiple rows per transaction
  • Item-level details
  • Quantity and line amounts
  • Line-level custom fields

Both (No Main Line Filter):

  • Shows both header and detail rows
  • Rarely useful
  • Usually causes confusion
  • Avoid unless specifically needed

Advanced Formulas#

Complex Conditional Logic#

Multi-Tier Classification:

Field: Formula (Text)Formula:CASE   WHEN {amount} >= 100000 THEN 'Tier 1 - Enterprise'  WHEN {amount} >= 50000 THEN 'Tier 2 - Corporate'  WHEN {amount} >= 10000 THEN 'Tier 3 - Business'  WHEN {amount} >= 1000 THEN 'Tier 4 - Standard'  ELSE 'Tier 5 - Basic'END

Status with Age:

Field: Formula (Text)Formula:CASE   WHEN {status} = 'Open' AND ({today} - {trandate}) > 60 THEN 'Open - Critical'  WHEN {status} = 'Open' AND ({today} - {trandate}) > 30 THEN 'Open - Warning'  WHEN {status} = 'Open' THEN 'Open - Normal'  WHEN {status} = 'Closed' THEN 'Closed'  ELSE {status}END

Aggregation within Formulas#

Year-to-Date Sales:

Field: Formula (Currency)Formula:SUM(CASE   WHEN TO_CHAR({transaction.trandate}, 'YYYY') = TO_CHAR({today}, 'YYYY')  AND {transaction.type} = 'Invoice'  AND {transaction.status} != 'Cancelled'  THEN {transaction.amount}  ELSE 0 END)

Last 12 Months Rolling:

Field: Formula (Currency)Formula:SUM(CASE  WHEN {transaction.trandate} >= ADD_MONTHS({today}, -12)  AND {transaction.type} = 'Invoice'  THEN {transaction.amount}  ELSE 0END)

Count Active Customers:

Field: Formula (Numeric)Formula:COUNT(DISTINCT CASE  WHEN {transaction.trandate} >= ADD_MONTHS({today}, -12)  AND {transaction.type} IN ('Invoice', 'Cash Sale')  THEN {transaction.entity}END)

Date Manipulation#

Week Number:

Field: Formula (Numeric)Formula: TO_NUMBER(TO_CHAR({trandate}, 'IW'))Label: Week #

Quarter with Year:

Field: Formula (Text)Formula: TO_CHAR({trandate}, 'YYYY') || '-Q' || TO_CHAR({trandate}, 'Q')Label: Quarter

Business Days Between:

Field: Formula (Numeric)Formula: ROUND(({duedate} - {trandate}) * 5 / 7, 0)Label: Business Days

Age in Months:

Field: Formula (Numeric)Formula: MONTHS_BETWEEN({today}, {datecreated})Label: Age (Months)

Text Manipulation#

First and Last Initial:

Field: Formula (Text)Formula: SUBSTR({firstname}, 1, 1) || '. ' || SUBSTR({lastname}, 1, 1) || '.'Label: Initials

Extract Domain from Email:

Field: Formula (Text)Formula: SUBSTR({email}, INSTR({email}, '@') + 1)Label: Email Domain

Format Phone Number:

Field: Formula (Text)Formula:   '(' || SUBSTR({phone}, 1, 3) || ') ' ||   SUBSTR({phone}, 4, 3) || '-' ||   SUBSTR({phone}, 7, 4)Label: Phone (Formatted)

Statistical Formulas#

Standard Deviation (Approximation):

Field: Formula (Numeric)Formula: SQRT(  AVG({amount} * {amount}) -   (AVG({amount}) * AVG({amount})))

Weighted Average:

Field: Formula (Currency)Formula: SUM({amount} * {weight}) / SUM({weight})

Percentile Ranking:

Field: Formula (Numeric)Formula:CASE  WHEN {amount} >= (SELECT AVG({amount}) * 1.5 FROM transaction) THEN 90  WHEN {amount} >= (SELECT AVG({amount}) * 1.2 FROM transaction) THEN 75  WHEN {amount} >= (SELECT AVG({amount}) FROM transaction) THEN 50  WHEN {amount} >= (SELECT AVG({amount}) * 0.8 FROM transaction) THEN 25  ELSE 10END

Search Expressions#

Creating Expressions#

Expressions allow complex OR logic and nested conditions.

Access: Criteria tab → Use Expressions checkbox

Expression Examples#

Multiple OR Conditions:

Expression 1:  ({amount} > 50000)  Expression 2:  ({entity.custentity_tier} = 'Premium' AND {amount} > 10000)  Expression 3:  ({custbody_rush_order} = 'T')
Result: Records matching any expression

Complex Date Logic:

Expression 1: Recent High Value  ({trandate} >= {today} - 30 AND {amount} > 25000)  Expression 2: Older Very High Value  ({trandate} >= {today} - 90 AND {amount} > 100000)
Result: Recent large orders OR older very large orders

Customer Classification:

Expression 1: VIP Customer  ({entity.custentity_tier} = 'VIP')  Expression 2: High Volume  ({entity.salesreadonly} > 500000)  Expression 3: Strategic Account  ({entity.custentity_strategic} = 'T')
Result: Any VIP, high volume, or strategic customer

Summary Searches with Subtotals#

Multi-Level Grouping#

Sales by Year, Quarter, Month:

Search Type: TransactionCriteria:  - Type: Invoice  - Date: Last 2 Years  - Main Line: Yes  Results:  - Formula (Text): Year    TO_CHAR({trandate}, 'YYYY')    Summary: Group      - Formula (Text): Quarter    'Q' || TO_CHAR({trandate}, 'Q')    Summary: Group      - Formula (Text): Month    TO_CHAR({trandate}, 'Mon')    Summary: Group      - Amount (Sum)  - Count (Count)  - Average (Average)
Result shows:  2024    Q1      Jan: $100,000 (50 orders, avg $2,000)      Feb: $120,000 (55 orders, avg $2,182)      Mar: $110,000 (52 orders, avg $2,115)    Q1 Subtotal: $330,000 (157 orders)    Q2      ...  2024 Subtotal: $1,200,000  2025    ...Grand Total: $2,500,000

Sales by Rep, by Customer, by Product:

Results:  - Sales Rep (Group) ← Level 1  - Customer (Group) ← Level 2    - Item (Group) ← Level 3  - Quantity (Sum)  - Amount (Sum)  Shows hierarchical breakdown with subtotals at each level

Pivot Tables#

Creating Pivot Tables#

Available for summary searches with appropriate grouping.

Example: Sales by Product Line by Quarter:

Rows: Product LineColumns: QuarterValues: Sales Amount (Sum)
Result:                Q1 2024    Q2 2024    Q3 2024    Q4 2024    TotalElectronics     $500,000   $550,000   $600,000   $650,000   $2,300,000Software        $200,000   $220,000   $240,000   $260,000   $920,000Services        $150,000   $160,000   $170,000   $180,000   $660,000Total          $850,000   $930,000  $1,010,000 $1,090,000  $3,880,000

Pivot Configuration#

Row Groups:

  • Primary dimension
  • What to analyze by
  • Can have multiple levels

Column Groups:

  • Secondary dimension
  • Typically time periods
  • Or categories

Values:

  • What to measure
  • Sum, Average, Count, etc.

Charts and Graphs#

Chart Types#

Line Chart:

  • Trends over time
  • Multiple series comparison
  • Time-series data

Bar/Column Chart:

  • Category comparison
  • Ranking
  • Part-to-whole

Pie Chart:

  • Composition
  • Percentage breakdown
  • Single-period snapshot

Area Chart:

  • Cumulative trends
  • Volume over time
  • Stacked categories

Creating Charts#

  1. Create summary search
  2. Click "View as Chart"
  3. Select chart type
  4. Configure:
    • X-axis (categories)
    • Y-axis (values)
    • Series (grouping)
    • Colors and labels

Example: Monthly Sales Trend

Search: Invoice sales by monthChart Type: Line ChartX-Axis: MonthY-Axis: Amount (Sum)Series: Sales Rep (for comparison)

Scheduled Searches#

Email Delivery#

Setup:

  1. Save search
  2. Edit search
  3. Go to "Email" subtab
  4. Configure schedule:
    • Frequency (Daily, Weekly, Monthly)
    • Time
    • Day of week/month
    • Recipients
    • Format (PDF, Excel, CSV)
    • Subject and message

Use Cases:

Daily Alerts:

Search: Orders pending fulfillmentSchedule: Daily at 8 AMRecipients: Warehouse teamFormat: Excel

Weekly Reports:

Search: Sales by rep - weekly summarySchedule: Monday morningRecipients: Sales managersFormat: PDF

Monthly Management Reports:

Search: Financial summarySchedule: First of monthRecipients: Management teamFormat: Excel with charts

Conditional Delivery#

Send Only If Results:

  • Only email when search has data
  • Perfect for alerts
  • Avoids empty reports

Example:

Search: Orders overdue for shipmentSchedule: DailyCondition: Only if results foundResult: Only get email when there are overdue orders

Dashboard Integration#

Adding to Dashboards#

Create Portlet:

  1. Customize dashboard
  2. Add content → Saved Search
  3. Select search
  4. Configure display:
    • Chart or list
    • Number of rows
    • Refresh frequency
    • Click-through behavior

Portlet Types:

List Portlet:

  • Shows search results as list
  • Limited rows (typically 10-20)
  • Click rows to view records
  • Real-time data

Chart Portlet:

  • Visual representation
  • Various chart types
  • Summary data
  • Dashboard metrics

KPI Portlet:

  • Single number
  • Key metric
  • Trend indicator
  • Traffic light colors

Dashboard Examples#

Sales Dashboard:

Portlets:1. Today's Sales (KPI - single number)2. Open Opportunities (List - top 10)3. Sales Trend (Chart - last 30 days)4. Pipeline by Stage (Chart - pie)5. Top Customers (List - by revenue)

Operations Dashboard:

Portlets:1. Orders to Fulfill Today (List)2. Overdue Shipments (List - highlighted)3. Inventory Alerts (List - reorder needed)4. Fulfillment Rate (Chart - daily trend)

Search Templates#

Saving as Template#

Save search structure without data for reuse:

  1. Create search
  2. Save as template
  3. Others can use as starting point
  4. Customize for their needs

Template Use Cases#

Monthly Report Template:

Template includes:- Standard column structure- Common criteria- Date filter (user adjustable)- Formatting- Sort order
Users customize:- Date range- Additional filters- Specific departments

Analysis Template:

Template for product analysis:- Basic structure- Key formulas- Standard groupings- Users add specific products/categories

Advanced Search Techniques#

Using SQL-like Features#

Subqueries (in formulas):

Formula:CASE WHEN {amount} > (SELECT AVG({amount}) FROM transaction WHERE {type} = 'Invoice')THEN 'Above Average'ELSE 'Below Average'END

EXISTS Conditions:

Formula (Boolean):EXISTS (  SELECT 1 FROM transaction   WHERE {entity} = {currentrecord.entity}  AND {type} = 'Invoice'  AND {status} = 'Open')

Performance Tuning#

Indexed Fields: Use in criteria for better performance:

  • Internal ID
  • Type
  • Status
  • Date
  • Entity

Avoid in Criteria:

  • Text "contains" searches
  • Complex formulas
  • Non-indexed custom fields

Optimization Tips:

  1. Filter early (restrictive criteria first)
  2. Limit formula columns
  3. Use summary when possible
  4. Set reasonable row limits
  5. Avoid open-ended date ranges

Search Debugging#

Test Incrementally:

  1. Start with basic criteria
  2. Add columns one at a time
  3. Test formulas separately
  4. Verify calculations
  5. Check performance

Common Issues:

No Results:

  • Remove criteria one by one
  • Check for conflicts
  • Verify data exists

Wrong Totals:

  • Check Main Line filter
  • Verify summary types
  • Test formulas independently

Slow Performance:

  • Simplify criteria
  • Reduce formula columns
  • Add row limits
  • Check for cartesian products

Best Practices#

Search Design#

Purpose-Driven:

  • Clear objective
  • Specific audience
  • Defined use case

User-Friendly:

  • Intuitive column headers
  • Logical organization
  • Helpful descriptions
  • Available filters

Performance-Conscious:

  • Efficient criteria
  • Reasonable scope
  • Optimized formulas
  • Appropriate limits

Documentation#

Description Field:

What: Customer sales analysisWho: Sales management teamWhen: Updated nightlyHow: Email delivery Monday morningsNotes: Includes YoY comparison and trend indicatorsOwner: Sales OperationsLast Updated: 2025-01-15

Formula Documentation:

Label: YoY Growth %Formula: (Current Year - Prior Year) / Prior Year * 100Notes: Shows percentage growth, handles zero division