In NetSuite saved searches, you can combine `DECODE` and `CASE WHEN` to handle both simple equality checks and complex conditional logic within a single formula column. This approach is especially useful when: * You need to treat different transaction types differently. * Some transaction types require additional conditional checks (dates, statuses, quantities, etc.). * You… Continue reading Using DECODE with Nested CASE WHEN in NetSuite Saved Search Formulas
Tag: sql formula
SQL Fornula to check if one string or ID contains in a concat string using summary FUnctions
CASE WHEN INSTR( TO_CHAR(REPLACE(NS_CONCAT(DISTINCT {custrecord_jj_transaction_number_ahap729.salesteammember.id}), ‘,’, ‘<br>’)), TO_CHAR(MAX({custrecord_jj_sales_person_ahap_729.id})) ) > 0 THEN ‘Y’ ELSE ‘N’ END add this in the formula text Maximum
Creating Conditional Hyperlinks in NetSuite Saved Searches
To display a value (e.g., name, code, ID) as a clickable hyperlink in a NetSuite Saved Search only if a valid URL is present in a related field. If no valid URL exists, the value is shown as plain text instead. Use Case Useful in any Saved Search where: * You want to link to… Continue reading Creating Conditional Hyperlinks in NetSuite Saved Searches
Saved Search – Count the number of occurrences in a text field
Requirement: If anyone has come up with a formula in saves searches that identifies the number of occurrences a number/word appears in a sting of a field? I would like to know how many times “529” appears in this field Answer: You can try this formula:- (LENGTH({yourtextfieldid}) -LENGTH(REPLACE({yourtextfieldid}, ‘529’, ”)))/LENGTH(‘529’)
SQL formula Add dynamic number of days to a static date or today date excluding non working days Saturday or Sunday
TO_CHAR( (CASE WHEN ROUND(NVL({timeremaining}, 0) / 8) <= 0 THEN {today} ELSE {today} + FLOOR((ROUND(NVL({timeremaining}, 0) / 8) + CASE WHEN TO_CHAR({today}, ‘D’) IN (‘7’, ‘1’) THEN 1 ELSE 0 END) / 5) * 7 + MOD(ROUND(NVL({timeremaining}, 0) / 8) + CASE WHEN TO_CHAR({today}, ‘D’) IN (‘7’, ‘1’) THEN 1 ELSE 0 END, 5)+ CASE WHEN TO_CHAR({today} +… Continue reading SQL formula Add dynamic number of days to a static date or today date excluding non working days Saturday or Sunday