Dataset Filtering

All saved search based dataset types (Search, Custom Label Search, Joined Search, Gantt, Graph) can be dynamically filtered using either the combination of Filter Field, Filter Operator and Filter Value or with a Filter expression (fields on the Reporter Dataset and Reporter Sub Search records). The latter field is usually useful when the search should be filtered on more than one parameter.

By filtering the search dynamically you can make sure that the data included in the report is only relevant for the selected base record or based on other input parameters. Value fields can also be used to dynamically filter searches.

This chapter aims to describe how the filtering of the saved searches works and how you should configure your datasets.

If your layout has a Record dataset defined you might want to filter your saved searches' result based on the internal id or some other field on the selected base record. Let us use our Project Scorecard template to explain.

This layout has a Record type dataset named "PROJECT" and the record type is defined as Project (internal record id = job). This means that we must always run this layout for a specific project. In this example we have selected the project Demo Project for the customer Virvelvind AB.

When I run the report for this project, I want to make sure that the project tasks listed in the PDF all belong to this project.

This template has two datasets that both include Project Task data, namely "PROJECT_TASKS" and "TASK_HIERARCHY". The first dataset's search is based on the Project record and the second is based on Project Task.

I do not want to hard-code the filter, project = Demo Project, in the searches. Then all projects would display the same data for only that specific project.

Instead I want Reporter to dynamically push a search criterion when running the layout so that the data returned is dynamic depending on which project I have selected.

The Filter Field, Filter Operator, Filter Value or Filter Expression follows the same syntax as if the search had been created directly in SuiteScript. All filters that we add to a search in the GUI has a Filter Field (the internal id of the field), filter operator and filter value.

The search will now only return data for my project. If I run this layout for another report, the internal ID will be different of course.

A useful tips when figuring out what to add as the filter field and operator, the Chrome extension called "NetSuite: Search Export" is very helpful. By adding the filter you wantdirectly in the search in the GUI you can use this extension to see how this translates to the SuiteScript (and also Reporter syntax).

For the other search, "REPORTER FOR PROJECTS | TASK DETAILS", we filter on project instead since that search is based on the Project task record type. If we would have used internalid and the filter field for this dataset as well, the search would have returned data for any project task with internal id 14157 in our example.

If you have a base record but your search should not be filtered based on the internal id of that record, you can also filter the search on any of the fields (custom or standard) from that record.

In the below example we have a Record dataset named "scevoa" based on a custom record called "Event Opportunity Application" (record type id = customrecord_scevoa).

The saved searches used are based on other custom records. Let us look at some examples:

Example 1
The dataset "Schedule" is based on a custom record called "Event Opportunity Schedule". In this case, the Schedule and Application custom records are not directly linked. They do however share the same parent record, "Event Opportunity". Both records have a field referencing their parent.

On our defined record, that field has internal id custrecord_scevoa_opportunity. Using the already existing dataset "scevoa" we can leverage the value stored in that field on our selected base record using the tag {scevoa.custrecord_scevoa_opportunity@id}. This tag consists of three parts: 1. the name of the dataset ("scevoa"), 2. the internal field id ("custrecord_scevoa_opportunity") and in this case also @id since this is a List/Record field and we want to reference the id and not the name of that value when filtering.

In our dataset search and the Schedule record type, the equivalent field has internal id custrecord_scevos_opportunity. This means that if we add this as the filter field (we also add .internalid here but that can be omitted for list/record typed fields) we can retrieve the schedule information only relevant for the Opportunity that is also referenced on our base record, Application.

Example 2
In the second example we have a custom field on our base record with internal id custrecord_scevoa_applicant (list/record field with Entity) and our dataset search is based on the Entity record type. This filter will then return the entity data that matches the internal id of the entity stored on the base record.

TEXT

Examples:

Filter Field

• name

• internalid

• subsidiary.internalid

• custrecord_sc_customer.internalid

Filter Operator

• anyof

• is

Filter Value

• {id}

• {DEPARTMENT_FILTER}

• ${Trx.subsidiary@id}

• A hard-coded value, e.g. the name of a specific record

Example:

[["subsidiary","anyof","{SUBSIDIARY}"],"AND",["class","anyof","{CLASS_FILTER}"],"AND",["department","anyof","{DEPARTMENT_FILTER}"],"AND",["location","anyof","{LOCATION_FILTER}"],"AND",["trandate","onorafter","{PERIOD_START}"],"AND",["trandate","onorbefore","{PERIOD_END}"]]