Creating Conditional Criteria in NetSuite Saved Search for Spiff Calculation

In NetSuite, Saved Searches are a powerful tool for extracting and analyzing data based on specific criteria. By leveraging advanced formulas, you can customize your saved search results to meet specific business requirements. In this article, we will explore how to set up a complex conditional formula in a NetSuite Saved Search to calculate spiff amounts based on multiple conditions, including payment status, date ranges, and item-specific attributes.

The formula presented below is designed to calculate spiff amounts based on certain conditions that involve various date ranges, payment status, and item classifications.

Formula Breakdown

The formula provided utilizes SQL-style CASE statements to evaluate different conditions and calculate the appropriate spiff amount. Below is the full formula:

sql

Copy code
CASE 
    WHEN ({status} = 'Paid In Full' AND {closedate} BETWEEN TO_DATE('2022-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-09-30', 'YYYY-MM-DD') AND {closedate} - {trandate} < 47)
    THEN
        CASE 
            WHEN ({item} IS NOT NULL AND {class} IS NOT NULL) 
            THEN 
                NVL({class.custrecord_jj_spiff_percentage_val} * {amount}, {class.custrecord_jj_spiff_dollar_val} * {quantity})
            ELSE 0 
        END
    WHEN ({status} = 'Paid In Full' AND {closedate} > TO_DATE('2023-09-30', 'YYYY-MM-DD'))
    THEN
        CASE 
            WHEN ({item} IS NOT NULL AND {class} IS NOT NULL)
            THEN 
                NVL({class.custrecord_jj_spiff_percentage_val} * {amount}, {class.custrecord_jj_spiff_dollar_val} * {quantity})
            ELSE 0
        END
    WHEN ({status} = 'Paid In Full' AND {custcol_jj_spiff_rate_store} IS NOT NULL AND {createddate} >= TO_DATE('2025-01-01', 'YYYY-MM-DD'))
    THEN
        CASE 
            WHEN INSTR({custcol_jj_spiff_rate_store}, '%') > 0 
            THEN 
                (TO_NUMBER(REGEXP_REPLACE({custcol_jj_spiff_rate_store}, '[^0-9.]', '')) / 100) * {amount}
            ELSE 
                TO_NUMBER({custcol_jj_spiff_rate_store}) * {quantity}
        END
    ELSE 0
END

1. First Condition: Payment Status and Date Range

The formula first checks if the status of the transaction is “Paid In Full” and if the closedate falls between January 1, 2022, and September 30, 2023. Additionally, it ensures that the difference between the closedate and the trandate (transaction date) is less than 47 days. If all conditions are met, it calculates the spiff amount as follows:

• If both the item and class are specified, it calculates the spiff based on either a percentage of the amount (custrecord_jj_spiff_percentage_val) or a dollar value per quantity (custrecord_jj_spiff_dollar_val).
• If either the item or class is missing, the result is set to 0.

2. Second Condition: Payment Status and Date After September 30, 2023

The second WHEN condition handles cases where the status is still “Paid In Full,” but the closedate is later than September 30, 2023. The same spiff calculation logic is applied as in the first condition.

3. Third Condition: Custom Spiff Rate After 2025

The third condition checks for situations where the status is “Paid In Full,” the custcol_jj_spiff_rate_store field is not null, and the createddate is after January 1, 2025. It evaluates whether the spiff rate in custcol_jj_spiff_rate_store contains a percentage sign (%):

• If the spiff rate is a percentage (identified by the presence of %), it calculates the spiff as a percentage of the amount.
• If the rate is a fixed dollar value, it calculates the spiff based on the rate multiplied by the quantity.

4. Default Else Case

If none of the conditions match, the formula defaults to a value of 0.

Explanation of Key Functions Used

• CASE Statement: This is a conditional logic statement used to check multiple conditions and return values based on whether those conditions are true or false.
• NVL Function: This function is used to return the first non-null value from its arguments. In this formula, it’s used to calculate the spiff based on either a percentage or dollar value.
• INSTR Function: This function checks if a string contains a certain substring (in this case, the percentage sign %).
• REGEXP_REPLACE: This function is used to remove all non-numeric characters from the custcol_jj_spiff_rate_store field, leaving only the numeric value. This is useful for extracting numerical values from a string like “15%” or “5.00”.

Best Practices for Using This Formula in Saved Search

1. Ensure Accurate Field Names: Double-check that the field names (e.g., {class.custrecord_jj_spiff_percentage_val}) exist in your NetSuite environment and are properly referenced in your Saved Search.
2. Test the Formula: Always test the formula in a non-production environment to ensure that it returns the expected results.
3. Optimize Performance: Complex formulas can impact performance, especially when dealing with large datasets. Consider indexing critical fields and avoiding overly nested functions where possible.

Conclusion

This complex formula is a powerful tool for calculating spiff amounts in a NetSuite Saved Search based on varying criteria, such as payment status, date ranges, and item classifications. By using SQL-style CASE statements and functions like NVL, INSTR, and REGEXP_REPLACE, you can create highly customizable and efficient formulas to meet your business requirements.