0

I have a custom Commission table which has a master detail relationship with contact. I have a List of AccountIds being passed into the function and then into the query. I'm getting the error: " Didn't understand relationship 'Contacts' in FROM part of query call" Any help would be great.

List<Commission__c> comList2 = [SELECT commission_amount__c, date_given__c,
                                         (SELECT Id FROM Contacts WHERE AccountId in : accountIds)
                                         FROM Commission__c];
Philip
  • 67
  • 7

1 Answers1

1
SELECT commission_amount__c, date_given__c, Contact__r.Name, Contact__r.Email
FROM Commission__c
WHERE Contact__r.AccountId IN :accountIds

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm

eyescream
  • 18,088
  • 2
  • 34
  • 46
  • Thank you eyescream. but i also need access to the name field on the contact object. Is that possible with this approach? – Philip Mar 31 '21 at 20:42
  • Sure. To go "up" you use the "dot syntax" like in formula editor. `SELECT Account.Owner.Profile.Name FROM Contact` is perfectly fine – eyescream Mar 31 '21 at 21:00
  • Thanks eyescream. Ive been trying to just do a debug message using a for loop on the List with the sql solution you provided. Ive tried using a for loop and printing the name of the Contact using Contact__c.Name but im getting an error saying " A non foreign key field cannot be referenced in a path expression: Contact__c" – Philip Mar 31 '21 at 21:23
  • `Contact__c` is the database column but the custom relationship's name would be `Contact__r`. It's like table alias if you have proper SQL background. For standard relationships it's similar. There's `AccountId` field on Contact or Opportunity but to go "up" you need to write `SELECT Name, StageName, Account.Name, Account.Description FROM Opportunity`. And then in code `for(Opportunity o : [SELECT ...]){System.debug(o.Account.Name);}` – eyescream Mar 31 '21 at 21:27
  • Ok I got it. Thank you It took my a minute to understand. I appreciate it a lot. Think I need a break. – Philip Mar 31 '21 at 21:32
  • No probs! If that documentation link is too dry for you try https://trailhead.salesforce.com/en/content/learn/modules/soql-for-admins/create-relationship-queries-with-standard-objects – eyescream Mar 31 '21 at 21:35