3

I am trying to identify any CASE records that have a email in custom field that matches personemail in account object. in sql I would write this something like this.. what is the corresponding soql query?

select id from case c
where exists
(select 1 from account a
  where a.personemail = c.contact_email__c )
4

You will have to do it in multiple parts as the functionality like in sql does not exist

For a all cases:

Case[] matched_cases = New Case[]{};

for(Case c : [Select ID, CustomField, Account.email From Case]){ //best to limit this if possible based on what you are doing

    if(c.CustomField == c.Account.email)
         matched_cases.add(c);

}

Now if you know the email you could do something like this

[Select ID, customField From Case Where AccountID IN (Select ID From Account Where email = :emailstring)]

It all depends on what you are doing and the context of what you are doing

2

Eric's answer certainly works, and is typically the best option, but in some cases it may be beneficial to create a formula field that can be used for this purpose.

To do so you'd create a new checkbox formula field on the case object, with a value of contact_email__c = account.personemail. This formula returns true if they match, false if not. You can then query against this: SELECT Id From Case WHERE new_bool_formula__c = TRUE.

One other tip that's tangentially relevant: if you're comparing fields on the same object (case.field1 = case.field2) you can even get an index applied to the formula field.

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.