Joining Transaction Line and Transaction Accounting Line in a Dataset
In the Saved Search application, by default a Transaction saved search could include both accounting and operational data. In SuiteAnalytics Workbook, to include accounting and operational data in a transaction workbook, you might need to join three record types in a single dataset: transaction, transaction line, and transaction accounting line. These record types exist in one-to-many and many-to-many relationships, which can cause data duplication. For more information, see Data Duplication Based on Record Joins. There additional implications you should be aware of when joining these record types in a dataset:
-
If you need to join the transaction accounting line record type in a dataset, you must use the join path Transaction> Transaction Line> Transaction Accounting Line. This join path prevents increased data duplication which would occur if you used the join path Transaction> Transaction Accounting Line.
-
If you have the Multi-Book Accounting feature enabled in your account and you join the transaction accounting line record type in a dataset, data duplication can be increased. This is because each transaction accounting line stores data for each accounting book in your NetSuite account. To limit this, consider creating a criteria filter using the accounting book field from the transaction accounting line record type, so that data from only one accounting book is included.
-
The transaction accounting line record type stores monetary values in the base currency of the subsidiary assigned to the transaction. The amount fields in this record type can only be consolidated and are typically used for accounting purposes, such as calculating financial results for your entire company, including all subsidiaries.
The transaction and transaction line record types store monetary values in the currency of the transaction and can only be converted. These amount fields are typically used for operational purposes, such as analyzing total sales of a specific item. For more information about currency consolidation and conversion in Workbook, see Currency in Datasets and Workbooks.
The following video demonstrates how data can be impacted by joining the transaction line and transaction accounting line record types in a dataset.