How to Find Saved Searches That Use a Specific Field Within the Criteria, Results, Email Options, Formulas or Available Filters

Have you ever been tasked with finding all of the saved searches that use a specific field in NetSuite? This can seem like a daunting task, especially because the field could be used within the criteria, results, email options, available filters, or even formulas of various searches. The analytics audit trail search provides an answer.

There are a number of reasons why you might need to find all of the saved searches using a specific field. For example, in the event that a field is going to be replaced by new fields in your NetSuite account, it is important to track down the saved searches that might be affected by the field changes so you can make the appropriate revisions. Otherwise, some searches might not pull the right information that they were intended to, email alerts might not get sent, reminders might get thrown out of whack… the list goes on…But how can you possibly find all of the searches using a specific field?

SuiteAnswers Answer ID 46075 offers a solution to find all of the saved searches that use a specific Result Field. The help article instructs the user to create a ‘Saved Search’ saved search, and add ‘Result Field’ to the results of the search. It then instructs the user to export the saved search results to an Excel file, and use the data filter to find all of the saved searches using a specific field in the results. I was not a fan of this solution, because this wouldn’t work if you’re trying to find saved searches that might use the field in different areas rather than just the results. Also, who wants to create a saved search just to export the results to an Excel file that requires filtering to find only a piece of the information they’re looking for?!

Wouldn’t it be great if you can pull all of the saved searches using a specific field, regardless of where it’s being used in the saved searches, with just one saved search?

Here’s the good news: You can!

I have a trick to finding saved searches using a specific field that will save you lots of time!

Scenario

You have been asked to create two new fields in the system, ‘Project Lead’ and ‘Project Secondary Lead’ that will replace the use of the ‘Project Manager’ field on a new Project Form that is going to be used. You need to find all of the saved searches in the system that use the old ‘Project Manager’ field so you can make the appropriate updates to the searches.

Solution

Step 1: Prepare, and Customize the Audit Trail View

Before tracking down the saved searches, I suggest that you take a couple minutes either to review the audit trail of an existing saved search that you know uses the specific field, or create a test saved search using the field you’re searching for. Use the field in different areas of the saved search, such as the criteria, results, available filters, formula fields, email options, etc., then review the audit trail so you can see how the specific field is being tracked. Doing this will help you design your saved search properly to pull the right information.

To have a better view of saved search audit trails while reviewing, you can customize the view of the audit trail so that it includes the ‘New Value’. This is very helpful, because there might be cases where a field is tracked in the audit trail without a ‘Component Name’. Normally you need to drill into the Component Name to see the New Value, but if the Component Name is missing, then you can’t drill into it to see the New Value. Adding the New Value to the view of the audit trail will help uncover some hidden values. You can choose to add other fields to the audit trail view as well. For the purposes of this example however, we only need to expose the New Value. 

To customize the saved search audit trail view, while in edit mode of a saved search, navigate to the Audit Trail subtab. Then click on ‘Customize View’. Under the Results subtab, add ‘New Value’. Click ‘Save’ when done. Now when you go back to a saved search audit trail, make sure the ‘View’ is set to the custom view you just created. Below is an example of a saved search audit trail after the view was customized to include the New Value.

For the scenario in this article, I created a test saved search and determined that whenever the ‘Project Manager’ field was used, except as a Recipient From Results, an Email Updated Field or as a field that has another field joined from it, the field ID of it appeared in the audit trail under the ‘New Value’. So, we know in the saved search we will be making, we will need to search for New Values containing the field ID.

After further reviewing my test saved search, I determined that when the ‘Project Manager’ field was used as a Recipient From Results, an Email Updated Field or as a field that has another field joined from it, the Component Name containing ‘Job Manager’ would appear in the audit trail. A New Value doesn’t show the Project Manager field ID in these cases, so we will need to also search for Component Names containing ‘Job Manager’ when we create our saved search. In fact, when any field is used in the Recipient From Results or Email Updated Field areas of saved searches, a New Value does not pull through at all; only a Component Name.

Step 2: Create a New Analytics Audit Trail Saved Search

Step 3: In the Criteria of the Search, Add the Appropriate Filters and Expressions

See below image for an example. Make sure to check the ‘Use Expressions’ box to use And/Or and Parentheses in the criteria.

I have my criteria set up so it will search for saved searches where the New Value contains %projectmanager%. Note that I have the field ID wrapped in wildcards without braces. The criteria of this search is also set up so it will search for Component Names that contain ‘Job Manager’, based on the findings from my research. Depending on your target field, you might need other criteria.

Step 4: In the Results of the Search Enter the Following Fields and Summary Types

Note: We want to use the ‘Group’ summary type for the ‘Record Title’ and the ‘Component Type’, and the ‘Maximum’ summary type for the ‘New Value’. If we didn’t use these summary types for the results, then the search may pull through a lot of unneeded lines. If we used the ‘Group’ summary type for the New Value, for example, if a formula was revised many times in the search that used the specific field, the new value of that formula will pull through for each time a revision was saved. We do, however, want to ‘Group’ the Record Title and the Component Type. Grouping the Component Type will allow us to see if the specific field was used in multiple areas of a single saved search.

Step 5: Save and Run the Saved Search

The titles of the saved searches that use the ‘Project Manager’ field, whether it be within the criteria, formulas, results, email body, email subject, email recipient from results, email updated field, or available filters will display. The ‘Component Type’ column will show where the field was used in the saved search. ‘Filter’ means it was used as a filter in the criteria of the search, and ‘Column’ means it was used in the results of the search. ‘Search Email Options’ means it was used either in the subject or body of the saved search email, and ‘Email Recipient from Results’, ‘Email Updated Field’, and ‘Available Filter’ are self-explanatory. The ‘New Value’ column shows a formula if the field was used in a formula.

