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 how to use a CASE formula in a saved search to calculate an item’s price dynamically based on whether a unit of measurement is defined. This ensures that items sold in different units have their prices calculated correctly.

Purpose of the Formula

When managing products sold in various units, pricing can get complicated. Some items may be sold per unit, while others are sold in bulk (e.g., a case of 12). In such scenarios, simply displaying the rate in a saved search may not give accurate pricing. Therefore, a formula is needed to:

  • Calculate the price when units are specified (e.g., multiplying the rate by the unit quantity).
  • Use the rate as-is if no unit of measurement is defined.

This formula uses NetSuite’s SQL-like syntax to handle both situations.

The Formula

Here’s the complete SQL CASE formula for calculating the price based on whether units are present:

CASE 
  WHEN TO_NUMBER({unit}) IS NULL THEN {rate} * 1 
  ELSE {rate} * TO_NUMBER({unit}) 
END

Breaking Down the Formula

  1. CASE Statement: The CASE statement works similarly to an IF statement, allowing conditional logic to be applied. It evaluates a condition and returns a value based on the result.
  2. TO_NUMBER({unit}): The TO_NUMBER function converts the unit value into a number. This ensures that the system can handle the unit of measurement as a numeric value for multiplication. It’s important to convert the unit field into a number because it might be stored as text in NetSuite.
  3. WHEN TO_NUMBER({unit}) IS NULL THEN {rate} * 1: This condition checks whether the unit field is null or empty (i.e., no units are defined). If the unit is null, it multiplies the rate by 1, which effectively leaves the rate unchanged. This scenario applies when the item is sold as an individual unit (e.g., 1 piece).
  4. ELSE {rate} * TO_NUMBER({unit}): If the unit field contains a value (e.g., a case of 12), the formula multiplies the rate by the number in the unit field. This calculates the total price for the item based on the number of units.
  5. END: The END statement closes the CASE expression.

Example Scenarios

Here’s how the formula works in practice:

  1. Scenario 1: No Unit Specified
  • Rate: $10
  • Unit: NULL (i.e., no specific unit defined)
  • Result: $10 * 1 = $10
  1. In this case, the item is sold as an individual unit, and the rate remains the same.
  2. Scenario 2: Units Specified
  • Rate: $10
  • Unit: 12 (e.g., sold by the case, with each case containing 12 units)
  • Result: $10 * 12 = $120
  1. Here, the total price is calculated by multiplying the rate by the number of units (e.g., 12 units in a case).

Where to Use This Formula

You can use this formula in any saved search where item prices need to be calculated dynamically based on units. This could include:

  • Sales Orders: When generating reports to calculate the total price of items ordered in various quantities and units.
  • Purchase Orders: For calculating total purchase costs when ordering in bulk.
  • Item Pricing Reports: For inventory management, where pricing by unit is critical to understanding the value of stock.
  • Invoice Calculations: Automatically calculating and displaying the correct price on customer invoices when units differ.

How to Implement the Formula in a Saved Search

Step-by-Step Guide

  1. Go to Reports → Saved Searches → All Saved Searches.
  2. Click New to create a new saved search, or edit an existing one.
  3. Choose the record type for the saved search (e.g., Transaction or Item).
  4. In the Results tab, choose the fields you want to display (e.g., Item Name, Rate, Unit, etc.).
  5. Click on Formula (Numeric) in the Results tab to add the formula field.
  6. Enter the formula:
  7. Optionally, add filters in the Criteria tab to narrow down the search (e.g., specific customers, items, or dates).
  8. Save the saved search and run it to see the calculated prices.

Benefits of Using This Formula

  1. Accuracy: The formula ensures that the correct price is calculated regardless of whether the item is sold per unit or in bulk.
  2. Efficiency: Instead of manually adjusting for units, this dynamic calculation eliminates the need for manual recalculations.
  3. Flexibility: You can easily modify the formula to account for additional conditions, such as applying discounts based on quantity or units sold.
  4. Automation: By embedding this formula in a saved search, you can automate price calculations across multiple reports and ensure consistent pricing for all transactions.

Conclusion

Calculating item prices dynamically based on units is a common requirement in businesses dealing with different packaging or sales units. This simple yet powerful CASE formula in NetSuite saved searches enables accurate and efficient pricing calculations, ensuring your reports are always precise and up to date.

By integrating this formula into your NetSuite saved searches, you can handle a wide range of pricing scenarios, reduce manual effort, and ensure that your pricing strategy reflects the correct values for different units. Whether you’re managing sales, purchases, or inventory, this formula will help streamline your processes and improve accuracy in reporting.

Leave a comment

Your email address will not be published. Required fields are marked *