-
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 | Custom Field -Show the latest date it was set to Pending/Approved
In NetSuite, some companies may need to add a Custom Field to their transactions to know when it was Approved or set to Pending. This article will guide you on how to achieve this using a Saved Search and a Custom Field. In this scenario, we'll be using a Custom Field as the Status field: Saved Search: Criteria subtab:…
-
NSC | Workflow - Set the value of the Display in Website field
In NetSuite, some companies may need to set the Display in Website field using a Workflow. However, users may notice that this field isn't available in the Set Field Value action. This article explains how to resolve this by selecting the correct field. Set Field Value action: On the Parameters section, set the fields…
-
NSC | Custom Field: Display the Timestamp of the Creation of a Transaction
Display the timestamp when the transaction form is opened and when it is saved. This is to show how long it took to create the transaction. Navigate to Customization > Lists, Records, & Fields > Transaction Body Fields > New 2. Label: Enter Time 3. Type: Select Time Of Day 4. Store Value: Remove Checkmark 5.…
-
NSC | Custom Field - Add Last Modified Date field to the Locations record
In NetSuite, some companies may need to add a Custom Field to the Location record that displays the date it was last edited. This article will guide you on how to achieve this using a Saved Search and a Custom Field. Create the Saved Search: Navigate to Reports > Saved Searches > All Saved Searches > New On the Saved…
-
Revenue arrangement
I am using kit items wherein FV is utilized for it's components. The components FV has a formula wherein it's 0.5*discounted sales amount of the Kit item. Individual SO and Return authorization is created for Kit item and the allocation works fine, but when arrangement of SO and Return is merged into single arrangement…
-
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 | SuiteAnalytics Connect: RESOLVE: Currency Table is Missing
Scenario: A NetSuite User wants to use the Currency Table to cross reference with other tables. However, the Currency table is not listed among the tables available in their integration. Solution: One of the possible reasons this could happen is if the SuiteAnalytics Integration is using a custom role to connect to…
-
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…
-
NSC | SuiteQL Query: How to Get the Custom Form ID and Description from the Transaction Table
Scenario: A NetSuite User aims to enhance the verification process by querying Form used in transaction Records and the corresponding form description. This would simplify the task of reviewing and confirming the correct form usage, ensuring a more accurate and streamlined approach. Solution: The query provided below shows…
-
NSC | SuiteQL: Utilizing BUILTIN.CURRENCY_CONVERT for Reports and Data Analytics
BUILTIN.CURRENCY_CONVERT enables accurate financial analysis, reporting, and decision-making in multinational business environments. These queries demonstrate how it can be applied to various real-world scenarios, such as to analyze sales performance across different regions in their local currencies and then convert the…
-
NSC | Workflow - Remove Email action
In our previous article, NSC | Custom Form - Remove Email action, we learned how to hide the Email action using a Custom Form. However, some companies may prefer to use a single Custom Form instead of maintaining multiple forms per role. This article will guide you through the process of accomplishing this using a…
-
NSC | Custom Form - Remove Email action
In NetSuite, some companies may want to remove the Email option from their transaction records. This article will guide you through the process of doing so using Custom Forms. Customize your Custom Form: On the top-right corner of your Invoice record, navigate to Customize > Customize Form On the Custom Transaction…
-
NSC | SuiteQL: How to Fetch a Transaction from a Specific Date
Scenario: A NetSuite user wants to design a SuiteQL query that effectively filters Vendor Credit transactions occurring on a specific date, ensuring precise data retrieval for financial analysis. Solution: The following query can be ran to extract Vendor Credits from a specific date. NOTE: Replace the first parameter on…
-
NSC | SuiteQL: How to Get the List of Existing Transaction Types Being Used in a NetSuite Account
Scenario: A NetSuite Client wanted to filter transactions based on their types via SuiteQL, that will later be a basis for their other business processes. Solution: Sample query that can be used as a reference: SELECT DISTINCT type, BUILTIN.DF(type) as type_description FROM transaction; The sample code described herein is…
-
NSC | SuiteQL Query: How to Get the Sales Order with Job/Project Details via Left Join
Some NetSuite Customers aim to develop an efficient SQL query that combines information from the TRANSACTION and JOB tables to facilitate payroll processing. The goal is to seamlessly merge these tables, allowing the extraction of relevant data required by the payroll team to accurately calculate and disburse employee…
-
Retrieve the Transaction Status Name via ODBC or SuiteQL
When retrieving the Transaction Status via ODBC or SuiteQL, results are displayed as symbols (i.e. A, B, C) instead of "Pending Approval, Billed", etc. This is the sample query that displays status as symbols: SELECT status FROM transaction. In order to get the desired values of the status as their text equivalent, use…
-
NSC | Custom Field - Source values from BOM to Work Order
In NetSuite, some companies require their Work Orders to source a Custom Field from the Bill of Materials. Unfortunately, this is not possible natively. However, a workaround for this would be to use a Saved Search and a Custom Field. In this article, we'll guide you on how to do this. Create a Transaction Saved Search:…
-
NSC | Purchase Order Workflow - Purchase Approver & Purchase Approval Limit fields are not available
When creating a Purchase Order Workflow, you might encounter wherein the Purchase Approver & Purchase Approval Limit fields are not available in the Visual Builder of your Workflow. In this article, we'll show you what features should be enabled: Approval Routing & Purchase Requests on Employees: Navigate to Setup >…
-
revenue arrangement merger
Prospective merge management does not support mapping revenue recognition fields. You cannot map custom and standard fields from source records to transaction line fields in prospective merge revenue arrangements. Need more clarity on above statement, I have created mapping as below wherein Revenue Rec.Start Date and…
-
add payment link on statement pdf
Need to add unique payment link per invoice on the advanced statement pdf template. This is my current code: <td align="right" colspan="4"><a style="color: blue;" href="${line.paymentlink}">Click To Pay</a></td>
-
NSC | SuiteAnalytics Connect: NetSuite.com to NetSuite2.com Schema > How to get Opportunity Records
The NetSuite.com data source is no longer supported in 2025.1 and will be removed in 2026.1. You should use the NetSuite2.com data source when working with SuiteAnalytics Connect to avoid any disruptions. For more information, see SuiteAnalytics Connect NetSuite.com Data Source End of Support. On NetSuite.com Schema:…
-
NSC | Custom Case Form: Display the Item Manufacturer Part Number
The MPN of the selected Item on the case record needs to be tracked. To achieve this, a custom CRM field can be used to display the MPN of Item selected in a Case record. 1. Navigate to Customization > Lists, Records, & Fields > CRM Fields > New 2. Custom CRM Field: Label: Enter Total in Words Type: Select Free-Form Text…
-
NSC | Custom Sales Order Form: Show Picked, Packed, Fulfilled and Invoice Fields
There is a requirement to display the Picked, Packed, Shipped and Invoiced line fields on Sales Order Custom Form despite when Advanced Shipping feature is enabled. Navigate to Customization > Forms > Transaction Forms 2. Preferred Custom Sales Order Transaction Form: Click Edit 3. Click Sublist Fields Tab 4. Show checkbox…
-
NSC | Advanced PDF/HTML Template: Display Quantity as Percentage
There is a requirement to display the quantity as a percentage value on the Advanced PDF/HTML Template. Navigate to Customization > Forms > Advanced PDF/HTML Templates 2. Preferred Advanced PDF/HTML Template: Click Edit or Customize 3. Top right corner: Toggle Source Code 4. Locate code that prints the Item Quantity 5.…
-
NSC | FAQ: Available Fields on Invoice Sales Order Page
This article describes the behavior of Custom Fields as additional column fields on the Invoice Sales Orders Page. The Invoice Sales Orders Page retrieves values from Sales Order records. However, when clicking on the Customize button, on the Additional Columns subtab, the list of fields includes all standard Sales Order…
-
NSC | Custom Item Form: Show Display Primary Units
There is a requirement to display the Primary Stock Unit, Primary Sale Unit, and Primary Consumption Unit in a Custom Item Form but these fields are not available in the Fields tab of the Custom Item Form. Navigate to Customization > Forms > Entry Forms 2. Preferred Item Form: Click Customize or Edit 3.Locate Primary Units…