How to Exclude NetSuite Records Based on Line Criteria

One of the most frustrating limitations in NetSuite's standard Saved Search functionality is the inability to filter a header record (like a Sales Order) based on a condition that exists only at the line level.

If you need to find all Sales Orders where "no item is from a specific vendor" or "the total quantity of a specific SKU exceeds 10," a standard Saved Search often fails. This is because NetSuite evaluates the criteria on a per-line basis. If a Sales Order has ten lines and only one line matches your criteria, the search will return that record because at least one row met the filter.

To solve this, you must move beyond basic filtering and use techniques that aggregate data across the entire transaction.

The Problem: Why Standard Filters Fail

When you apply a filter like Item : Vendor = 'ABC' on a Saved Search, NetSuite looks at every line item. If any single line matches that criteria, the entire Sales Order is included in the results.

Because a transaction can have multiple lines, there is no native "Summary Criteria" that allows you to filter the header while still seeing every line item in the search results. This creates a logical paradox for the user: you want to see the details of the order, but only if the entire order meets a specific aggregate condition.

Solution 1: The SuiteQL Approach

For users who require high precision and the ability to perform complex logic (like "Exclude if any line matches X"), SuiteQL is the right tool. Unlike standard Saved Searches, SuiteQL allows for subqueries and joins that can evaluate the state of a transaction as a whole.

By using a WHERE clause with a subquery, you can check for the existence of specific line criteria without those lines being part of the primary filter. This is particularly useful because SuiteQL supports query capabilities that standard search filters cannot replicate, such as complex joins and aggregations.

Example: Excluding Sales Orders with a Specific Item

If you need to find Sales Orders that do not contain any items from a specific category, you can use a query like this:

SELECT 
    T0.TranID, 
    T0.Entity, 
    T0.Amount
FROM 
    _salesorder T0
WHERE 
    T0.MainLine = 'F' 
    AND NOT EXISTS (
        SELECT 1 
        FROM transaction_line_item T1 
        WHERE T1.Transaction = T0.ID 
        AND T1.Item_Category = 'Restricted'
    )

Note: In SuiteQL, you can perform complex joins and subqueries that are impossible in the Saved Search Criteria tab.

Solution 2: The "Flagging" Method (Scheduled Script)

If you require the results to be viewable in a standard Saved Search (perhaps for a dashboard or a list view), the best practice is to "flatten" the logic.

Instead of trying to make the search engine do complex math on every page load, use a Scheduled Script to evaluate the criteria and write a value to a custom field on the header.

The Workflow:

1. Create a custom checkbox or text field on the Sales Order record (e.g., custbody_is_restricted).
2. Create a Scheduled Script that runs periodically (or on a trigger).
3. The script iterates through the transactions, checks the line items, and sets the checkbox.
4. Your Saved Search then simply filters by that checkbox.

Code Example: Flagging Transactions

The following SuiteScript 2.1 snippet demonstrates how to check line items and update a header field. Note that record.submitFields is the preferred method for updating body-level fields without loading the entire record, which saves on governance units.

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/query', 'N/record', 'N/log'], (query, record, log) => {
    const execute = () => {
        // Example: Find all Sales Orders that haven't been flagged yet
        // In a real scenario, use N/query to fetch IDs. 
        // For this example, we assume a list of IDs is retrieved.
        const orderIds = [12345, 67890]; 

        for (let id of orderIds) {
            // Load the record to check line items
            const objRecord = record.load({
                type: record.Type.SALES_ORDER,
                id: id
            });

            // Get the line count for the 'item' sublist
            const lineCount = objRecord.objRecord.getSublist({
                sublistId: 'item'
            }).getRowCount();

            let hasRestrictedItem = false;

            for (let i = 0; i < lineCount; i++) {
                const item = objRecord.objRecord.getSublist({
                    sublistId: 'item',
                    fieldName: 'item'
                }).getText({ line: i });

                // Logic to check if item is restricted
                if (item === 'Restricted_Item_Name') {
                    hasRestrictedItem = true;
                    break;
                }
            }

            if (hasRestrictedItem) {
                // Update the header field using record.submitFields
                record.submitFields({
                    type: record.Type.SALES_ORDER,
                    id: id,
                    values: {
                        'custbody_is_restricted': true
                    }
                });
            }
        }
    };

    return { execute };
});

Solution 3: Summary Saved Search (The "Drill Down" Approach)

If you do not need to see every line item in the final list and only need a list of the Sales Orders themselves, you can use a Summary Saved Search.

1. Go to Reports > Saved Searches > All Saved Searches > New.
2. Set the Criteria: Main Line is False (to look at line items).
3. Add your specific criteria (e.g., Item : Vendor = 'ABC').
4. Go to the Summary tab.
5. Set the Summary Type to Group for the Document Number and Date.
6. In the Criteria tab, you can use a formula to count if any items match:
   • Formula (Numeric): CASE WHEN {item.vendor} = 'ABC' THEN 1 ELSE 0 END
7. Filter the summary results where this formula is greater than 0.

Note: This will only return the summarized header data. You will not see the individual line items in the search results, but you can click into the record to see them.

Comparison Table: Which Method to Choose?

| Method | Complexity | Real-Time Accuracy | User Experience | Best For... | | :--- | :--- | :--- |   |   | | Standard Search | Low | High | Good | Simple filters on single lines. | | SuiteQL | High | High | Excellent | Complex reporting and data exports. | | Scheduled Script | Medium | Delayed | Best | Dashboards and list views that need to be clean. | | Summary Search | Low | High | Limited | Identifying high-level totals/counts. |

Conclusion

Filtering a NetSuite record based on line-level criteria is a common hurdle because the standard Saved Search engine evaluates "OR" logic across lines. If any line matches, the record is included. To bypass this, you must either use SuiteQL for a deeper query or use a Scheduled Script to "tag" the header record based on its line contents.

By moving the logic into a custom field or a SQL subquery, you ensure that your reports remain accurate and your data remains clean.