Exploring the “When Ordered By” Feature in NetSuite Saved Searches

Introduction

The “When Ordered By” feature in NetSuite saved searches is a robust tool for creating advanced summary searches that require conditional data retrieval. It allows you to specify the order in which a field’s value is determined, based on another column or attribute. This is particularly useful in cases where the data depends on the sequence of related values, such as fetching the latest transactions or aligning quantities with specific dates.

This article will guide you through understanding, configuring, and using the “When Ordered By” functionality, along with a practical use case and sample code for implementation.

What is “When Ordered By”?

The “When Ordered By” feature in NetSuite saved searches enables you to control how the value of a column is calculated when the search includes summary functions (e.g., MAX, MIN). It ensures that the correct value is returned by associating it with a specific column, such as a date field, to maintain the desired order of evaluation.

For example, in a purchase history report, if you want to fetch the maximum quantity sold and align it with the corresponding last selling date, “When Ordered By” ensures that the quantity value reflects the same transaction as the maximum date.

Use Case

Scenario: Retrieve the last purchase history of a customer to identify:

1. Last selling date
2. Last selling price
3. Last selling quantity

The goal is to summarize this data for each item the customer has purchased, aligning the quantity and price with the most recent date of purchase.

Steps to Implement “When Ordered By”

1. Create a Summary Search:

• “When Ordered By” is available only in summary searches. Set up your saved search with appropriate grouping and summary functions.

1. Define the Filters:

• Specify the criteria to filter relevant transactions (e.g., Customer Invoices, Cash Sales, etc.).

1. Add Columns with Summary Functions:

• Include columns for item details, transaction date, rate, and quantity.
• Use MAX for the date to fetch the latest transaction.

1. Apply “When Ordered By”:

• Associate the quantity column with the date column using “When Ordered By” to ensure the maximum quantity aligns with the latest transaction date.

Sample Code for Implementation

Here’s a sample SuiteScript snippet that uses “When Ordered By” in a saved search:

javascript

Copy code
let transactionSearchObj = search.create({
    type: 'transaction',
    filters: [
        ["type", "anyof", "CustInvc", "CashSale"], // Transactions of interest
        "AND",
        ["mainline", "is", "F"], // Exclude mainline rows
        "AND",
        ["entity", "anyof", customerId], // Filter by customer ID
        "AND",
        ["cogs", "is", "F"] // Exclude COGS transactions
    ],
    columns: [
        search.createColumn({ name: 'internalid', join: 'item', summary: 'GROUP' }),
        search.createColumn({ name: 'custitem_2srx_item_number', join: 'item', summary: 'GROUP' }),
        search.createColumn({ name: 'salesdescription', join: 'item', summary: 'GROUP', sort: search.Sort.ASC }),
        search.createColumn({ name: 'trandate', summary: 'MAX' }), // Maximum transaction date
        search.createColumn({ name: 'rate', summary: 'GROUP' }), // Selling price
        search.createColumn({ 
            name: 'quantity', 
            summary: 'MAX' 
        }).setWhenOrderedBy({ 
            name: 'trandate', 
            join: 'transaction' // Align max quantity with the latest date
        })
    ]
});

Explanation of the Code

1. Filters:

• The search filters transactions of type Customer Invoice or Cash Sale, excluding mainline rows and filtering for a specific customer.

1. Columns:

• Grouping is applied on item fields to summarize the data.
• trandate column fetches the latest transaction date using the MAX function.
• quantity column uses “When Ordered By” to align with the latest transaction date.

1. setWhenOrderedBy:

• Ensures the MAX(quantity) value corresponds to the same transaction as the MAX(trandate). Without this, the quantity might reflect a different transaction.

Benefits of Using “When Ordered By”

1. Data Accuracy:
2. Ensures that values like quantity and price are correctly aligned with associated attributes, such as transaction dates.
3. Efficiency:
4. Reduces manual effort and potential errors when analyzing complex data.
5. Flexibility:
6. Can be applied to various fields and use cases, making it a versatile tool for saved searches.

Conclusion

The “When Ordered By” feature in NetSuite saved searches is an essential tool for creating precise and insightful reports. By aligning data columns based on their relationships, it ensures accuracy and relevance in summary search results. Whether you’re analyzing purchase history, inventory transactions, or sales trends, this feature adds significant value to your NetSuite customization toolkit.