NetSuite's saved search functionality provides users with a powerful and customizable tool to retrieve and present data from records within NetSuite. It allows users to define criteria and filters to extract relevant results, enabling them to run reports, conduct data analysis, and make key business decisions. With formulas, saved searches act as a dynamic reporting engine that offers businesses minimal restrictions in getting their data in the right format.
What are NetSuite Formulas
In NetSuite, formulas are used within saved searches to perform calculations, manipulate data, and generate dynamic results based on specific criteria. These formulas can be categorized into several types, each serving a distinct purpose. Here are the main types of NetSuite formulas:
Formula (Text)
Purpose: Used to manipulate and display text data.
Examples:
- Concatenating fields: CONCAT({firstname}, ' ', {lastname})
- Extracting substrings: SUBSTR({itemid}, 1, 5)
Formula (Numeric)
Purpose: Used to perform calculations that return numeric results.
Examples:
- Calculating totals: {quantity} * {rate}
- Displaying a value for a null numeric result: NVL({quantitycommitted},0)
Formula (Date)
Purpose: Used to manipulate and calculate dates.
Examples:
- Adding days to a date: DATEADD({startdate}, 30)
- Extracting the year: TO_CHAR({trandate}, 'YYYY')
Formula (Percent)
Purpose: Used to perform calculations that result in a percentage.
Examples:
- Calculating profit margin: (({amount} - {cost}) / {amount}) * 100
- Percentage of total: ({quantity} / SUM({quantity}) OVER ()) * 100
Formula (Currency)
Purpose: Used to perform financial calculations involving currency amounts.
Examples:
- Converting amounts: {amount} * {exchangerate}
- Conditional currency calculation: CASE WHEN {currency} = 'USD' THEN {rate} ELSE {fxrate} END
By leveraging the power of NetSuite saved search formulas and functions, users can create highly customized and insightful reports, aiding in better decision-making and efficient data management. These advanced formulas enable users to manipulate and analyze data with precision, uncovering valuable trends, patterns, and metrics. By mastering NetSuite formula syntax and best practices, businesses can enhance their reporting capabilities, optimize operations, and gain a competitive edge through data-driven insights.
Saved Search Methods
Basic Saved Search
The Basic Saved Search is the foundation of data querying in NetSuite. It allows users to define simple criteria and filters to retrieve relevant records. You can specify the type of record (e.g., transactions, customers, items) and apply basic filters (e.g., date range, status).
Example: To find all sales orders created in the last month, you can set the criteria to:
- Type: Sales Order
- Date Created: Last Month
Summary Saved Search
Summary Saved Searches aggregate data and provide summarized views of information, such as totals, averages, and counts. This method is useful for generating high-level reports and dashboards.
Example: To summarize total sales by customer:
- Type: Sales Order
- Criteria: Date Created = This Month
- Results: Group by Customer, Sum of Amount
Join Saved Search
Join Saved Searches allow users to pull in related data from different records. By using joins, you can create complex queries that span multiple record types.
Example: To find sales orders and their associated customer details:
- Primary Record Type: Sales Order
- Join Field: Customer (will be donated as ‘Customer Fields…’ when searching for join)
- Display Fields: Sales Order Number, Customer Name, Customer Email
Formula Saved Search
Formula Saved Searches use SQL-based expressions to create custom calculations and logic within search criteria and results. This method is ideal for advanced users who need precise control over their data output.
Example: To calculate the total sales amount excluding tax:
- Formula Field: {amount} - {taxamount}
- Criteria: Type = Sales Order
Tips for Maximizing NetSuite Saved Searches
Use Criteria and Filters Wisely
- Narrow down your search results by applying specific criteria and filters. This ensures your searches are efficient and return only the relevant data.
Leverage Formulas
- Utilize formulas to perform non-native calculations and create custom logic within your searches, either to filter or present different data in your results.
Create Summary Searches
- Create summary level saved searches to generate high-level overviews of your data for easier consumption and understanding.
Schedule Searches
- Automate your reporting by scheduling saved searches to run at regular intervals. This ensures you always have up-to-date information without manual intervention.
Share and Save Searches
- Save your searches for future use and share them with other team members. This promotes consistency and ensures everyone has access to critical data.
By harnessing the power of NetSuite saved search formulas and methods, businesses can create sophisticated data queries, streamline business processes, and uncover actionable insights. Whether you're building simple or complex saved searches, mastering these techniques is essential for optimizing your NetSuite investment. From improving financial reporting and inventory management to enhancing customer relationship management, the possibilities are endless.
If you're looking to unlock the full potential of NetSuite saved searches, Luxent can help. Our NetSuite consulting experts can provide guidance, training, and support tailored to your specific needs. Contact us today to learn how we can elevate your NetSuite experience.
