How do I go through a list of all values of a multi-select in a Netsuite saved search and get a list of the values that haven't been selected?

I'm writing a saved search in Netsuite that requires that I output the following:

Rather than just a list of the selected values from a multi-select, I need to output the full list and prepend those values that aren't selected with a minus (-) symbol.

The use case of this is to send this information to a channel management system, so if the items are not selected in the multi-select, rather than not being listed at all, they need to be a -marketplace_name (e.g. the finished string would look like the following:

"Amazon AU Marketplace, -Bunnings, -Catch"

This essentially translates to mean:

List on Amazon AU Marketplace
Remove from Bunnings
Remove from Catch

I'd like to ask whether anyone's aware of how to get a list of all values that haven't been selected, rather than those that are (the default behaviour) of a multi-select list, or alternately, how to get a list of all values, whether selected or not?

The formula I'm using at present is a simple Formula(Text) field to retrieve the actual selected values is as follows:

{custitem_pim_enabled_marketplaces_aus}

Following is a screenshot of what I'm trying to achieve:

further details of what I'm trying to achieve

Many thanks and hopefully there's some way to get this list simply without resorting to string explodes via SUBSTR(), etc.

Given that at present I only have a list of the "selected" items on the multi-select, thus far I've attempted something similar to the following, though given I'm essentially only able to cycle through the list of selected item, not the full list of items, this strategy isn't going to work.

CASE WHEN {custitem_pim_enabled_marketplaces_aus} NOT LIKE ‘%Amazon AU Marketplace%’ THEN ‘-Amazon AU Marketplace’ ELSE END

Any ideas would be appreciated.

Regards,

Christian

Solution

If you know the full list of possible values beforehand, and it's a manageable size, you can specify that list and use REPLACE and REGEXP_REPLACE to modify how it's shown. In the following example, I start with a full list with hyphens prepended (1). I then use REPLACE to generate a regex pattern (it replaces the commas which NetSuite returns with pipes (|) to provide "alternation" between the various possible values (2). It also wraps the pattern in parentheses to allow for the backreference \1 )

If a match is found I replace it with the same value, but with another hyphen prepended (3). I then replace any double hyphens with an empty string (4).

I'm using a custom field from my account which contains days of the week, so be sure to replace the field reference and values with the relevant data from your account. Also, you may need to remove the comments if you paste it into NetSuite

REPLACE(                                             --4
  REGEXP_REPLACE(                                    --3
    '-Friday,-Monday,-Thursday,-Tuesday,-Wednesday', --1 (replace with your values)
    '(' || REPLACE(                                  --2
      {custrecord_sg_day_of_the_week},               --2 (replace with your custom field)
      ',', '|'                                       --2
    ) || ')',                                        --2
    '-\1'                                            --3
  ), 
  '--',                                              --4
  ''                                                 --4
)

EDIT: To handle the scenario where some records have no value selected for that field, you can use a CASE statement to test for that and simply return the initial string containing all values with the prepended hyphen:

CASE WHEN {custrecord_sg_day_of_the_week} IS NULL THEN '-Monday, -Tuesday, -Wednesday, -Thursday, -Friday' ELSE REPLACE(
  REGEXP_REPLACE(
    '-Monday, -Tuesday, -Wednesday, -Thursday, -Friday', 
    '(' || REPLACE(
      {custrecord_sg_day_of_the_week}, 
      ',', '|'
    ) || ')', 
    '-\1'
  ), 
  '--', 
  ''
) END