[SalesForce] “Bind Variables only allowed in Apex code” when comparing fields from two different tables

I'm trying to show all the leads that have the same DUNS number like the record i'm currently on (account)

Select l.Name, l.Email, l.CompanyDunsNumber,l.Status From Lead l
WHERE CompanyDunsNumber = (Select a.DunsNumber From Account a
Where Id='0015E00000CsMZXQA3')

and i receive the error:

Bind Variables only allowed in Apex Code

Best Answer

You are seeing this error due to the field to field comparison in WHERE clause of SOQL.

I am not sure how you are obtaining the current Account Id but I am providing an example with this Id hardcoded (you should move away from this approach within your design) for you to move forward with.

Let's start with creating a collection of Ids to store our found "Matching" Leads (based on DunsNumer). You will need to iterate over the Account(s) (plural once you have redesigned to pass in a List of Account Ids to work with rather than the single hardocded Id) and then for each Account we can query the Leads that match on DunsNumber.

List<Id> leadsWithMatchingDUNS = new List<Id>();
for (Account acc : [select DunsNumber from Account where Id ='0012400000Max8P']) {
    Lead l = [select id from Lead where CompanyDunsNumber = :acc.DunsNumber];
    if (l != null) {
        leadsWithMatchingDUNS.add(l.Id);        
    }
}
System.debug(leadsWithMatchingDUNS);

You can follow on with this List of Lead Ids however you choose to do so. I am simply debugging for POC.

Update

After posting above solution I realized I was going against Best Practice by perform a SOQL within a for loop.

There is a governor limit that enforces a maximum number of SOQL queries. When queries are placed inside a for loop, a query is executed on each iteration and governor limit is easily reached

As of that, I have modified the code to use Map values when querying the Lead object.

Map<Id, String> accountDUNS = new Map<Id, String>();
List<Id> leadsWithMatchingDUNS = new List<Id>();

for(Account acc : [
        select DunsNumber
        from Account
        where id = '0012400000Max8P']){
    accountDUNS.put(acc.Id, acc.DunsNumber);
}

for (Lead l : [
        select id,
          CompanyDunsNumber
        from Lead
        where CompanyDunsNumber = :accountDUNS.Values()]) {  
    leadsWithMatchingDUNS.add(l.Id);
}

System.debug(leadsWithMatchingDUNS.size());
Related Topic