Using Stored and Non-Stored Custom Fields in Saved Searches

Overview:

Saved Searches in NetSuite are a powerful tool that allows users to search through thousands of records and return only the data they need. However, combining and joining data from different record types into one saved search has its challenges. If a user wanted to create a report based on transaction data and include a column with associated customer data, the process can become more burdensome. In some cases, prior configuration of custom fields is required to obtain the desired result. This is where using custom fields that are stored and non-stored, as well as a workflow, come into play.

Use Case:

Imagine a NetSuite user in the Accounts Receivable department. They have been keeping customer notes in the customer record to track open receivables and status updates. They can see these notes in the Communication subtab under User Notes.

To make day-to-day operations easier, the user wishes to create a customized AR Aging report in their NetSuite environment. In this case, the user would like to have the latest customer note from the customer record pulled into a column in the AR Aging report. This way, they can easily keep track of customer notes directly on the report so they can get a clearer picture of open receivables without having to look at the customer record.

A Transaction Saved Search allows the user to replicate the standard AR Aging report via criteria and formulated results. However, pulling the latest customer note from the customer record in a transaction type saved search is a bit trickier. Joining fields from different records is possible in NetSuite, but it has some quirks that users should be aware of. In the user’s case, joining a customer field on a transaction saved search would result in customer data that has an associated transaction. The user is not looking for transaction notes, but simply the latest note on the customer record.

A solution presents itself when custom fields are introduced into the equation. Custom fields can be created for almost any type of record. They can be stored or non-stored: updated manually or dynamically. In this user’s case, they can use both stored and non-stored custom entity fields in conjunction with a saved search and a workflow to get the desired custom report. While this may sound convoluted, it is simpler than it seems.

Configuration:

To start the configuration, the user must create a transaction type saved search to replicate the standard AR Aging report. More information on how to do this can be found in this article. Once the AR Aging report has been created, the user must address how to join customer record data with this saved search.

This next process has a few steps to follow along:

1. Create saved searches for latest user note and all user notes
2. Create a non-stored custom field and a stored custom field
3. Create workflow that sets stored field value equal to non-stored field value
4. Add stored custom field to AR Aging Saved Search results

Similar information on how to do this can be found in this article.

1. Create Customer Note Saved Searches:

Start by creating a customer saved search that pulls the latest customer user note.

Navigation: Lists -> Search -> Saved Searches -> New -> Customer

• Search Title: Latest Customer User Note

Criteria:

Filter: Stage      Description: is Customer

Results:

Field: Formula (Text)   Summary Type: Maximum

Formula: max({usernotes.note})KEEP(DENSE_RANK LAST  ORDER BY {usernotes.notedate})

Available Filters:

Filter: Internal ID

Note: This search will be used in the Non-Stored Custom Field in Step 2.

Create another customer saved search that pulls all customer user notes.

• Search Title: All Customer User Notes

Criteria:

Filter: Stage      Description: is Customer

Results:

Field: User Notes : Memo

Note: This search will be used in the Workflow in Step 3.=

2. Create Non-Stored and Stored Custom Field

Navigation: Customization -> Lists, Records, & Fields ->  Entity Fields à New

Non-Stored Custom Field

Label: Non-Stored Customer Notes    Type: Text Box              Store Value: Uncheck

Applies To Subtab: Customer          Validation & Defaulting: Search – Latest Customer User Note

Stored Custom Field

Label: Stored Customer Notes             Type: Text Box              Store Value: Check

Applies To Subtab: Customer

Note: The Non-Stored Field will update dynamically as new customer notes are entered into the environment using the Latest Customer User Note Saved Search. As of NetSuite 2023.2, Non-Stored Fields will show up when selecting a field in search results, however, they will display as blank when the search is run. This is why a stored field is needed and paired with a workflow to keep the field value up to date.

3. Create Workflow to set Stored Field Equal to Non-Stored Field

Navigation: Customization -> Workflows -> New

Basic Information:

Name: Sourcing to Stored Customer Note Custom Field

Record Type: Customer

Sub Type: Customer

Execute as Admin: Checked

Release Status: Released

Keep Instance and History: Always

Enable Logging: Checked

Initiation:

Scheduled: Checked

Saved Search Filter: All Customer User Notes (Created in Step 1)

Frequency: Every 30 Minutes

Scheduled From Date: Today’s Date

Click Save

State:

Select State 1

Click on “New Action” in bottom right corner

Click on “Set Field Value”

Basic Information:

Trigger On: Entry

Parameters:

Field: Stored Customer Notes (Created in Step 2)

Value:

Select Formula

Enter ID of Non-Stored Customer Notes Field             fx: {custentity1}

Click Save

Note: Once the workflow is set to released, every 30 minutes, it will set the value of the Stored Custom Field equal to the value that the Non-Stored Field is currently displaying.

4. Finalize AR Aging Saved Search

Navigation: Lists -> Search -> Saved Searches

It is now time to put it all together. Open the AR Aging saved search from the beginning of the configuration. The Stored Field that was created in Step 2 will be used in the AR Aging saved search to display the latest user note in a column. Add the stored field to the results with a Group Summary Type, fill in a custom label name, and press “Save and Run”.

Results:

Field: Customer Fields… : Stored Customer Notes

Summary Type: Group

The saved search results should look like this:

Conclusion:

NetSuite saved searches are a great tool to create highly customized datasets that standard reports could not do. Many search types are available and provide users with numerous possibilities to get the exact data they are searching for. However, there are some limitations to what data NetSuite saved searches can handle and join without some customization. Using stored and non-stored custom fields is a great way to extend the capability of Saved Searches and allow users to join different data types to get the results they are looking for.

Christoffer Moelgaard