MONTHS_BETWEEN({today}, MIN(CASE WHEN {transaction.trandate} BETWEEN ADD_MONTHS({today}, -12) AND {today} THEN {transaction.trandate} END)) Avg Qty Sold Per Month SUM(ABS( CASE WHEN {transaction.type} IN (‘Item Fulfillment’, ‘Assembly Build’) AND {transaction.trandate} BETWEEN ADD_MONTHS({today}, -12) AND {today} THEN {transaction.quantity} ELSE 0 END )) / NULLIF(MONTHS_BETWEEN({today}, MIN(CASE WHEN {transaction.trandate} BETWEEN ADD_MONTHS({today}, -12) AND {today} THEN {transaction.trandate} END)), 0)
Tag: Formula
Using Custom Fields to Access Locked or Bundle Fields in Templates
When working with transactions, you might encounter a situation where certain fields hold values from bundles or are otherwise locked. These fields cannot be directly referenced or used in templates, creating challenges when you need to display or utilize their values in documents, emails, or reports. To work around this limitation, you can create custom… Continue reading Using Custom Fields to Access Locked or Bundle Fields in Templates
Using Formula to Calculate Item Price Based on Units in NetSuite Saved Search
Introduction NetSuite’s saved searches allow users to create custom reports, perform advanced filtering, and display specific data using formulas. One common use case is calculating the price of an item based on its unit of measurement, particularly when items are sold in different units (e.g., boxes, cases, or individual units). This article will focus on… Continue reading Using Formula to Calculate Item Price Based on Units in NetSuite Saved Search
Calculate the difference between two fields in a saved search in case a field has null value.
Formula NVL({field1}, ‘0’)-NVL({field2}, ‘0’) Function = NVL Syntax = NVL(expr1, expr2) Short Description = lets you replace null with the second parameter. Example = NVL({quantity},’0′)
Usage of COALESCE in Formula Functions
Scenario: If the Record’s Billing City is not NULL it will return the Billing City > If the Billing City is NULL and Billing Country is NOT NULL it will return the Billing Country > If the Billing Country is NULL and Billing Phone is NOT NULL it will return Billing Phone> If the Billing… Continue reading Usage of COALESCE in Formula Functions
Setting the default value for a transaction line field using formula
Create a transaction line field. Customization > List, Records & Fields > Transaction Line Fields > New Choose the required field type. To define formula fields, click the Validation & Defaulting subtab of the custom field. Check the Formula box. To dynamically recalculate a formula, clear the Store Value box, and if needed, use the… Continue reading Setting the default value for a transaction line field using formula
Formula for Finding the back ordered quantity in a transaction Saved search.
Scenario: In a transaction saved search if we want to get the number of back ordered quantity in the search, we can use this formula as formula text in the results column. Formula {quantity}-nvl({quantityshiprecv},0)-nvl({quantitycommitted},0)
Formula to find the day difference between two Dates
Scenario: In the vendor record, we have a status called ‘Charity Status’. While creating a vendor record, the status is set to ‘Initial contact’. The status of the vendor will converted to ‘Approved – Active’ after a time period. So the requirement is to calculate the number of days from the ‘Initial contact’ date/ create… Continue reading Formula to find the day difference between two Dates
To find the time difference and date difference
To find the time difference from a date, use the following formula in the script The formula to calculate the days
Formula to find Transfer price of an item.
>>Get the price and exchange rate >>To find transfer price use the formula, (parseFloat(price) / parseFloat(exchangerate)).toFixed(2).