CASE WHEN formula not working This formula works by itself C NetSuite Professionals #general

CASE WHEN formula not working? This formula works...

07/20/2023, 12:10 PM

CASE WHEN formula not working? This formula works by itself - (CASE WHEN {type} IN 'Non-inventory Item' THEN NVL({custitem_ev_uk}, 0) - NVL({custitem_bun}, 0) ELSE 0 END) however am getting blanks for the same bit in the formula: CASE WHEN {type} IN 'Non-inventory Item' THEN NVL({custitem_ev_uk}, 0) - NVL({custitem_bun}, 0) ELSE 0 END + ((CASE WHEN {type} IN ('Assembly', 'Inventory Item') AND {inventorylocation} = 'ABC' THEN {locationquantityavailable} ELSE 0 END + CASE WHEN {type} IN ('Assembly', 'Inventory Item') AND {inventorylocation} = 'XYZ' THEN {locationquantityavailable} ELSE 0 END) - (CASE WHEN {inventorylocation} = 'ABC' AND {locationquantityavailable} > 0 THEN 1 ELSE 0 END + CASE WHEN {inventorylocation} = 'XYZ' AND {locationquantityavailable} > 0 THEN 1 ELSE 0 END) * (CASE WHEN (CASE WHEN {type} IN ('Assembly', 'Inventory Item') AND {inventorylocation} = 'ABC' THEN {locationquantityavailable} ELSE 0 END > 0) AND (CASE WHEN {type} IN ('Assembly', 'Inventory Item') AND {inventorylocation} = 'XYZ' THEN {locationquantityavailable} ELSE 0 END > 0) THEN {custitem_buffer} / 2 ELSE {custitem_buffer} END)) Any ideas where's it wrong?

David B

07/20/2023, 10:19 PM

Here it is formatted for anyone interested

Untitled.sql

David B

07/20/2023, 10:22 PM

the

IN

condition/operator works on an array of values or a subquery. See the TechOnTheNet page Try changing it to either

{type} IN ('Non-inventory Item')

or honestly, just use

{type} = 'Non-inventory Item'

David B

07/20/2023, 10:29 PM

Can't tell why you're getting blanks, have you tried logging out all of the fields you're referencing? Some might be null, resulting in a null/blank final value. Also this formula is quite convoluted, what are you trying to achieve?

2 Views

Open in Slack

Previous Next