N/datasetLink Module

Use the N/datasetLink module to link datasets. When you link two datasets, you can use data from both datasets in your workbooks.

The N/datasetLink module lets you logically link two datasets and use data from both datasets in your workbook visualizations (such as pivots). Linking datasets is useful when you cannot use joins in the SuiteAnalytics Workbook UI or the Workbook API to join record types explicitly. Linking datasets does not merge or join the datasets. Instead, you specify an expression (which usually represents a column that shares common data between the two datasets, such as a date), and this expression is used to link the datasets.

After datasets are linked, you can access all of the data in both datasets to use in workbook visualizations. For example, when you create a pivot in a workbook, you can specify a linked dataset (as a datasetLink.DatasetLink object) to use as the data source for the pivot. You can use fields in both datasets to create data dimensions, data measures, sections, and other elements of the pivot.

For more information about linking datasets in SuiteAnalytics Workbook, see Dataset Linking in SuiteAnalytics Workbook.

To link two datasets, you must do the following:

1. Create the datasets that you want to link.

   You can use dataset.create(options) to create a dataset in a script. If you use this method to create a dataset, you must also use Dataset.save(options) to save the dataset before you can link it with another dataset. Alternatively, you can use the Dataset Builder Plug-in to create datasets. For more information, see Dataset Builder Plug-in.

                 // In this example, period, department, and total are dataset.Column objects that
   // represent fields on the budgets record type (or a joined record type)
   var myDataset1 = dataset.create({
       type: 'budgets',
       columns: [period, department, total],
       name: 'Example Dataset 1'
   });
   
   myDataset1.save(); 
   
               

                 // In this example, postingperiod, department, and amount are dataset.Column
   // objects that represent fields on the salesinvoiced record type (or a
   // joined record type)
   var myDataset2 = dataset.create({
       type: 'salesinvoiced',
       columns: [postingperiod, department, amount],
       name: 'Example Dataset 2'
   });
   
   myDataset2.save(); 
   
               

2. Load the datasets that you want to link, if necessary.

   In general, you can create datasets in one script, then load and link them in another script. If you are creating a workbook visualization (such as a pivot) in a script, you can create and link datasets directly in the same script, then use the linked dataset in your visualization. However, if you are creating a visualization using the Workbook Builder Plug-in, you must load the datasets in the plug-in script before you can link them. For more information, see Workbook Builder Plug-in.

                 // In this example, the id parameter values represent the script IDs of
   // plug-in scripts that create datasets using the Dataset Builder Plug-in
   var budgetDataset = dataset.load({
       id: 'customscript1772'
   });
   
   var salesDataset = dataset.load({
       id: 'customscript1773'
   }); 
   
               

3. Create expressions from columns in each dataset.

   In a dataset, a column represents a field on a record type (or a field on a joined record type, if the base record type of the dataset is joined with other record types). Datasets are linked using a column that shares common data (for example, a date), and you must use Dataset.getExpressionFromColumn(options) to create expressions for this common column in each dataset.

                 // In this example, the alias parameter value represents the alias set
   // on the associated column in the dataset
   var budgetMachinePeriodExp = budgetDataset.getExpressionFromColumn({
       alias: 'budgetmachineperiod'
   });
   
   var postingPeriodExp = salesDataset.getExpressionFromColumn({
       alias: 'postingperiod'
   }); 
   
               

4. Link the datasets.

   Use datasetLink.create(options) to link your datasets. You must specify the original datasets (as dataset.Dataset objects), the expressions for the common column (as workbook.Expression objects), and an ID for the linked dataset.

                 var myDatasetLink = datasetLink.create({
       datasets: [budgetDataset, salesDataset],
       expressions: [[budgetMachinePeriodExp, postingPeriodExp]],
       id: 'myLinkedDatasetId'
   }); 
   
               

5. Use the linked dataset to create a visualization.

   For example, if you are creating a pivot, you can use workbook.createPivot(options) and specify the linked dataset (as a datasetLink.DatasetLink object).

                 // In this example, rowSection and columnSection are workbook.Section
   // objects that represent sections in a pivot
   var myPivot = workbook.createPivot({
       id: 'myPivotId',
       rowAxis: workbook.createPivotAxis({
           root: rowSection
       }),
       columnAxis: workbook.createPivotAxis({
           root: columnSection
       }),
       name: 'My Pivot',
       datasetLink: myDatasetLink
   }); 
   
               

In This Help Topic

• N/datasetLink Module Members

• DatasetLink Object Members

N/datasetLink Module Members

Member Type	Name	Return Type / Value Type	Supported Script Types	Description
Object	datasetLink.DatasetLink	Object	Server scripts	A representation of two datasets that are linked using datasetLink.create(options).
Method	datasetLink.create(options)	datasetLink.DatasetLink	Server scripts	Links two datasets using a common column expression.

DatasetLink Object Members

The following members are available for a datasetLink.DatasetLink object.

Member Type	Name	Return Type / Value Type	Supported Script Types	Description
Property	DatasetLink.datasets	dataset.Dataset[]	Server scripts	The linked datasets that the datasetLink.DatasetLink object represents.
	DatasetLink.expressions	Array<workbook.Expression[]>	Server scripts	The column expressions for the datasetLink.DatasetLink object.

Related Topics

• N/datasetLink Module
• SuiteScript 2.x Modules
• Dataset Linking in SuiteAnalytics Workbook

General Notices