function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
HaroldHHaroldH 

Is there query syntax to determine whether an ID field is empty?

I'm running a query against a custom object, MyObject__c, which contains a custom reference field: OpportunityId__c. The intent of the query is to return the set of MyObjects which do not have null or blank OpportunityId fields.

I've tried these two queries, and neither of them work. Both queries return MyObjects containing null or blank OpportunityId reference fields.

Select Id, OpportunityId__c from MyObject__c where OpportunityId__c != ''
Select Id, OpportunityId__c from MyObject__c where OpportunityId__c != null

Is there query syntax to determine whether an ID field is empty?

Message Edited by HaroldH on 06-23-2004 12:32 PM

DevAngelDevAngel

Hi HaroldH,

The answer is no.  You might try a not like '%0000%' or something to get the data in a back-handed way though.

HaroldHHaroldH
According to the 3.0 API dox, "The like operator is supported for string fields only." When running like against reference fields, we see "INVALID_QUERY_FILTER_OPERATOR: invalid operator on id field."

Oddly, combining "not" with "like" yields "malformed query".

Example: Select Id, Name from Opportunity where Name not like '%test%'

I've worked around the problem, but these items might be helpful in future releases.