Saved Search Formula for Comparing two fields with different data types

When comparing values from different columns in NetSuite (e.g., Vendor Offered Quantity and Request Quantity), you might encounter issues due to datatype mismatches. A common issue is when one of the columns is stored as a string while the other is numeric, leading to incorrect comparisons.

To ensure accurate comparison, you can use the TO_NUMBER or TO_CHAR functions to explicitly convert both columns to the same datatype (numeric or string). This is especially useful when comparing fields like quantities from different sources that may be stored as different datatypes. Additionally, handle NULL values using the NVL function, which replaces NULL with a default value (e.g., 0).

• Use TO_NUMBER: When comparing numeric fields.
• Use TO_CHAR: When comparing string fields or when you need both fields to be in string format for comparison.

Example Formula:

["formulanumeric: CASE 
    WHEN TO_NUMBER({lineuniquekey}) = TO_NUMBER({custcol_jj_linked_requsition.custcol_mhi_ibs_vrfq_key}) 
    AND NVL(TO_NUMBER({custcol_mhi_ibs_entity_qty}), 0) > NVL(TO_NUMBER({custcol_jj_linked_requsition.custcol_mhi_ibs_entity_qty}), 0)
    THEN 1 
    ELSE 0 END", "equalto", "1"]

Alternatively, can use

["formulachar: CASE 
    WHEN TO_CHAR({lineuniquekey}) = TO_CHAR({custcol_jj_linked_requsition.custcol_mhi_ibs_vrfq_key}) 
    AND NVL(TO_NUMBER({custcol_mhi_ibs_entity_qty}), 0) > NVL(TO_NUMBER({custcol_jj_linked_requsition.custcol_mhi_ibs_entity_qty}), 0)
    THEN 1 
    ELSE 0 END", "equalto", "1"]

Explanation:

• TO_NUMBER({field_name}): Converts the field to a numeric value for comparison.
• TO_CHAR({field_name}): Converts the field to a string value for comparison.
• NVL(value, default_value): Replaces NULL values with a default value (in this case, 0).
• CASE WHEN ... THEN 1 ELSE 0 END: This logic checks if the vendor’s offered quantity is greater than the required quantity for the same request key and returns 1 if true, otherwise 0.
• "equalto", "1": The formula filters the results where the condition evaluates to 1 (i.e., when the vendor’s offered quantity is greater than the required quantity).

Key Takeaways:

• For Numeric Comparison: Use TO_NUMBER to ensure both fields are treated as numbers.
• For String Comparison: Use TO_CHAR to treat both fields as strings.
• Handling NULL Values: Use NVL to ensure NULL values are replaced with a default value (e.g., 0) to avoid calculation errors.
• This approach can be applied in any saved search where two fields with different data types need to be compared.