4

Quick Information:

  • Using SOQL via the Enterprise SOAP API
  • This is a .NET 4.5 application, C#
  • This is in my Salesforce sandbox
  • I'm using Force.com Explorer
  • I'm new to StackExchange AND Salesforce. Be nice :)

Objective: To get all contacts, along with any matching leads who have been converted (they have a Lead.ConvertedContactId value).

I'm trying to left join Contacts on to Leads. The documentation seems clear enough, and I think I wrote the query correctly, but the results aren't as expected. Note in the screenshot below that no Leads__r values exist. Odd. Please scroll down to the next screenshot.

Contact to Lead Left Join

I ran a query to prove that the Lead records exist for the contact IDs (both queries use the same IDs). Please see the screenshot below to view the result set. Since I'm getting results, I expected the query from the first screenshot to produce objects of type Leads__r, but that doesn't work. Please scroll down to my question.

enter image description here

Question: Can someone help me understand what I'm missing? Is this a programmatic/syntax problem, or did I not convert the Lead to a Contact correctly in my sandbox? If it's the latter, then what might I have done wrong?

Thank you!

Tyler

Improve this question
1
  • i would try the first query in the developer console. as soql query and with apex (just create a list and then debug the Leads__r.size() )
    – Sergej Utko
    Commented Sep 5, 2013 at 21:11

1 Answer 1

Reset to default
2

As a start, the 'Leads__r' relationship is based on a custom field, as noted by the __r at the end, while convertedContactId is a standard field. Off the bat you're querying via the wrong relationship.

Looking at the schema it looks like there's no way to use a subquery (what your join is technically called in SOQL) as there's no relationship name. This is a quirk that can only happen with customer foreign-key (aka lookup) relationships. This means you'll need to change your logic to either

  1. Query leads and their related contacts, like so: SELECT convertedContactId FROM Lead WHERE convertedContactId IN ('your id that isn't provided as text', 'and the other one I'm too lazy to transcribe')
  2. Query the contacts, and then make a second query for any leads that reference them. As you're only querying the Id from the contact the first option makes sense, but I don't know your business case for this.

SOQL is odd in that it doesn't join in the traditional way, and instead uses subqueries in one direction and cross-object fields (e.g. querying lead for convertedContact.name) in the other. (there's also semi-joins and anti-joins, but that's getting longwinded)

Improve this answer
4
  • 1
    I'm assuming the question is about the built in lead conversion relationship. In which case the ChildRelationship from Contact to Lead resulting from the ConvertedContactId field doesn't have a relationshipName. So you can use the relationship from Lead to Contact, but not Contact to Lead.
    – Daniel Ballinger
    Commented Sep 5, 2013 at 21:52
  • @DanielBallinger doh, I could have sworn I added that to my answer, but you're exactly right.
    – ca_peterson
    Commented Sep 5, 2013 at 21:53
  • I think it is the '__r' on the relationship that makes it misleading. I'm guessing it is there as the Salesforce error message will prompt you to add it. I still gave you the +1 as the suggested solutions are good ideas :)
    – Daniel Ballinger
    Commented Sep 5, 2013 at 22:00
  • The whole concept of relationship named is pretty foreign at first, you'd normally assume it's the name of the related object, but if only it were that simple...
    – ca_peterson
    Commented Sep 5, 2013 at 22:07

You must log in to answer this question.

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