Why Aren't Null Values Considered When Filtering 'Excludes' in a Multi-Picklist field?

Related products: None

If I filter on a multipicklist field in my program by doing ‘Excludes’ and select the values that I want to be excluded from the query, you’d think that would bring in any account that doesn’t have the excluded value.

But after troubleshooting why certain accounts weren’t meeting my criteria I realized that it was due to their field value being null. Essentially if you are filtering a multipicklist field, it limits the audience to only the accounts that have a value in that field and any account with a null is not considered in the filter logic. 

You’d think by definition an account with a null value meets the criteria because it does not have the excluded value. This is not at all intuitive to a user.

 

Adding the screenshot from similar idea and merging the Idea

Hi.

Currently, we don’t have an option to choose/select the “Include Null Values” for the Multi-Select Picklist fields in Rules and/or JO Query Builder.

When ever we drag the multi select picklist field in to a filter section (Ex: in Rules) we have to choose either Includes or Excludes option only and unable to select the “Include Null Values” as well. Like how we have this option for Picklist Data type fields.

Screenshots for the same:

 

Due to unavailable of this option we are unable to get the Null value rec

Agree with @kelly.  You can work around it, but it would be simpler if the workaround wasn’t needed. 


Agree with @kelly.  You can work around it, but it would be simpler if the workaround wasn’t needed. 

This!


100% 


I am finding that I can’t even really work around this issue! In Rules Engine, I tried to use a filter where my field excluded all available values, meaning that in theory NULL records would appear…. but they still don’t 😭 I have over 4500 records that need updating and I can’t use Rules Engine to do it so now I’m back to the drawing board 🙃


Same. Currently in Rules engine, I can’t seem to get a decent workaround. Are there plans to update this in the future to allow filtering on null multi-select fields? 


@anirbandutta may we get this converted to an idea? Thx!


@darkknight would this Idea be the same?

 


@darkknight would this Idea be the same?

 

@anirbandutta  I don’t think that is the same, that post is asking for the null box so you can include or exclude null values. 

 

This post is describing how the system does not consider null values at all in the include/exclude filter. For example if I want to exclude values B, C, and D, theoretically I should get back A and Null, but I only get back A. 


First, I’m 100% behind native functionality to query for records where a multi-select dropdown has no values selected.

Second, until that’s available, a potential work-around for those needing it:

  1. On the object in question, create a new field with Type of String.
  2. In the Rules Engine, build a rule to regularly populate/update the new String field with the values of the multi-select dropdown.
    • In the rules engine, you can map a multi-select dropdown to a String field, and the values in the String field will be a semi-colon-separated concatenation of the multi-select dropdown values.
  3. Now you can use the new String field in your queries, where you can then use filters that incorporate the null values.

@darkknight would this Idea be the same?

 

@anirbandutta  I don’t think that is the same, that post is asking for the null box so you can include or exclude null values. 

 

This post is describing how the system does not consider null values at all in the include/exclude filter. For example if I want to exclude values B, C, and D, theoretically I should get back A and Null, but I only get back A. 

Yea, this is a massive bummer for any data type in Gainsight to be honest. Even for a boolean field where you say “does not equal yes/true” you’d think your query would find anything matching that, which would be No/false and null, but instead there’s an extra step and is a pretty big gotcha.


@anirbandutta may we get this converted to an idea? Thx!

Thanks everyone, converted to an Idea to vote.

@PavanCh on your radar.


Repeatedly steals a day from me every time it comes up. Please do something to tame the null menace!


I’ve not had problems with this myself but I have with boolean fields and it’s completely counter-intuitive

“Does not equal” filters should always return everything that isn’t equal to the value(s) you are explicitly trying to exclude — including nulls

This should be implemented in an intuitive, logical and consistent way across the whole platform


I would love to be able to +1 a post more than once! 
I had to stick a note on my screen that says ADD OR NULL in caps and highlighted. I fell for the null trap more times that I can count


Thanks everyone, converted to an Idea to vote.

@PavanCh on your radar.

@Bhawya could you pl let us know about the latest?


Hi All,

I understand how limiting this can be. I also believe that we have solved this problem in Reporting part of Gainsight. Posting what we have in Reporting to confirm if this is more elegant way to solve the problem:

 

We have plans to enhance the filtering capability across Gainsight, for this functionality, I want to ascertain if the above works. 


@rakesh - would that require two filters or does the Industry dropdown allow for selecting values and checking the null checkbox? So Industry has A and B and the Include null values can be checked as well?


@rakesh - would that require two filters or does the Industry dropdown allow for selecting values and checking the null checkbox? So Industry has A and B and the Include null values can be checked as well?

Looks like it allows for selecting values either as in/not in, as well as checking the box to include nulls should you so desire. At least in reports.


Perhaps, the most annoying part of this is the inconsistency with which GS handles multi-select fields when you query Salesforce objects. In the filter options for a multi-select field on a SF object, you can have field ‘in’, ‘not in’, ‘is null’, ‘is not null’. The same multi-select field on a GS object allows only and ‘include’, ‘exclude’ filter. 

The 2nd most annoying part is the inconsistency with which GS treats the same field across the different features.

For the same field in company:

In DD, I can have the field ‘in’ and ‘not in’.

In Rules, I can have the field ‘include’, exclude’.

In reports, I can have the field ‘in’, ‘not in’, ‘is null’, ‘is not null’. You still need 2 filters to exclude something and to include nulls. At least this is close to how SF querying is handled. 

In a case statement in a rule, the ‘exclude’ filter will capture the null values. 

@rakesh - any plans to fix this across the platform?


Perhaps, the most annoying part of this is the inconsistency with which GS handles multi-select fields when you query Salesforce objects. In the filter options for a multi-select field on a SF object, you can have field ‘in’, ‘not in’, ‘is null’, ‘is not null’. The same multi-select field on a GS object allows only and ‘include’, ‘exclude’ filter. 

The 2nd most annoying part is the inconsistency with which GS treats the same field across the different features.

For the same field in company:

In DD, I can have the field ‘in’ and ‘not in’.

In Rules, I can have the field ‘include’, exclude’.

In reports, I can have the field ‘in’, ‘not in’, ‘is null’, ‘is not null’. You still need 2 filters to exclude something and to include nulls. At least this is close to how SF querying is handled. 

In a case statement in a rule, the ‘exclude’ filter will capture the null values. 

@rakesh - any plans to fix this across the platform?

@jivanova FWIW the Horizon Rules should at least close the gap between DD and current rules in that regard, as rules and DD will be mostly the same in terms of design, meaning your options will be the same for filters. 


Still the inconsistency between rules and reports, which are supposed to be both Horizon, remains. This creates so much tech-debt for admins to clean.


Still the inconsistency between rules and reports, which are supposed to be both Horizon, remains. This creates so much tech-debt for admins to clean.

Agree things are and will still be a bit disjointed in some places, just wanted to highlight an upcoming change and give credit where credit is due :)

The whole ‘null’s thing in general is still a PITA either way


No StatusAcknowledged

Updated idea statusNo StatusAcknowledged

The following idea has been merged into this idea:

All the votes have been transferred into this idea.