We have a requirement, after leads are converted, we are sending data to a partner about them. We have a batch process which will periodically look for newly converted leads, send the data to the partner in bulk, and then save ConvertedAccount.Was_Sent_to_Partner__c was true for each of the saved leads.

Our query looks like this:

                SELECT Id, External_Id__c
                FROM Lead
                WHERE IsConverted = TRUE
                        AND ConvertedAccountId != NULL
                        AND ConvertedAccount.Was_Sent_to_Partner__c != TRUE

But the problem is, that some values have never been set, so when queried with the Developer Console's Query Editor, their value shows as (null ConvertedAccount) .... which aparrently is not equal to either false or null.

Is there a way I can filter on this value?

  • ConvertedAccount.Was_Sent_to_Partner__c = null or ConvertedAccount.Was_Sent_to_Partner__c = '' in the WHERE clause should work. I'm able to do this in my org. – arut Sep 3 '20 at 16:39
  • @anut, ConvertedAccount.Was_Sent_to_Partner__c is a checkbox/boolean so '' definitely won't work. – Brian Kessler Sep 3 '20 at 18:11
  • Oops...I had tried at my end with ConvertedAccount.Name and missed to notice that your field Was_Sent_to_Partner__c is boolean. So, I guess you actually wanted the SOQL comparison on ConvertedAccountId? – arut Sep 4 '20 at 3:04
  • Not sure what you mean by the question. ... But it seems that the extra rows I was seeing in the developer console were false positives because the accounts did not exist anymore even though their value was not null on the lead. I fixed the problem by changing ConvertedAccountId != NULL to ConvertedAccount.Id != null. – Brian Kessler Sep 4 '20 at 14:18
  • I was gonna suggest the same i.e., usage of ConvertedAccount.Id, but thought you might have that already figured that based on David's answer. So, ended my previous comment with the question. – arut Sep 4 '20 at 16:29

That's just what Developer Console shows when you run a relationship query and the relationship on a given row is null. In this situation, ConvertedAccountId is null. You can't, and shouldn't, try to filter on the (null XXXXX) value, because it's a UI artifact of the Developer Console.

  • Just reread you answer and realized an inaccuracy. ConvertedAccount is null, but ConvertedAccountId is not null.... I would have thought that impossible and perhaps that's a defect.... but that's the heart of the problem. However, it is fixed by checking ConvertedAccount.Id instead of ConvertedAccountId. – Brian Kessler Sep 5 '20 at 21:56

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.