Purpose of this query
This SuiteQL statement counts transaction accounting lines by accounting book and subsidiary. It joins TransactionAccountingLine to TransactionLine to ensure each accounting entry is tied to a valid transaction line and subsidiary. The result shows the internal IDs and display names for both accounting book and subsidiary, along with the total transaction line count. This report is useful for multi-book environments to validate configuration, measure adoption of secondary books, estimate data volume for reporting, and confirm that transactions are being recorded across expected subsidiaries and books.
SuiteQL
SELECT
tal.accountingbook AS accountingbook_id,
BUILTIN.DF(tal.accountingbook) AS accountingbook_name,
tl.subsidiary AS subsidiary_id,
BUILTIN.DF(tl.subsidiary) AS subsidiary_name,
COUNT(*) AS transaction_line_count
FROM
TransactionAccountingLine tal
JOIN
TransactionLine tl
ON tl.transaction = tal.transaction
AND tl.id = tal.transactionline
WHERE
tal.accountingbook IS NOT NULL
AND tl.subsidiary IS NOT NULL
GROUP BY
tal.accountingbook,
BUILTIN.DF(tal.accountingbook),
tl.subsidiary,
BUILTIN.DF(tl.subsidiary)
ORDER BY
subsidiary_name,
accountingbook_name;
Sample Output
How the query works
Joining accounting entries to transaction lines
The query links TransactionAccountingLine to TransactionLine using both transaction ID and transaction line ID. This ensures accurate alignment between accounting entries and their related subsidiary.
Grouping by accounting book and subsidiary
The GROUP BY clause aggregates results by accounting book and subsidiary combination, providing a breakdown of activity volume.
Displaying readable names
BUILTIN.DF is used to convert internal IDs into human-readable accounting book and subsidiary names for easier reporting.
Counting transaction lines
COUNT(*) measures the number of accounting lines recorded for each book and subsidiary pair. This provides insight into transaction volume distribution.
Why this report is useful
• Multi-book validation: confirm that transactions are posting to the expected accounting books.
• Data volume planning: understand transaction counts for performance tuning or reporting strategy.
• Audit preparation: demonstrate activity levels per subsidiary and accounting framework.
• Configuration review: identify subsidiaries that may not be using secondary books as expected.
• Environment comparison: compare sandbox and production transaction distributions.
Customization notes
• Add date filtering: join to Transaction and filter by trandate to analyze a specific period.
• Restrict to primary book only: add AND tal.accountingbook = 1 if needed.
• Focus on specific subsidiaries: add AND tl.subsidiary = <internal_id>.
• Convert to financial impact: replace COUNT(*) with SUM(tal.amount) to analyze monetary totals instead of volume.
• Add transaction type: join Transaction to break down by type (e.g., SalesOrd, CustInvc).