1

So i have a custom object which has in of one of the fields( Creator__c ) the Id of the person who created it which is also the Contact Id, and i'm trying to get the contacts first and last name field in that query, but i can't seem to make the query work.

Select Id, Name, Status__c, Subject__c, CreatedDate, (Select FirstName, LastName FROM Contact WHERE Id = Creator__c ) ,Creator__c FROM Custom__c Where AccountId = '0000000000001' ORDER BY Name ASC LIMIT 10

I'm guessing my Where clause in the sub-query is faulty.

So to summarize ,what i have (without the sub-query) is this:

Name | Status | Subject | Date | Id

And need

Name | Status | Subject | Date | Id | FirstName | LastName

Any ideas where i went wrong ?

EDIT #1: updated the question as i got one field name wrong

1
  • Salesforce is considering adding support for this in the future. Please vote on this idea.
    – sfdcfox
    Commented Nov 16, 2017 at 17:38

2 Answers 2

1

In SOQL, we are not able to directly compare two fields (i.e. WHERE Id = CreatedById is invalid).

There are two main workarounds here:

  1. Don't worry about filtering Contacts in your subquery, and use Apex to find your target Contact instead.
  2. Create a formula field on the child object (Contact in this case). A formula field can pull in field values from the parent record. Have your formula result in true when Id = Custom__c.CreatedById. You can then filter your subquery on where this formula field = true.

The issue with both approaches here is that CreatedById (a generally immutable system field) holds the Id of a User, not a Contact. You'll need to do some more work to get the appropriate user id into your Contact, but after that, the general approaches for both solutions should work.

2
  • I edited my question, it's not the CreatedById field, but a custom field called Creator__c, which does hold the Contact ID
    – lordZ3d
    Commented Nov 16, 2017 at 14:02
  • @Aca85 That doesn't change the nature of the answer. You can't compare a field to another field.
    – Adrian Larson
    Commented Nov 16, 2017 at 14:41
0

As far as I know, that is not permitted in SOQL. For example, if I try to execute the following:

SELECT Id FROM Contact WHERE LastName = FirstName

The server replies with the following message:

Executing:
=================================================
SELECT Id FROM Contact WHERE LastName = FirstName
=================================================

MALFORMED_QUERY:
Id FROM Contact WHERE LastName = FirstName
^
ERROR at Row:1:Column:40
Bind variables only allowed in Apex code

I understand that the compiler expects either a value such as LastName='<value>' or a bind variable like LastName= :myApexVariable.

In order to do what you need, you would need to bring the records and do that filtering in Apex, like this:

Contact[] contacts = [SELECT Id FROM Contact WHERE AccountId = '<Your Id>'];

Contact[] filteredContacts = new Contact[]{};
for(Contact con : contacts){
    if(con.LastName == con.FirstName){
        filteredContacts.add(con);
    }
}
2
  • Sorry, i don't know what Apex is, i use a custom CMS insterface with SF, where i can only use SOQL.
    – lordZ3d
    Commented Nov 16, 2017 at 14:01
  • @Aca85 well, Apex is the language in which developers can customize Salesforce... if you only hace access to SOQL... then do the same logic, but in whatever language you have access to :) Commented Nov 16, 2017 at 14:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .