I wish to find all the tasks where one custom text field which corresponds to an ID of a custom object, but is not a foreign key which points to a valid custom object.

SELECT Id, ME__ThirdPartyId__c, WhatId FROM Task WHERE ME__ThirdPartyId__c NOT IN (SELECT Id FROM ME__MyCustomObject__c)

I get:

MALFORMED_QUERY: The left operand field in the where expression for outer query should be an id field, cannot use: 'ME__ThirdPartyID__c'

Any ideas?


A simpler way would be to form a set or list of strings which correspond to the Ids for of Me_MyCustomObject__c and then use it in the where clause of the query:

for example:

List<Me_MyCustomobject__c> myCustomObjects = [SELECT Id FROM Me_MyCustomObject__c];
Set<String> setIds = new Set<String>();

for (Me_MyCustomobject__c customObject:myCustomObjects) {

List<Task> filteredTasks = [SELECT Id, ME__ThirdPartyId__c, WhatId FROM Task WHERE Me_ThirdParty__c NOT IN :setIds];
  • 2
    I think if it were a set of IDs, you'd still get the type mismatch, It would probably have to be a set of Strings. – Daniel Hoechst Mar 8 '13 at 3:45

This is not possible to do in SOQL since you have two different field types you are trying to compare. Although IDs look like text, Salesforce treats them differently. If you remove the NOT from your SOQL, you'd get the same error.

You'd have to build a string of all your IDs and use that instead.

  • +1. Also, even if they were both Strings it would not be possible in the current format because semi-join/anti-join subselects can only be done with ID fields. – Peter Knolle Mar 8 '13 at 2:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.