Marty Zigman on "Understand NetSuite ‘When Ordered By’ Saved Search Feature"

Understand NetSuite ‘When Ordered By’ Saved Search Feature

By Marty Zigman , on April 30, 2021

This article is relevant if you are curious about how to use NetSuite Saved Search “When Ordered By Field” in a Summary/Aggregate search.

Background

NetSuite’s saved search tool offers a feature that may not be well understood but can be very useful when performing an aggregate/summary search. The NetSuite help offers up four examples of how to find the answers to these inquiries:

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

In a more recent client example, while working with one of our Senior Analysts, it became apparent that there may be a different way to explain this feature.

Multiple Daily Imported Exchange Rate Records

NetSuite maintains a daily exchange rate for imported foreign exchange prices. When performing an inquiry for the exchange rate for a particular day, NetSuite will provide information based on the last updated rate for a currency for that day. However, behind the scenes, NetSuite is holding the values for all the rates that were imported.

While not formally documented, you likely can retrieve NetSuite lists that are published under the Records Browser but do not appear in NetSuite’s formal saved search list. Consider my 2016 article, Uncovered: How to use SuiteScript to Create Unpublished Saved Searches. In this case, we used the ‘currencyrate’ list to obtain the values of the foreign exchange prices that had been previously imported.

In the example provided (click on images), there were three imports for foreign exchange values for the same base currency, transaction currency (target), and transaction date. If we wanted a saved search to return the first imported rate, we would need something to sort on to get the first record that was imported. Since there is no date created or last modified date on this search, we need something else to sort on; thus, we can use the integer-based internalid to act as a sort key which (most typically) increments as new records are inserted in the database.

Consequently, if we group on the currency and the date, and we want the exchange rate of the first or last record inserted, we can use the ‘When Ordered By Field’ on ‘internalid’ and use Maximum to get the Last or Minimum to get the First record.

Click through the related images to develop an appreciation and reinforce your understanding of the saved search feature.

Work with NetSuite Professionals

I am grateful that I get to work with an exceptionally great team. More recently, one of my senior consultants was offering explanations to help me better understand the ‘When Ordered By Field’ feature. I didn’t have a specific personal or client challenge to anchor my understanding — but I listened and conceptually understood. In due course, one of my other senior consultants was presented with the client challenge noted above. Through the conversation, I immediately offered up the ‘When Ordered By Field’ solution which expanded his thinking; after doing some investigation, he returned, and indicated that the challenge was solved! As such, I recognized that it was important to help others learn more about this likely underutilized capacity.

The great thing about being in a powerful team configuration is that each member is not alone — we help each other solve our client situations. We collectively get better as we share. It takes courage to tell the truth and ask for help when we do not know — and it is great to work with other professionals to help produce solutions.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like to work with NetSuite professionals that are intelligent, resourceful, reliable, and trustworthy, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.