Accounting
ERP
Financial Management
NetSuite Saved Searches for Finance Teams: Building AP, AR and GL Searches Step by Step
Last updated: October 15, 2026 · Independent guide · We do not sell rankings or coverage.
A NetSuite saved search is a stored, reusable query that returns live records, lets you add formulas and summary totals, and can email its results on a schedule. For a finance team, that makes it the fastest way to build the lists NetSuite does not ship: open bills bucketed by due date, customer payments that were never applied, GL detail filtered by department, and a journal entry audit trail. Below are all four, with criteria, columns, summary types, formulas and scheduled email.
How we researched this
We built each step from Oracle’s NetSuite Applications Online Help, checked on October 15, 2026, and give menu paths only where Oracle states them. Where a setup came from a partner or vendor, we say that the company sells services or software. We also read the top-ranking guides for this term, which explain the search screen in general terms but do not walk through finance searches with formulas and tie-out checks.
Before you start
- Access to search. Saved searches are part of core NetSuite, documented by Oracle under SuiteAnalytics > Search. You create them from Reports > Saved Searches > All Saved Searches > New (Oracle, Defining a Saved Search).
- Publish Search permission. You need at least the Create level of Publish Search to use the Public box and the Audience, Roles and Email subtabs, and the Publish Search permission to use Save & Email. Oracle states that administrators also have the Public box and Audience subtab (Oracle).
- Notes Tab permission if you want to read line-level history on journal entries (Oracle, Line-Level Audit Trail).
- A tie-out report for each search. Know which standard report you will compare totals against, such as A/P Aging at Reports > Vendors/Payables > A/P Aging (Oracle, A/P Aging Summary Report).
Step 1: Create the search and set who can see it
Go to Reports > Saved Searches > All Saved Searches > New and choose Transaction as the record type. Give each search a title that says what it returns, such as “AP: Open bills by due date”.
Leave Public unchecked on finance searches. Oracle advises against public searches with sensitive data and recommends the Audience subtab instead, where you choose which roles and people can run and view the search (Oracle).
The Criteria subtab has two sublists. Standard filters each record, and Summary filters grouped totals, which only works after you set a summary type on the Results subtab (Oracle, Advanced Search Criteria Filters).
Step 2: Open AP by due date
NetSuite’s A/P Aging report ages bills by due date or transaction date, depending on Accounting Preferences. A saved search adds your own buckets and a link to every bill. For how to read and act on the aging itself, see our accounts payable aging report guide.
| Subtab | Field | Setting | Why |
|---|---|---|---|
| Criteria (Standard) | Type | is Bill | Vendor bills only |
| Criteria (Standard) | Amount Remaining | is greater than 0.00 | Excludes paid bills |
| Criteria (Standard) | Main Line | is true | One row per bill, not per expense line |
| Results | Name, Document Number, Date, Due Date, Amount Remaining | Sort by Due Date | Payment-run worklist |
Oracle confirms that Main Line set to true returns one row per transaction and skips line detail (Oracle, Main Line in Transaction Search Criteria). Without it, a bill with five expense lines appears six times and totals are overstated.
Next, add the aging buckets as formula columns on the Results subtab. Choose Formula (Currency) in the Field column, enter the expression, and set the summary type to Sum:
- Current: CASE WHEN {today}-NVL({duedate},{trandate}) <= 0 THEN {amountremaining} ELSE 0 END
- 1 to 30 days: CASE WHEN {today}-NVL({duedate},{trandate}) BETWEEN 1 AND 30 THEN {amountremaining} ELSE 0 END
- 31 to 60 and 61 to 90 days: the same pattern with BETWEEN 31 AND 60 and BETWEEN 61 AND 90
- Over 90 days: CASE WHEN {today}-NVL({duedate},{trandate}) > 90 THEN {amountremaining} ELSE 0 END
CASE, NVL and {today} are supported in search formulas (Oracle, SQL Expressions; Oracle, Using a Formula in Search Results). {amountremaining}, {duedate} and {trandate} are the internal IDs Oracle lists for the Amount Remaining, Due Date and Date fields in transaction searches (Oracle, SuiteScript Records Browser: Transaction). NVL treats a bill with no due date as due on its transaction date, so it still lands in a bucket.
Set Name to summary type Group and Amount Remaining to Sum, and remove the other detail columns to get one row per vendor with five buckets. The available summary types are Group, Count, Sum, Minimum, Maximum and Average (Oracle, Summary Type Descriptions). Keep a detail version with a Formula (Numeric) column of {today}-{duedate} labelled “Days past due” for the AP clerk.
Illustration: if the search totals $412,800, the A/P Aging report for the same day and subsidiary should match. If it does not, look for open vendor credits and journal entries posted directly to the AP account, which a Bill-only search leaves out.
Step 3: Unapplied customer payments
Payments received but not applied to invoices distort AR aging. Anchor Group, a NetSuite partner that sells implementation services, publishes this setup (Anchor Group, September 28, 2026):
- Criteria: Type is Payment; Amount (Credit) is not empty; Main Line is false; Amount Remaining is greater than 0.00.
- Results: Date, Status, Name, Document Number, Amount (Credit) and Amount Remaining, each with summary type Group, plus Applied To Link Amount with summary type Sum.
Add a Formula (Numeric) column of {today}-{trandate} with summary type Maximum to show how long each balance has sat. Repeat the structure for credit memos and customer deposits, which also leave open credits. Our AR aging report guide covers how unapplied cash distorts DSO.
Step 4: GL detail by department
Pigment, a planning software vendor, recommends filtering transaction searches on Posting is true so you only pull actuals, filtering by accounting period, and excluding zero amounts (Pigment, August 22, 2025).
- Criteria: Posting is true; Accounting Period is the month you are reviewing; Account is the expense accounts in scope; Department is any of the departments you need; Amount is not equal to 0.
- Results (detail): Accounting Period, Account, Department, Type, Date, Document Number, Name, Memo, Amount (Debit), Amount (Credit), Amount.
- Results (summary version): Department (Group), Account (Group), Amount (Sum).
- Available Filters: Accounting Period and Department, so reviewers can change them without editing the search.
Do not add Main Line here, because you need every posting line. For a year-to-date comparison, save a copy with a date filter covering this and last fiscal year to date, then add Oracle’s documented pattern: a Formula (Numeric) column of DECODE(TO_CHAR({trandate},’YYYY’),TO_CHAR({today},’YYYY’),{amount},0) with summary type Sum, and a second column for the prior year using ADD_MONTHS({today},-12) (Oracle, Comparing Summed Amounts Across Two Fiscal Years). This assumes a calendar fiscal year; Oracle shows the offset for other year-ends. Tie each account total to your income statement before you rely on it.
Step 5: Journal entry audit trail
Oracle states that system notes fields can be used as filters and result columns in transaction saved searches (Oracle, Searching System Notes).
- Criteria: Type is Journal Entry; Posting is true; Accounting Period is the period under review.
- Results: Document Number, Date, Accounting Period, Account, Amount (Debit), Amount (Credit), Memo, then the System Notes fields for the user who made the change, the date of the change and the type of change.
- Exception flag: a Formula (Text) column of CASE WHEN TO_CHAR({datecreated},’DY’) IN (‘SAT’,’SUN’) THEN ‘Weekend entry’ ELSE NULL END, using the TO_CHAR date function Oracle lists as supported. {datecreated} is Oracle’s internal ID for the Date Created field in transaction searches (Oracle, SuiteScript Records Browser).
Oracle notes that the Main Line filter does not work for the Journal Entry type, so this search returns one row per journal line (Oracle). Joining system notes also adds a row per change, so do not sum amounts here. Line System Notes fields show updates to existing lines, but not their creation or deletion (Oracle). For a quick look without building a search, use Transactions > Management > View Audit Trail.
Step 6: Schedule the results by email
On the Email subtab, check Send Emails According to Schedule. Oracle describes the steps that follow (Oracle, Enabling Saved Search Scheduled Email):
- On the Schedule subtab, choose the interval, start date, start time and an end date or No End Date.
- Add people or groups under Specific Recipients. Every To recipient receives the full results. At least one recipient must not be Bcc (Oracle, Defining Recipients).
- To send each person only their own rows, use Recipients from Results and pick an entity field such as Sales Rep on a customer search.
- On Customize Message, set the subject and choose Send within Message, Send as CSV, Send as Microsoft Excel or Send as Tableau Workbook.
- Uncheck Send if No Results on exception searches so empty results send nothing.
- Click Save & Email to test it.
A workable cadence is open AP every Monday before the payment run, unapplied payments daily, and GL by department and the JE audit trail during the close.
When a saved search beats a report or a SuiteAnalytics workbook
| Need | Best fit | Reason |
|---|---|---|
| Worklist with links to each record (bills to pay, cash to apply) | Saved search | Live records, drill-through, scheduled email and alerts |
| Formatted financial statement, budget vs actual | Report | Accounting layout, period columns and subsidiary consolidation |
| Exploring trends with pivots and charts | Workbook | Datasets, joins, pivots and formula fields on the analytics data source |
SixLakes Consulting, which sells NetSuite consulting, draws the same line (SixLakes, July 12, 2026). Oracle notes that Workbook uses a different analytics data source, so fields and joins may not match an existing saved search one for one (Oracle, Getting Started with SuiteAnalytics Workbook).
Common mistakes and how to fix them
- Totals that are too high. Line-level rows are being summed. Add Main Line is true for header amounts, or use summary types instead of Show Totals on a line search.
- Bills missing from the aging. A blank due date breaks the CASE formula. Wrap it in NVL as shown above.
- Unexpected foreign-currency totals. Oracle says that when a search has more than one date filter, the most recent date drives exchange rates (Oracle). Keep one date filter on multi-currency searches.
- Invalid expression errors. Formulas are limited to 1,000 characters, and you cannot mix aggregate and non-aggregate functions in one formula (Oracle, Using a Formula in Search Criteria; Oracle, SQL Expressions).
- Scheduled email stopped. Oracle says the schedules of an inactive saved search do not run, so no email is sent (Oracle). Check that the search is still active.
Limitations of native saved searches
Saved searches return current balances, so an open-AP search run today cannot show the aging as it stood at last month-end. Export the results at period end if you need a point-in-time record. One scheduled search runs at a time per user, and up to 200 per account, so stagger close-week schedules (Oracle). The navigation menu shows at most 100 saved searches (Oracle). SixLakes also notes that summary searches cannot drive alerts.
When companies add a third-party tool
Teams add tools when they need multi-period history, data from other systems or planning models. Oracle sells NetSuite Analytics Warehouse, a prebuilt analytics product with connectors to non-NetSuite sources (The Register, October 19, 2021), and planning tools such as Pigment read transaction saved searches as a data feed. If you are moving off QuickBooks, build these four searches during the cutover; our QuickBooks to NetSuite migration guide covers data mapping, opening balances and cutover.
Related guides
Frequently asked questions
How do I create a saved search in NetSuite?
Go to Reports > Saved Searches > All Saved Searches > New and pick a record type, such as Transaction. Set filters on the Criteria subtab and columns on the Results subtab, then click Save.
Why does my NetSuite saved search show duplicate rows?
Transaction searches return a header row plus a row for each line by default. Add Main Line is true to get one row per transaction. Journal entries are the exception, because the Main Line filter does not work for that type.
Can I email a saved search automatically?
Yes, if your role has the Publish Search permission. Check Send Emails According to Schedule on the Email subtab, set the interval on the Schedule subtab and add recipients. Results can arrive in the message body or as a CSV, Excel or Tableau file.
Should I use a saved search or a SuiteAnalytics workbook?
Use a saved search when someone has to act on individual records or receive results by email. Use a workbook when you want pivots and charts to explore trends. Formal financial statements belong in NetSuite’s financial reports.
Sources
- Oracle, Defining a Saved Search, no date shown (checked October 15, 2026). docs.oracle.com
- Oracle, Advanced Search Criteria Filters, no date shown. docs.oracle.com
- Oracle, Main Line in Transaction Search Criteria, no date shown. docs.oracle.com
- Oracle, Summary Type Descriptions, no date shown. docs.oracle.com
- Oracle, Using a Formula in Search Results, no date shown. docs.oracle.com
- Oracle, Using a Formula in Search Criteria, no date shown. docs.oracle.com
- Oracle, SQL Expressions, no date shown. docs.oracle.com
- Oracle, Comparing Summed Amounts Across Two Fiscal Years for Transactions, no date shown. docs.oracle.com
- Oracle, SuiteScript Records Browser: Transaction, no date shown. netsuite.com
- Oracle, Searching System Notes, no date shown. docs.oracle.com
- Oracle, Line-Level Audit Trail for Transactions, no date shown. docs.oracle.com
- Oracle, Enabling Saved Search Scheduled Email, no date shown. docs.oracle.com
- Oracle, Defining Recipients for Saved Search Email, no date shown. docs.oracle.com
- Oracle, A/P Aging Summary Report, no date shown. docs.oracle.com
- Oracle, Getting Started with SuiteAnalytics Workbook, no date shown. docs.oracle.com
- Oracle NetSuite, How to Build a NetSuite Saved Search, April 17, 2024 (vendor). netsuite.com
- Anchor Group, View Unapplied Balances from Payments in NetSuite, September 28, 2026 (NetSuite partner that sells services). anchorgroup.tech
- Pigment, NetSuite Integration: Create Transaction Saved Searches, August 22, 2025 (sells planning software). kb.pigment.com
- SixLakes Consulting, NetSuite Reports vs Saved Searches vs SuiteAnalytics Workbooks, July 12, 2026 (sells NetSuite consulting). sixlakesconsulting.com
- The Register, NetSuite Analytics Warehouse, October 19, 2021. theregister.com
Sherman Hsieh
Independent analysis of enterprise software — ERP, CRM and more
