Welcome back to SuiteTip, your trusted guide to unlocking NetSuite's full potential! In this article, we'll explore how to utilize Formula Fields in Summary Criteria within NetSuite Saved Searches, a powerful feature that can transform your data analysis.
In our previous article "SuiteTip: Use Formula Fields as Criteria in Saved Searches", we focused on using the Standard Criteria. In this article, we will expound more on the Summary Criteria.
Why Use Summary Criteria?
There may be scenarios where users would need to filter the results of a Saved Search based on the Results of a Formula field with a Summary Type that contains aggregate functions (e.g. MIN, MAX, SUM) in the formula.
If the formula is entered in a Formula field under the Criteria > Standard subtab, running the search will result to an Unexpected Error.
Summary Search Filters
If you defined summary types for any of your search results columns, you can apply additional filters to the summary results page of the search.
The summary search results page displays only those results fields that have a summary type assigned. The rest of the results fields are available in the detailed search results view that opens when you click an item on the summary search results page.
Additional filtering can be applied only to the fields that are displayed on the summary search results page.
Known Limitations
- Multiplied Results: When using multi-select related record types or multi-select fields from related records as summary search filters, the search results may be multiplied. This issue is particularly associated with related record types that have join fields available as filters (e.g., Account Fields...). Unfortunately, there is no current workaround for this limitation.
- KPI Inconsistencies: Key Performance Indicators (KPIs) cannot accurately enforce summary search filters because KPI calculations do not consider groupings. If a Saved Search with summary filters is used as a custom KPI, the KPI result may not align with the search results when executed independently.
Sample Scenario
You have a Saved Search that analyzes items in Blanket Purchase Orders and calculates the remaining quantity to be ordered using a formula.
- Criteria tab > Standard subtab:
Add the formula to the Results tab of your Saved Search to calculate the desired value.
Formula: MAX({quantity}) + SUM({applyingtransaction.quantity})
When you run the search, you notice that some lines have a result of 0, indicating no quantity left to order.
You want to remove these lines that have no quantity left to be ordered. To do this, a Summary Criteria Filter should be added to the Search.
- Criteria tab > Summary subtab:
This uses the same formula and summary type in the Results tab of the search. For the description, we used "is not equal to 0" because we want to remove lines that equal 0.
Execute the search again, and you'll see that the summary results now exclude the lines with a formula result of 0.
Summary Criteria, combined with Formula Fields, offer a powerful customization tool for your NetSuite Saved Searches. By utilizing this, you can create highly tailored search criteria that address your business's unique needs. This flexibility is particularly advantageous for companies dealing with intricate data analysis requirements.
As always, when working with Formula Fields, it is crucial to test and validate your formulas to ensure accuracy. This step ensures that your search criteria align perfectly with your business objectives. By doing so, you unlock the full potential of NetSuite's Saved Searches, enabling advanced data analysis and generating reports that drive strategic decisions.
Happy Searching! 🔍
---
Stay tuned for our next SuiteTip, where we’ll explore more tips and best practices for navigating NetSuite Reports and Saved Searches!
We encourage you to share your own tips, ask questions, and engage with fellow community members. Your experiences can help others on their journey to becoming NetSuite analytics experts!