You need to sign in to do that
Don't have an account?

SOQL Query not filtering out Formula NULLs
I have a formula which concatenates two text fields.
I have run queries against it and can see records which contain non-null values and fields listed as null values in Real Force Explorer Query Tool.
I have been using the syntax myfield__c != null but that does not filter out any of the values which appear to be empty. I also tried syntax myfield__c != '' for kicks but neither seems to filter out the records.
My next step is to reach out to the community to see if perhaps I need to be doing something different to evaluate null formula values in SOQL or if there is a function to check the length of a text field (so I can check for a length greater than 1 for example)
I would try trimming the 2 text fields in your formula field and see if that works.
For example, your myfield__c formula would be: TRIM( Field1__c ) & TRIM( Field2__c )
Also, in your query you want to specify both cases: where (myfield__c != null and myfield__c != '')
If you're writing an apex class or trigger you can create a variable to use in your SOQL query and set it to null like this:
if (myfield__c.trim().length() == 0) myString = null;
You need a try and catch system.nullpointerexception if the field is indeed null.
--
George Sowards
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Certified Salesforce.com Developer
www.RedPointSolutions.com
profile: linkedin.com/in/georgesowards
All Answers
I would try trimming the 2 text fields in your formula field and see if that works.
For example, your myfield__c formula would be: TRIM( Field1__c ) & TRIM( Field2__c )
Also, in your query you want to specify both cases: where (myfield__c != null and myfield__c != '')
If you're writing an apex class or trigger you can create a variable to use in your SOQL query and set it to null like this:
if (myfield__c.trim().length() == 0) myString = null;
You need a try and catch system.nullpointerexception if the field is indeed null.
--
George Sowards
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Certified Salesforce.com Developer
www.RedPointSolutions.com
profile: linkedin.com/in/georgesowards
Since I had no luck filtering on that formula field, and since I knew it was just a combo of two other fields, I broke the where clause down evaluating those two fields individually and that worked out ok.
Try this
http://www.tgerm.com/2011/01/possiblity-of-getting-wrong-results.html