CODEWITHSUNDEEP
casefieldformulanetsuitecase-when
Radoslav Penov
Radoslav Penov

Reputation: 47

CASE WHEN Formula - NetSuite

I'm trying to get results for a field with Formula (Text). The idea is - if the subsidiary = A, then show field 1. If subsidiary = B, then show field B. (please see the formula below). For some reason, I didn't get any results. Both fields are transaction column fields. When I'm testing only the fields (without formula - just the name of the field) they work.

Type of the Saved Search is: Transaction Main Line is: False

Case when {subsidiary}='A' THEN {field1} when {subsidiary}='B' THEN {fieild2} END

For the records which belong to the Subsidiary A field 2 is empty and for the Subsidiary B - field 1 is empty accordingly. Therefore I would like if the Subsidiary is A to populate field 1 and if it's B to populate filed 2. Can you please advise how to achieve this?

Thank you in advance.

Upvotes: 0

Views: 3889

Answers (2)

vVinceth
vVinceth

Reputation: 915

Another way and making it more accurate, is to use {subsidiary.id}. The ID is the internal id of the subsidiary. I hate dealing with Text so I normally use internal id when needed, specially in CASE WHEN of a saved search.

If someone changes the name of the subsidiary or any other list field then the saved search will break.

Upvotes: 0

Mike Robbins
Mike Robbins

Reputation: 3287

The {subsidiary} field is expecting the entire parent-child hierarchy for a match, so 'Honeycomb Holding Inc. : Honeycomb Mfg.'. You can use `{subsidiarynohierarchy} to match just the base subsidiary name (i.e, Honeycomb Mfg.) the transaction is in. This formula should work:

case 
    when {subsidiarynohierarchy} = 'Honeycomb Mfg.' then {field1}  
    when {subsidiarynohierarchy} = 'test sub' then {field2}  
end

If no ELSE clause is provided and no match is found, it will return an empty result.

For the records which belong to the Subsidiary A field 2 is empty and for the Subsidiary B - field 1 is empty accordingly.

If, as you mentioned, these fields are ONLY populated for the given subsidiary, then you could simplify the formula to this:

coalesce({field1}, {field2})

This will return the first non-null value so if subsidiary A has field1 populated, it will return field1. If subsidiary B has field1 empty and field2 is populated, it will return field2.

Upvotes: 2

Related Questions