Pages

2013/12/02

Displaying Summary of Saved Search Results in a Custom Field

This is a new feature available in Netsuite version 2013.2. It allows you to create a Saved Search to quickly total values from records and put this summarized value into a custom field (as opposed to creating a script to perform the same logic).

This is a great addition to Netsuite, but it's not for everyone. Below you'll find the pros and cons of using it before we go into the details on how to setup the Saved Search and the custom field.

PROS AND CONS

Pros:
  • Populate custom fields with results from familiar and easy to use Saved Searches
  • Don't need to know how to script at all
Cons:
  • The custom field does not store the value, so you cannot run searches or reports on it
  • The calculated value might change depending on the role currently accessing the record the custom field is on, for example if they do not have access to certain fields or records

SETTING UP

In order to start using this feature, you'll need to set up two things:
  1. A saved search that has the following:
    • Any amount of Criteria that you'd like to get the aggregate value of
    • One Result that needs to be summarized with any type other than Group
    • One Available Filter which is used to filter the results based on a match to the record the custom field is on
  2. A custom field that has the following:
    • Store Value must be unchecked
    • The Search created from step 1 selected in the Search drop down on the Validation & Defaulting sub-tab
    • Optionally, a "compare-to" Field can be chosen in cases where the record type that contains the custom field is different from the summary search record type
This is a lot of information, so let's go ahead and go through the process with an example of a typical situation where this feature comes in handy. We want to see the total expenses of a customer on a field in the customer record.

We know that each item in a Purchase Order is connected to a customer, so let's go ahead and do step 1 from the Set Up section and create the Saved Search on Transactions.

Since we want to total the expenses of a customer, our criteria for the saved search will be as follows:
  • The type of the transaction is Purchase Order
  • And we want to get all lines, so we let Main Line equal to false

The summarized results will be the sum of the absolute values of the amounts. This is to ensure we get a positive value on our custom field.


The available filter will be the Internal ID of the Customer Record searched on via a join field - Customer : Internal ID. 


The purpose of the available filter is so the saved search can filter based on an attribute or field of the record that the custom field is on. For this example, since the Internal ID is unique to each customer record, the saved search will only return the Purchase Orders which have a customer with the same Internal ID. 



Now that the saved search is created, all we have left to do is to create our custom field with the requirements summarized in the Set Up section.


And that's it! When viewing the record your custom field is on, the search will run and populate the field automatically.

No comments:

Post a Comment