How to Create Custom Fields with Values from Summary Search Results
An extremely useful but little known capability of NetSuite is that it allows you to create a custom field that displays values derived from summary saved search results. Each summary search field displays a rollup value for a selected search result field. This value is dynamically calculated each time a form containing the summary search field is displayed.
For example, you can use a summary search field to display the total quantity of all line items on a transaction. The summary search field provides an alternative to using SuiteScript to calculate a custom field’s values. Note that this functionality is very similar to that of custom sub-lists, except the search results are displayed in a field rather than a sub-list.
Steps for Creating a Summary Search Custom Field
To create a custom field with a value derived from a summary search:
- Create or edit a summary saved search that rolls up to the result you want to display in the custom field.
- Add search criteria to filter out any records/lines you do not want included in the rollup value.
- Define a search results field for which values will be rolled up, and select a summary type. (Count, Sum, Minimum, Maximum, and Average are supported; Group summary type is not supported.) For example, you could set Quantity as the results field and set a summary type of Sum.
- Define an available filter field. This field is used to filter search results to include only those records with available filter field values that match the available filter field value of the current record. The value displayed for the summary search custom field is a rollup of search results field values for the records that have matching available filter field values.
- Create or edit the custom field.
- Select this search on the custom field’s Validation and Defaulting subtab.
- You can select a “Compare To” field. You can use this field in cases where you want to put the custom field on a form for a record type that is different from the summary search record type. For example, if you want to put a custom entity field showing the result of a customer record summary search on an employee custom form, you could select an employee record field whose values could be matched to the values for the search’s Available Filter field. Search results field values for all records with matching values for the Compare To field and Available Filter field would be used to calculate the value of the summary search custom field.
- Do not enable the Store Value option, as values for this kind of custom field are not stored.
Types of Fields that Support Summary Search Derived Values
You can select a summary search to provide rollup values for the following types of custom fields:
- Entity Fields
- CRM Fields
- Transaction Body Fields
- Other Custom Fields
- Custom Record Custom Fields
Custom Field Data Types that Support Summary Search Derived Values
You can select a summary search to provide rollup values for custom fields of the following types:
- Decimal Number
- Email Address
- Free-Form Text
- Integer Number
- Long Text
- Phone Number
- Rich Text
- Text Area
- Time of Day
Example Summary Search Custom Field
The following example shows the creation of a custom field to be displayed on custom purchase order forms, with the value of the field calculated by a purchase order summary search that sums the values for the purchase order line items’ Amount field. No Compare To field is needed, because the Internal ID field set as the Available Filter for the search can be matched to the Internal ID of the purchase order record displayed on the form.
Current Limitations for Summary Search Custom Fields
- Custom field values from summary search results are never stored. Field values are always calculated dynamically at run time. A user may be able to edit values, depending on the display options set for the custom field, but the edited values will not be stored. Also, because its values are not stored, this kind of field is not available in search results, including lists based on saved searches.
- Calculated values for this kind of field may be different for users with different permissions. Summary search results are rolled up for the records to which the current user has access. Because users with different permissions may have access to different sets of records, the calculated value of this field may vary per user.