Reports - Not recognizing NULL value in Condition

English Language - Forum
1

I am trying to run a report where a condition test is a custom field NOT being empty but the report ends up including records where that field is empty.

System: SuiteCRM 7.13 on Debian 11 and Debian 12 (both tried)

I am not sure if it is because SQL stores the field as NULL, not blank, or if there is something else I am missing … or if it is a bug.

I want to create a Target list by running a report against a custom module in which I have stored several fields, including a text field which stores the email address associated with the record. The email field is fetched from the relevant Contact email1 field (if present).

I do not want to include records with no email addresses so I added a Condition to the Report:

  • Field: Email
  • Operator: Not Equal To:
  • Type: Value
  • Value: (left blank)

I have also tried

  • Field: Email
  • Operator: Not Equal To:
  • Type: Value
  • Value: @ (also tried %@%)

But the resultant report includes records with no Email

I see that SQL stores the Email field as NULL so I tried Value: NULL but that does not work either.

How do you create a condition so that the final selection does NOT include records where a specified filed has no entry (blank or NULL)?

2

Why is it that after working on something for hours and not solving it, as soon as you post for help, the answer is obvious? …

I post this as a “solution” to help others even though it is me admitting stupidity

The issue was my use of AND and OR conditions without using the brackets

I had
field1 = blank
AND
field2 = this
OR
field2 = that

I should have had
field1 = blank
AND
(
field2 = this
OR
field2 = that
)

which made it all work

arrgh :slight_smile:

1 Like