Skip to main content

Saved Search Tips and Tricks

Expert Techniques#

Dynamic Filters#

Current User Filters:

Criteria:  Assigned To: is {current user}  Result: Each user sees only their recordsPerfect for: Task lists, opportunity pipelines

Current Role Filters:

Criteria:  Department: is {current user's department}  Result: Department-specific viewsPerfect for: Departmental reports

Current Date Logic:

Criteria:  Due Date: within Next 7 Days  Result: Always shows next week, automatically updatesPerfect for: Rolling forecasts

Reusable Search Patterns#

Base Template Pattern: Create a comprehensive search, users add filters:

Base Search: All CustomersPublic: YesAvailable Filters:  - Sales Rep  - Customer Category  - State/Province  - Credit Hold Status  - Date Created  Users run and apply filters as needed

Drill-Down Pattern: Start broad, allow narrowing:

Level 1: All Sales (Summary by Rep)Click Rep → Level 2: Rep's CustomersClick Customer → Level 3: Customer's OrdersClick Order → Level 4: Order Details

Formula Library#

Keep common formulas for reuse:

Age in Days:

{today} - {datefield}

Year-Month:

TO_CHAR({datefield}, 'YYYY-MM')

Business Days:

ROUND(({date2} - {date1}) * 5 / 7, 0)

Percent Change:

CASE WHEN {oldvalue} != 0THEN (({newvalue} - {oldvalue}) / {oldvalue}) * 100ELSE 0 END

Safe Division:

CASE WHEN {denominator} != 0THEN {numerator} / {denominator}ELSE 0 END

Fiscal Quarter:

CASE   WHEN TO_CHAR({date}, 'MM') IN ('01','02','03') THEN 'Q1'  WHEN TO_CHAR({date}, 'MM') IN ('04','05','06') THEN 'Q2'  WHEN TO_CHAR({date}, 'MM') IN ('07','08','09') THEN 'Q3'  ELSE 'Q4'END

Performance Optimization#

Query Optimization#

Use Specific Criteria:

Bad:  Date: All time  Type: Any  Status: Any  Good:  Date: This Year  Type: Invoice  Status: any of (Open, Partially Paid)

Field Selection Order: Add most restrictive first:

  1. Type
  2. Status
  3. Date range
  4. Subsidiary (if multi-subsidiary)
  5. Department/Location
  6. Other filters

Index Usage: Favor indexed fields in criteria:

  • Internal ID
  • Type
  • Status
  • Date
  • Entity ID
  • Document Number

Formula Optimization#

Pre-calculate When Possible:

Instead of formula in search:  Create custom field  Use workflow/script to calculate  Reference in search  Faster: Field lookupSlower: Formula calculation every time

Simplify Complex Formulas:

Complex:  CASE WHEN condition1 THEN    (SELECT complex_calc FROM table WHERE multiple_conditions)  ELSE    (SELECT other_calc FROM table WHERE other_conditions)  END  Better:  Break into custom fields  Or use simpler logic

Avoid Subqueries:

Slow:  WHERE {amount} > (SELECT AVG({amount}) FROM transaction)  Faster:  Calculate average separately  Use as fixed value  Or use summary search

Result Limitations#

Set Row Limits:

For dashboards: 10-20 rowsFor analysis: 1000-5000 rowsFor exports: Consider scheduled search with email

Use Available Filters: Let users narrow results instead of showing everything

Pagination: For large datasets, use pagination instead of loading all

Creative Solutions#

Pseudo-Columns#

Running Total:

Can't do true running total, but can show:- Period total- Cumulative through grouping- Use pivot for time-based accumulation

Rank/Priority:

Formula (Numeric):CASE   WHEN {amount} >= 100000 THEN 1  WHEN {amount} >= 50000 THEN 2  WHEN {amount} >= 10000 THEN 3  ELSE 4END
Sort by this formula

Conditional Highlighting Tricks#

Traffic Light System:

Highlighting 1: Green  Condition: Formula {score} >= 80  Style: Green background  Highlighting 2: Yellow  Condition: Formula {score} >= 60 AND {score} < 80  Style: Yellow background  Highlighting 3: Red  Condition: Formula {score} < 60  Style: Red background

Age-Based Colors:

Highlighting: Overdue (Red)  Condition: {duedate} < {today} AND {status} = 'Open'  Highlighting: Due Soon (Orange)  Condition: {duedate} <= {today} + 3 AND {status} = 'Open'  Highlighting: On Track (Green)  Condition: {duedate} > {today} + 3 AND {status} = 'Open'

Priority Indicators: Use Unicode symbols in formulas:

Formula (Text):CASE   WHEN {priority} = 'Highest' THEN '🔴 ' || {title}  WHEN {priority} = 'High' THEN '🟠 ' || {title}  WHEN {priority} = 'Medium' THEN '🟡 ' || {title}  ELSE '🟢 ' || {title}END

Multi-Currency Handling#

Convert to Base Currency:

Formula (Currency):{foreignamount} * {exchangerate}

Show Both Currencies:

Column 1: Amount (Foreign)Column 2: CurrencyColumn 3: Amount (Base Currency via formula)

Currency Grouping:

Group by CurrencyShow subtotals per currencyGrand total in base currency

Time Period Comparisons#

This vs Last Period:

Column 1: Current PeriodFormula: SUM(CASE WHEN {date} >= {period_start} THEN {amount} ELSE 0 END)
Column 2: Prior Period  Formula: SUM(CASE WHEN {date} >= {prior_start} AND {date} < {period_start}          THEN {amount} ELSE 0 END)
Column 3: ChangeFormula: [Current] - [Prior]
Column 4: Change %Formula: CASE WHEN [Prior] != 0 THEN (([Current] - [Prior]) / [Prior]) * 100          ELSE 0 END

Troubleshooting Guide#

Common Problems and Solutions#

Problem: Search Times Out Solutions:

  1. Add more restrictive criteria
  2. Reduce date range
  3. Limit formula columns
  4. Set row limit
  5. Remove unused joins

Problem: Wrong Totals Check:

  1. Main Line filter
  2. Summary type (Sum vs Group)
  3. Formula logic
  4. Duplicate records from joins

Problem: Missing Records Check:

  1. Criteria too restrictive
  2. Status filters excluding records
  3. Date range
  4. Inactive filter
  5. Permissions

Problem: Duplicate Records Causes:

  1. Missing Main Line filter
  2. Joined search creating duplicates
  3. Multiple matching criteria

Solutions:

  1. Add Main Line = Yes
  2. Use DISTINCT in formulas
  3. Review join conditions
  4. Check for multiple related records

Problem: Formula Errors Debug:

  1. Test formula separately
  2. Check field references
  3. Verify syntax
  4. Handle null values
  5. Check data types

Testing Strategies#

Incremental Testing:

1. Create basic search (type + status only)2. Verify correct records3. Add one criterion4. Test again5. Add one column6. Test again7. Repeat

Edge Case Testing:

Test with:- Zero values- Null/empty fields- Maximum values- Minimum values- Special characters- Very old dates- Future dates

Performance Testing:

1. Test with small dataset2. Gradually increase scope3. Monitor execution time4. Identify bottlenecks5. Optimize as needed

Real-World Examples#

Sales Analysis Dashboard Searches#

Search 1: Daily Sales Summary

Type: TransactionCriteria:  - Type: Invoice  - Date: Today  - Main Line: Yes  - Status: not Cancelled  Results:  - Count of Invoices  - Total Amount (Sum)  - Average Order Value  - Top Sales Rep  - Top Customer
Used in: Dashboard KPI portlet

Search 2: Sales Pipeline

Type: Transaction  Criteria:  - Type: Opportunity  - Probability: less than 100  - Close Date: This Quarter  Results:  - Sales Rep (Group)  - Stage (Group)  - Count  - Total Value (Sum)  - Weighted Value (Formula: Value * Probability)  Used in: Sales manager dashboard

Search 3: Customer At-Risk Alert

Type: CustomerCriteria:  - Status: Active  - Last Order Date: more than 90 days ago  - Prior Year Sales: greater than 10000  Results:  - Customer Name  - Last Order Date  - Days Since Order  - Prior Year Sales  - Current Year Sales  - Assigned Sales Rep  Used in: Weekly email alert

Operations Searches#

Search 1: Fulfillment Queue

Type: TransactionCriteria:  - Type: Sales Order  - Status: Pending Fulfillment  - Ship Date: on or before Next 7 Days  Results:  - Order Number  - Customer  - Ship Date  - Days Until Ship (Formula)  - Items Count  - Order Amount  - Location  Sort: Ship Date (Ascending)Highlighting: Overdue in red
Used in: Daily operations list

Search 2: Inventory Reorder Report

Type: ItemCriteria:  - Type: Inventory Item  - Inactive: No  - Formula: {quantityavailable} <= {reorderpoint}  - Quantity On Order: is 0  Results:  - Item Name  - SKU  - Quantity Available  - Reorder Point  - Suggested Order Qty (Formula)  - Preferred Vendor  - Last Purchase Price  - Days Out of Stock (Formula)  Sort: Days Out of Stock (Descending)
Used in: Weekly purchasing review

Financial Searches#

Search 1: Aged Receivables

Type: TransactionCriteria:  - Type: Invoice  - Status: Open  - Main Line: Yes  Results:  - Customer (Group)  - Invoice Number  - Date  - Due Date  - Days Overdue (Formula)  - Amount Remaining (Sum)  - Age Bucket (Formula)    CASE       WHEN days_overdue <= 0 THEN 'Current'      WHEN days_overdue <= 30 THEN '1-30 Days'      WHEN days_overdue <= 60 THEN '31-60 Days'      WHEN days_overdue <= 90 THEN '61-90 Days'      ELSE '90+ Days'    END  Sort: Days Overdue (Descending)Summary: Group by Customer with subtotals
Used in: Monthly collections review

Pro Tips#

Keyboard Shortcuts#

When Editing Searches:

  • Ctrl+S: Save search
  • Tab: Move between fields
  • Enter: Submit/save dialog

When Running Searches:

  • Ctrl+F: Quick filter
  • Export buttons: Quick access to Excel/CSV

Search Organization#

Naming Strategy:

Prefix by function:  RPT_ for reports  LIST_ for list views    DASH_ for dashboard  ALERT_ for notifications  EXPORT_ for data exports  Example:  RPT_Sales_Monthly_Summary  LIST_Active_Customers  DASH_Open_Orders  ALERT_Inventory_Low_Stock

Folder Organization:

Create search folders:  /Sales Reports  /Financial Reports  /Operations  /Inventory  /Customer Service  /Executive Dashboard

Documentation Best Practices#

In Search Description:

Purpose: Monthly sales analysis for management reviewAudience: Sales managers, executivesSchedule: Runs 1st of month, emails to management@company.comData: Current and prior year invoice data with YoY comparisonOwner: Sales Operations Manager (john.smith@company.com)Created: 2024-06-15Last Modified: 2025-01-20Notes: Includes custom tier classifications based on annual spendDependencies: Requires custentity_customer_tier field

Formula Documentation:

Use custom labels that explain:  Instead of: "Formula 1"  Use: "YoY Growth % (Current vs Prior Year)"  Add comments in complex formulas:  /* Calculate prior year sales */  SUM(CASE WHEN TO_CHAR({date}, 'YYYY') = '2024'       THEN {amount} ELSE 0 END)

Collaboration Tips#

Sharing Searches:

  1. Make public for team use
  2. Set appropriate audience
  3. Document purpose clearly
  4. Provide training if complex
  5. Gather feedback

Version Control:

When updating search:1. Document what changed2. Test in sandbox first3. Notify users of changes4. Keep backup of original5. Update documentation

Advanced Filtering Techniques#

Cascading Filters:

Filter 1: Sales Rep (affects available customers)Filter 2: Customer (shows only customers for selected rep)Filter 3: Date Range (shows only dates with data)
Implement via:- Multiple searches- Dynamic forms- Scripted portlets

Smart Defaults:

Available Filter: Date RangeDefault: This Month
Available Filter: Sales Rep  Default: {current user} (if they're a sales rep)
Result: Users see relevant data immediately

Integration Tips#

Using Searches in Scripts#

Load Search Results:

var search = nlapiLoadSearch('customer', 'customsearch_id');var results = search.runSearch();
results.forEachResult(function(result) {    var customerId = result.getId();    var customerName = result.getValue('companyname');    // Process each result    return true;});

Dynamic Search Creation:

var search = nlapiCreateSearch('transaction',    [        ['type', 'is', 'SalesOrder'],        'AND',        ['status', 'anyof', 'SalesOrd:A', 'SalesOrd:B']    ],    [        new nlobjSearchColumn('tranid'),        new nlobjSearchColumn('entity'),        new nlobjSearchColumn('amount')    ]);

API Access#

SuiteTalk (SOAP/REST):

Access saved searches via web servicesUse search IDRetrieve results programmaticallyPerfect for integrations

REST API Example:

GET /record/v1/savedSearch/{searchId}/resultsAuthorization: NLAuth nlauth_account={account}, ...
Returns: JSON with search results

Excel Integration#

Exporting Best Practices:

1. Limit columns to what's needed2. Use clear column labels3. Format dates consistently4. Remove internal IDs if not needed5. Consider pivot-ready format

Dynamic Excel Updates:

1. Export search to Excel2. Set up pivot tables3. Save template4. Refresh data regularly5. Charts update automatically

Search Maintenance#

Regular Audits#

Quarterly Review Checklist:

â–¡ Review all public searchesâ–¡ Check for unused searches (no run history)â–¡ Update outdated formulasâ–¡ Verify permissions still appropriateâ–¡ Test performanceâ–¡ Update documentationâ–¡ Consolidate duplicatesâ–¡ Archive old searches

Performance Monitoring:

Track:- Execution time- Usage frequency- User feedback- Error rates
Optimize:- Slow searches- Frequently used searches- Complex formulas- Large result sets

Deprecation Strategy#

When Retiring Searches:

1. Identify unused/obsolete searches2. Notify users of deprecation3. Provide alternatives4. Set inactive (don't delete immediately)5. Monitor for issues6. Delete after grace period

Common Use Case Solutions#

Scenario: Month-End Reporting#

Challenge: Multiple reports needed monthly

Solution: Report Suite

Search 1: Monthly Sales Summary- Scheduled 1st of month- Email to management- Excel format with charts
Search 2: Outstanding AR- Same schedule- Email to accounting- PDF format
Search 3: Inventory Valuation- Same schedule  - Email to operations- CSV for import to other system
All use same date logic: Prior Month

Scenario: Real-Time Dashboard#

Challenge: Multiple metrics, live updates

Solution: Dashboard Portlets

Portlet 1: Today's Sales (KPI)- Single number- Refreshes every 5 minutes- Color coded vs target
Portlet 2: Open Orders (List)- Top 10 by amount- Click to view detail- Real-time
Portlet 3: Low Stock (Alert)- Items below reorder- Only shows if issues- Actionable
Portlet 4: Sales Trend (Chart)- Last 30 days- Line chart- Multiple series

Scenario: Exception Reporting#

Challenge: Only notify when problems

Solution: Alert Searches

Search: Overdue ShipmentsCriteria:- Status: Not shipped- Ship date: Before todaySchedule: Daily 8 AMCondition: Only if resultsEmail: Operations team
Result: Only get email when there are issues

Scenario: Customer Segmentation#

Challenge: Classify customers dynamically

Solution: Segmentation Search

Search: Customer TiersResults:- Customer Name- Formula (Text): Tier  CASE    WHEN {salesreadonly} > 500000 THEN 'Platinum'    WHEN {salesreadonly} > 100000 THEN 'Gold'    WHEN {salesreadonly} > 25000 THEN 'Silver'    ELSE 'Bronze'  END- Total Sales- Order Count- Average Order Value- Last Order Date
Used for:- Targeted marketing- Service levels- Pricing strategies- Account assignments

Advanced Tricks#

Hidden Gems#

Saved Search URLs:

Direct URL to search:https://system.netsuite.com/app/common/search/searchresults.nl?searchid=123
With filters applied:...&Customer_CUSTRECORD123=456&Date_FROM=01/01/2025&Date_TO=12/31/2025
Bookmark for quick accessShare with team

Search Result Columns as Filters:

Click any column header in resultsAdditional filters appearQuick way to drill down

Inline Editing:

Some searches support inline editingCheck "Allow Inline Editing" in searchEdit directly in resultsQuick bulk updates

Mass Update from Search:

Run searchSelect records (checkboxes)Actions > Mass UpdateApply changes to selected

Scripting Enhancements#

Add Custom Buttons:

// Client script on search resultfunction addCustomButton() {    var form = nlapiGetForm();    form.addButton('custpage_mybutton', 'Process Selected',                    'processRecords()');}

Custom Actions:

function processRecords() {    var records = nlapiGetSearchResults();    // Process each selected record    // Custom logic here}

Power User Features#

Bulk Operations:

1. Run search2. Select multiple records3. Actions menu:   - Print   - Email   - Mass Update   - Mass Delete   - Export

Search-Based Workflows:

Trigger workflow from search:1. Search finds qualifying records2. User selects records3. Workflow action applied4. Bulk processing

Saved Search as Data Source:

Use searches to populate:- Custom forms- Report builder- Dashboard KPIs- Email templates- SuiteScripts

Future-Proofing Searches#

Design for Change#

Flexible Criteria:

Use available filters instead of hard-coded:Bad: Date is "01/01/2025"Good: Date filter with default "This Month"

Generic Labels:

Instead of: "2025 Sales"Use: "Current Year Sales"Remains accurate next year

Parameterized Logic:

Store thresholds in custom recordsReference in formulasEasy to update without editing search

Scalability#

Design for Growth:

Consider:- Increasing data volume- New subsidiaries- Additional currencies- More users- Performance impact

Modular Design:

Break complex searches into:- Base search (core criteria)- Specialized variants- Focused dashboardsEasier to maintain

Troubleshooting Checklist#

Search Not Returning Expected Results:

â–¡ Check criteria one by oneâ–¡ Verify date rangesâ–¡ Confirm status filtersâ–¡ Check Main Line settingâ–¡ Review formula logicâ–¡ Test with known dataâ–¡ Check permissionsâ–¡ Verify record type

Performance Issues:

â–¡ Add restrictive criteriaâ–¡ Reduce formula columnsâ–¡ Limit result rowsâ–¡ Remove unused joinsâ–¡ Check for infinite loopsâ–¡ Simplify complex formulasâ–¡ Use indexed fieldsâ–¡ Set appropriate row limits

Formula Errors:

â–¡ Check syntaxâ–¡ Verify field namesâ–¡ Handle null valuesâ–¡ Check data typesâ–¡ Test incrementallyâ–¡ Use NVL for nullsâ–¡ Avoid division by zeroâ–¡ Check parentheses

Resources#

Learning More#

NetSuite Help:

  • Saved Search documentation
  • Formula reference
  • Field reference
  • Examples and tutorials

Community:

  • NetSuite User Group
  • Stack Exchange
  • Partner forums
  • Training videos

Testing:

  • Sandbox account
  • Sample data
  • Test scenarios
  • Performance testing

Getting Help#

When Stuck:

  1. Check documentation
  2. Search SuiteAnswers
  3. Review similar searches
  4. Test incrementally
  5. Ask community
  6. Contact support
  7. Consult partner

Summary#

Saved searches are incredibly powerful when you master:

  • Efficient criteria design
  • Formula techniques
  • Performance optimization
  • Advanced features
  • Real-world patterns

Practice regularly, document well, and keep learning!