Create conditions
Conditions let you filter the query results in a dataset.
A condition filters entire records and fields from a dataset, which affects all workbooks that are based on the dataset. For example, if you create a condition that filters invoice records from a dataset, workbooks based on that dataset will also not include invoice record data.
In this step, you will create objects to represent conditions for the dataset.
-
In your script file, create a condition using dataset.createCondition(options). This condition filters any customer records that are inactive (that is, the Inactive box in the UI is checked). Because this condition applies to a field (
isinactive
) that does not have a corresponding dataset.Column object in your script, you must create a column for the field to use when creating the condition. Add the following code after the three dataset.createColumn(options) calls in Create columns:var inactiveColumn = dataset.createColumn({ fieldId: 'isinactive' }); var inactiveCondition = dataset.createCondition({ column: inactiveColumn, operator: 'IS', values: [false] });
The dataset.createCondition(options) method returns a dataset.Condition object. For a list of available operators, see query.Operator.
-
Create a second condition that filters any transaction records with a transaction date on or before March 3, 2020. You already created a dataset.Column object for the
trandate
field in Create columns, so you can use that object when creating the condition. Add the following code after the first dataset.createCondition(options) call:var trandateCondition = dataset.createCondition({ column: trandateColumn, operator: 'AFTER', values: ['3/3/2020'] });
Note that the date value you provide must be formatted according to the preferences set in your NetSuite account.
At this point, your script file should look similar to the following:
require(['N/dataset'], function(dataset) {
var tutorialDataset = dataset.create({
type: 'customer'
});
var salesRepJoin = dataset.createJoin({
fieldId: 'salesrep'
});
var transactionJoin = dataset.createJoin({
fieldId: 'entity',
source: 'transaction'
});
var entityIdColumn = dataset.createColumn({
fieldId: 'entityid'
});
var emailColumn = dataset.createColumn({
fieldId: 'entityid',
join: salesRepJoin
});
var trandateColumn = dataset.createColumn({
fieldId: 'trandate',
join: transactionJoin
});
var inactiveColumn = dataset.createColumn({
fieldId: 'isinactive'
});
var inactiveCondition = dataset.createCondition({
column: inactiveColumn,
operator: 'IS',
values: [false]
});
var trandateCondition = dataset.createCondition({
column: trandateColumn,
operator: 'AFTER',
values: ['3/3/2020']
});
});
SuiteAnalytics Workbook UI
In the SuiteAnalytics Workbook UI, conditions appear in the Criteria summary area above the Data Grid.
![Conditions in a dataset.](img/SuiteCloudCustomizationScriptingWebServices/SuiteScript/SuiteScript20/SuiteScript20/prop_Dataset_condition.png)
Previous: Create columns |