-
NSC | SuiteQL: Join Types: CROSS JOIN
CROSS JOIN is a type of SQL join that combines every row from one table with every row from another table. It is also known as a Cartesian product, named after the mathematician René Descartes, who introduced the concept. The result of a cross join is a dataset that includes all possible combinations of rows between the…
-
NSC | SuiteQL: Custom Data Retrieval with SQL Joins
SuiteQL is a querying language designed for NetSuite, offering users a structured approach to access and analyze data stored within the platform. It provides a robust framework for data retrieval, enabling users to create custom queries. What are JOINs in SuiteQL? JOINs are SQL operations used in SuiteQL to combine and…
-
NSC | SuiteQL Query: Filter Transactions by Last Modified Date
Scenario: A company using SuiteAnalytics Connect wants to be able to filter transactions based on their Last Modified Date field. Solution: Query: select id, lastmodifieddate, tranid from transaction where lastmodifieddate >= to_date('2024-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') The sample code described herein is…
-
NSC | SuiteQL Query: Fetch File URL of an Image Field from the Item Record
Scenario: A user wants to be able to fetch the URL Item Display Image but is unable to do so as the image field type is not supported by SuiteQL. To work around this, a custom field is created along with a workflow that will automatically set the custom field’s value when the Item Display Image field is changed. For more…
-
NSC | SuiteQL Query: How to Get the List of Deleted Records
Scenario: A company using NetSuite wants to be able to find which records were deleted for auditing purposes. Solution: Query: SELECT * FROM deletedRecord; SELECT deleteddate, deletedby, recordid, recordtypeid, type FROM deletedRecord; The sample code described herein is provided on an "as is" basis, without warranty of…
-
NSC | SuiteAnalytics Connect: RESOLVE ERROR: Base table role not found
Scenario: A NetSuite User attempting to query the Role table via SuiteAnalytics Connect encounters an error stating that the table is not found. Solution: Navigate to Setup > Users/Roles > Manage Roles. Click on Edit for the Role used for the integration. Go to Permissions subtab > Setup sublist Add the Bulk Manage Role…
-
NSC | SuiteQL: Using JOIN to Get Currency Name and Exchange Rate from the Currency Table
Scenario: The finance team wants to generate a report that shows all international sales transactions, including the original transaction currency and the corresponding exchange rates used for conversion to the base currency. Solution: The following query retrieves essential details about transactions, including the…
-
NSC | SuiteQL Query: Getting the Internal ID of the Currency Used in a Transaction Field
In NetSuite accounts that operate with multiple currencies, maintaining data integrity is crucial. It is essential to identify the specific currencies associated with transaction amounts to guarantee accurate financial reporting. Here's a sample query that retrieves the internal ID of the currency used in the 'Transaction…
-
NSC | SuiteQL Query: Using BUILT.CF() to Get Transaction Status Key Value Pair
Scenario: A NetSuite user is looking to optimize their query process by implementing a filtering mechanism for transaction records based on specific criteria. They aim to retrieve only the transactions with a particular transaction type and status code. Solution: Instead of using two fields in the WHERE clause, users can…
-
NSC | SuiteQL Query: Using BUILTIN.CURRENCY_CONVERT to Get the Exchange Rate from a Different Date
Understanding historical exchange rates in data analytics for NetSuite is crucial for businesses with international operations as it ensures financial accuracy and compliance. By applying the exchange rate from the transaction date, companies can maintain consistent and reliable financial records, enabling accurate…