function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jucuzoglujucuzoglu 

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)

 

 

Best Answer chosen by Admin (Salesforce Developers) 
geosowgeosow

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

geosowgeosow

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

This was selected as the best answer
jucuzoglujucuzoglu

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.
OyeCodeOyeCode

Try this 

 

http://www.tgerm.com/2011/01/possiblity-of-getting-wrong-results.html