As you can see from my screenshot, the saved search titled “PM Search in criteria formula and results CMW” is displayed twice. This is because the ‘Project Manager’ field is being used in both the criteria and results of the search, as indicated under the ‘Component Type’ column. From my screenshot, you can also see that the searches that use the ‘Project Manager’ field as the Email Recipient From Results and the Email Updated Field do not show a ‘New Value’. This is why we had to include in our search criteria to search for the ‘Component Name’ that field is tracked under when used in those areas of the saved searches rather than the ‘New Value’. 

Note: If the specific field was added to a saved search and then later removed after it was saved, the search may get pulled through to the results of your saved search when using this method, even if the field isn’t currently being used in the search. This is because the New Value from when that specific field was used in the search was tracked in the audit trail and will remain in the audit trail even if it was later removed. I considered adding to my saved search criteria to exclude any searches that had an Old Value containing the field I’m searching for, however, I realized that wouldn’t be a reliable route for finding saved searches where the specific field may have been added, removed, and then added again in the saved search.

Limitations

If the specific field you’re looking for is a custom field, or is a standard field that is joined with a custom field in a saved search, how it is logged in the audit trail is a little different. When fields are joined, the New Value in the audit trail will show the field ID for the ‘joining’ field, but not the ‘joined’ field. Furthermore, if a custom field is the ‘joined’ or ‘joining’ field, a Component Name does not show in the audit trail. Because of this, using the searching method in this article may omit some searches if the field you’re searching for is joined with a custom field and is the ‘joined’ field. If the field is the ‘joining’ field however, this searching method will work.

If the field you’re searching for is joined with a custom field and is the ‘joined’ field, this approach might miss it.

Secondly, you might recall that a ‘New Value’ does not show in the audit trail when a field is used  as a Recipient From Results. Furthermore, since a Component Name doesn’t show in the audit trail for joined custom fields, this method might also miss some searches if the field you’re searching for is a joined custom field in the Recipients From Results.

If the field you’re searching for is a joined custom field and used only in the ‘Recipients From Results’ section of your search, this approach might miss it.

Examples to Illustrate the Behavior of Custom Fields and Joined Fields in Saved Search Audit Trails

Let’s go through some examples to further clarify how custom and joined fields are captured in the audit trails.

Example 1: ‘standard.custom’ field join

With a standard.custom field join, the custom field will show as the New Value in the audit trail.

The ‘joining’ field ID will always be the one that shows. Notice that there isn’t a Component Name even though the custom field was joined with a standard field. Also notice that the ‘joined’ field ID did not show in the New Value.

Example 2: ‘custom.standard’ field join

A custom.standard field join shows the standard field as the New Value in the audit trail. Again, consistent with the fact that the ‘joining’ field ID will show. Notice again that there isn’t a Component Name even though the field join involves a standard field:

Example 3: ‘custom.custom’ field join

A custom.custom field join shows the ‘joining’ field ID in the New Value, just like the previous examples:

Example 4: Custom field without join

When a custom field is used in a search NOT as a joined field, the audit trail shows the custom field as the Component Name and also the New Value, contrary to the previous examples:

Example 5: ‘standard.standard’ field join

As with the preceding example, notice that a Component Name shows in the audit trail. The first part of the name represents the ‘joined’ field. This means that if the field you’re searching for is a ‘joined’ standard field with a ‘joining’ standard field, you can find that specific field by the Component Name even though the field doesn’t show as the New Value:

Conclusion

In my previous article, I provided a solution for finding the particular saved search that sent an email alert with a custom subject. This article explains another way that Analytics Audit Trail saved searches can be extremely helpful. Finding the saved searches that use a specific field is a feasible task in NetSuite by following the method outlined in this article, or tailoring this method based on your search needs. You can also find the Reports using a specific field as well by following the same process by using or including ‘Reports’ as the ‘Record Type’ in your search.

Just like with most things, you will get a better end result if you prepare. In the example used in this article, when the ‘Project Manager’ field is used in saved searches as an Email Recipient From Results or Email Updated Field, the field ID does not get pulled through to the ‘New Value’ in the audit trail. Instead, just the ‘Component Name’ containing ‘Job Manager’ shows in the audit trail. If I didn’t take a couple minutes to verify how the Email Recipients From Results or Email Updated Fields are tracked in saved search audit trails when a specific field is used, the search I created would have omitted some important searches to update. If you spend a little extra time preparing how you’re going to set up your saved search, you will save more time in the long run. 

Finally, there are third-party tools like Strongpoint.io’s Flashlight which might help you tackle this challenge. However, this article shows that you can achieve the same goal with a native solution in NetSuite without depending on external tools.

Happy Searching!

Let us know what you think of this article by dropping a comment below. Be sure to share it with others and let us know what other admin tricks you’ve found useful. Also subscribe to get an alert in your inbox whenever we publish new NetSuite Insights.

Do you have NetSuite insights of your own you’d like to share?  Consider becoming an becoming an author on this community platform of top NetSuite professionals with a shared mission to raise the standards, one insight at a time!

Related Posts

1. How to Limit NetSuite Case Alert Emails to Business Hours (Yes, You Can!)
2. How to Quickly Find the Particular Saved Search that Triggered an Email With a Custom Subject Line in NetSuite