1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] List filters and empty or null fields

[SOLVED] List filters and empty or null fields

9 years 8 months ago - 9 years 8 months ago #248372 by pasquale1980
[SOLVED] List filters and empty or null fields was created by pasquale1980
Hi, which is the right sql syntax that allows to run the filter also if there are fields empty or null?
Example: (`cb_field1` LIKE '%[cb_field2]%')
If the field2 is empty, the filter does not work because it does not find a value and displays all data.
I tried this:
((`cb_field1` LIKE '%[cb_field2]%') OR `cb_field2` IS NULL)
but it does not work.
Thank you.

Please Log in to join the conversation.

9 years 8 months ago #248374 by krileon
Replied by krileon on topic List filters and empty or null fields
The below should work fine.

( ( `cb_field1` LIKE '%[cb_field2]%' ) OR ( `cb_field1` IS NULL ) OR ( `cb_field1` = '' ) )

The above should check that field 1 contains field 2 of the viewing user or if field 1 is null or if field 1 is empty. If you're wanting it to check if field 1 contains field of the viewing user or that field 2 of the viewing user is empty you could use the below.

( ( `cb_field1` LIKE '%[cb_field2]%' ) OR ( '[cb_field2]' = '' ) )

For more information on SQL please see the below tutorials and documentation.

www.w3schools.com/sql/default.asp
www.w3schools.com/sql/sql_quickref.asp
dev.mysql.com/doc/refman/5.6/en/index.html

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

9 years 8 months ago #248380 by pasquale1980
Replied by pasquale1980 on topic List filters and empty or null fields
Does not work, still returns all values.

Please Log in to join the conversation.

9 years 8 months ago #248393 by pasquale1980
Replied by pasquale1980 on topic List filters and empty or null fields
Seen that the SQL string for reading empty fields does not function, can be a powerful way to use CB Auto Action and in phase of subscription with the method UPDATE populate fields with symbol "-", that fills the field and at the same time represents an empty field for the user?
Thank you.

Please Log in to join the conversation.

9 years 8 months ago #248394 by pasquale1980
Replied by pasquale1980 on topic List filters and empty or null fields
Sorry, I noticed that it is not a good option because complete the percentage of the user profile.

Please Log in to join the conversation.

9 years 8 months ago #248428 by krileon
Replied by krileon on topic List filters and empty or null fields
What doesn't work? The first or second one? The first one should work fine. The second one is a bit weird, but should work (it's strange to compare empty string to empty string in SQL). You'll need to better describe what you're wanting to filter the list to in order for me to suggest anything further.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] List filters and empty or null fields
Time to create page: 0.223 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. [SOLVED] List filters and empty or null fields

Search

Login / Logout

User Menu