Search code examples
caseformulanetsuite

NetSuite System Notes CASE formula returning all notes

I've added a formula(date/time) column to a saved search in NetSuite, to return a system notes' date.

My CASE formula is returning all the system notes row's, and I would like a specific row's date i.e. 'POP Host Int ID' date.

How can I specify the row to return the date from, or remove the rows with no date that are not relevant?

CASE 
WHEN {systemnotes.field} = 'POP Host Int ID' AND {systemnotes.type} = 'Set' 
THEN {systemnotes.date} 
ELSE NULL 
END

It appears that my WHEN logic works to identify the record's system notes do contain an entry for 'POP Host Int ID' but in THEN I'm not specifying which row to get the date from so it returns all rows. And I could be wrong on this part.

Example results enter image description here

Example System Notes for 1 record enter image description here

Thank you for your assistance.

Solution

  • The CASE statement doesn't determine which rows are returned, only what data is returned for that field. On the other hand, the reference to the systemnotes table creates a join that causes each record result to be repeated for every system note entry.

    To avoid this, add {systemnotes.field} = 'POP Host Int ID' and {systemnotes.type} = 'Set' as Filters in the Criteria tab instead of in the WHEN conditions. You can then just add the field under results instead of needing a formula.

    Edit in response to comment below:

    In cases where you need one result per base record (user), but they don't all have valid values from the joined table (system notes), I'd suggest grouping the results by user, and using aggregation functions for all the columns. EG: For the column in question I'm assuming you are getting one valid result and a lot of blanks per user. If you group by user and set the Summarize function to MAX, you should just get one result where the valid value is returned. If no valid value exists from the system notes, you would still get a result from the user and that field will be blank.