Using When Ordered By Field for Search Results
The When Ordered By field option provides search results that return the value for a field when the value for another field is minimal or maximal. For example, you could use this option to return the:
-
amount for the most recent sales order by each customer
-
contact for the most recent case filed by a customer
-
sales rep for the top transaction by month
-
item with the largest transaction per month
The When Ordered By field is available when you select a Summary Type of either Minimum or Maximum on the Results subtab of a search record.
Normally, the When Ordered By field is a date field or a quantitative field. A Maximum summary type would find the record with the most recent date or largest quantity. A Minimum summary type would find the record with the earliest date or smallest quantity. (All of the examples above would use a Maximum summary type).
If the field you define in the When Ordered By field contains NULL values, these records appear last in the search results. However, if you use the Maximum summary type, records with NULL values appear first. To avoid this, do not use the Maximum summary type with the When Ordered By field set to a field with NULL values.
To return the value of a search results field when another value is minimal or maximal:
-
On the Results subtab of an advanced or saved search, in the Field column, select the field for which you want to return a value.
-
In the Summary Type column, select either Minimum or Maximum, depending on whether you want the value to be returned from the record where the When Ordered By field is minimal, or from the record where the When Ordered By field is maximal.
-
Select the field that you want to be minimized or maximized in the record returned.
For example, to return the amount for the most recent transaction by each customer, create a Customer search, select a results Field of Formula (Date), a Summary Type of Maximum, and a When Ordered By field of Date Created.
When Ordered By field does not support Long Text and Rich Text fields. Fields of these types are not available for selection in the dropdown list.
‘When Ordered By’ in NetSuite has the same behavior as Oracle’s analytic ‘keep dense_rank’. For example, when the following transactions are present:
|
Name |
Amount |
Date |
|
John |
123 |
01/23/2014 |
|
Mike |
10 |
05/12/2014 |
|
Peter |
777 |
05/12/2014 |
|
Peter |
777 |
06/12/2014 |
|
John |
15 |
12/12/2014 |
|
Mike |
-13 |
05/12/2014 |
|
John |
456 |
12/11/2013 |
If you want to know when the last transaction with the highest amount was created, go to the Results tab and add these fields:
|
Field |
Summary Type |
When Ordered By |
|
Date |
Maximum |
Amount |
The result is: 06/12/2014.
If you want to select the highest amount for the most recent transaction by each customer, go to the Results tab and add these fields:
|
Field |
Summary Type |
When Ordered By |
|
Name |
Group |
— |
|
Amount |
Maximum |
Date |
The results are shown in the table below:
|
Name |
Amount |
|
John |
15 |
|
Mike |
10 |
|
Peter |
777 |
If you want to select the lowest amount for the earliest transaction by each customer, go to the Results tab and add these fields:
|
Field |
Summary Type |
When Ordered By |
|
Name |
Group |
— |
|
Amount |
Minimum |
Date |
The results are shown in the table below:
|
Name |
Amount |
|
John |
456 |
|
Mike |
-13 |
|
Peter |
777 |
Related Topics
- Search Results Display Options
- Selecting Fields to Display in Search Results
- Entering Custom Labels for Search Results Columns
- Defining Summary Types to Roll Up Search Results
- Showing Totals in Search Results
- Applying Functions to Search Results Columns
- Defining Order and Overall Formatting for Search Results