Working with NetSuite Saved Search: Basic and Advanced Techniques
Earlier in the year, we gave you an introduction to NetSuite saved searches, which help users analyze NetSuite data and use the results for deeper insights into strategic business decisions. Now it’s time to dig a little deeper. Whether you’re still just getting started with NetSuite saved searches or you’re on your way to becoming a seasoned pro, there’s something in here that can help take your saved search game to the next level.
Basic NetSuite Saved Search Best Practices
Saved searches are a powerful tool for analyzing any type of information in NetSuite. Build your favorite searches, then save and run them on-demand for to-the-minute updates. But setting up the most efficient saved search takes some planning and preparation, and a couple best practices that will help you get the results you want.
Define the Purpose
In order to get what you want from your saved search, you have to know why you’re looking for it. What are you looking for? What fields and filters need to be included in the search? Knowing the purpose of the search will help you build one that is accurate and efficient.
Keep Filters Simple
Using simple filters can keep your searches from becoming a tangled ball of operators that are next to impossible to manage. Stick with simple filters like AND and OR operators that can be grouped using parentheses. If you use more complex filters such as NOT, IN, BETWEEN, or CONTAINS, your filters will be a headache to manage.
Minimize Your Number of Criteria
Much like using complex filters, the more criteria you add to your search, the longer it will take to get the results you’re looking for. If you can minimize the number of criteria used and employ simple filters, your search will certainly be more efficient. And so will you.
Get the Right Record Types
Did you know that different record types have different fields and filters available? This means that if you choose the wrong record type for your search, you could get searches that are inefficient at best, and inaccurate at worst. This goes back to defining the purpose of the search: Know what information you need to get so you can ensure you’re selecting the right record types.
Less is More with Joins
Joins, used to link records together within a saved search, are helpful in building more complicated searches. But if you string too many together, they’ll bog down saved search execution time. Less is more: Only use joins when necessary.
Use Summary Types
Summary types, which are used to summarize data in a saved search, help reduce the number of results you get from your saved search, which in turn makes the saved search more efficient. Common summary types you’ll see are SUM, COUNT, AVERAGE, and MIN/MAX.
Optimize and Test, Test, Test!
Once you’ve built your saved search, you can optimize it in several ways, including adding indexes to the fields used in the search, employing summary types that will rescue the number of results, and capping the number of criteria your search uses. Then test the search to make sure it retrieves what you were hoping for. Try running the search with different criteria, cross-referencing the results for accuracy, and timing the search execution. From there, you can find and fix any issues to make sure your saved search is as efficient and accurate as possible.
Dig Deeper with Advanced Techniques
Did you already know those basic best practices for NetSuite saved searches? Then you’re ready to tackle some more advanced saved search techniques. Try these on for size:
Saved Search Formulas
NetSuite saved searches include formula fields, which can be used to limit search results more precisely or perform specific actions on results, such as converting dates to strings.
Here’s how you build a formula filter:
- Open the saved search you want to work on and click Edit, then select the Criteria tab.
- In the drop-down menu, select the formula option that works best for your goals (Date, Numeric, or Text)
- In the FORMULA box, write the formula using NetSuite formula syntax and click Set.
- Use the preview to make sure the formula works as intended, then save and run your search.
Because NetSuite has its own formula syntax, you’ll need experience writing SQL and Oracle SQL (Oracle 10g or Oracle 11i) in order to use formulas in your saved searches. (We did say these were the advanced techniques!) But formulas come in handy for several things, such as calculated data like summing up values or performing mathematical operations. Or you can use case statements in formula fields that let you manipulate data based on specific criteria. Try using case statements to create conditional logic within your formula.
Join Saved Searches
The easiest way to join results from several different saved search types—such as for creating a dashboard that drills down into data—is to use an ellipsis. In the Results tab, select a field with a “. . .” in the FIELD drop-down menu.
But the joins you need might not always be available that way. Enter the more advanced technique: Start by creating two separate saved searches with different accessibility, then link them using a formula. In the first saved search, go to the Results tab and enter the URL of the second saved search into a Formula (Text) field. Finally, replace the necessary fields in the URL using the CONCAT function and a double pipe “||” operator.
Export Your Saved Search
There are occasions when you’ll need to access your saved search data outside NetSuite. Exporting the search enables analysts to view and/or work with the data offline. Start in Reports > Saved Searches > All Saved Searches to see all your public saved searches. Next, click View to open the saved search you want to export, and select the appropriate format (CSV, Excel, PDF) for your export.
Exported searches can give your team enhanced reporting and data analysis in addition to letting you collaborate on results with teams who do not have access to NetSuite. Want to add some additional safety to your data? Regularly export saved searches as backups in case there is a system crash or data loss. That way you can rest easy knowing there’s a recent copy of your data that you can restore to the system.
Saving the Easiest Tip for Last
Of course, there’s another really easy way to work with saved searches, and that is integrating Excel with NetSuite to automatically pull saved search results from multiple searches into one dynamic dashboard. Create pivot tables, use macros and formulas, all within the familiar environment of Excel. Save the dashboard and simply refresh any time you need the most recent data.
And what advanced techniques do you need to do this? None at all. It’s actually very simple: CloudExtend Excel for NetSuite gives you everything you need to manage and analyze NetSuite data in real-time, right in Excel. Want to use Excel formulas and complex models? You need CloudExtend. How about running one or more saved searches simultaneously in Excel with a single click—or on a regularly scheduled basis? You should try CloudExtend.
CloudExtend Excel for NetSuite does all the heavy lifting so you can be the NetSuite data hero you want to be while having more time back in your day to do other things. (Like maybe learn SQL!) Whatever your goals, take CloudExtend for a free test drive and see how easy your NetSuite saved search experience could be.