Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I am having problems creating a SOQL query over my 3 objects.

Contact, Patient and Disease

Patient and Disease are in a Master Detail relationship, where Disease is the Master

Contact is a Lookup field in Patient.

So, the query that I need is: Select Id from Contact, where patient is that contact and has disease x

I need this query to populate the list targetObjects in this code:

List <Id> targetObjectIds = new List <Id> ();
List <Contact> targetObjects = new List <Contact> ();

targetObjects = [SELECT Id FROM Contact];
for(Contact cnt : targetObjects) {
  targetObjectIds.add(cnt.Id);
}

Messaging.MassEmailMessage mail = new Messaging.MassEmailMessage();
mail.setTargetObjectIds(targetObjectIds);

Tia.

share|improve this question
    
Can you please describe your question with an example ? –  S.Sharma Jun 17 at 10:08
add comment

3 Answers 3

up vote 3 down vote accepted

Since Contact is a Lookup for Patient, relevant Contact id is available at Patient object itself. So something like below should work. This is for going from child to parent
SELECT Contact__c FROM Patient__c WHERE Disease__c = 'your_val'

This is for going from parent to child
SELECT Id, Name, (SELECT Contact__c FROM Patient__r) FROM Disease__c

Apply your naming conventions where it's required.
If you are facing with can not understand the relationship error better to check the correct API name by generating the WSDL (Name > Setup > Develop > API > Generate Enterprise WSDL).

share|improve this answer
    
I have tried something similar before, but I always get the error that it doesn't understand the relationship Patient__r. –  user8737 Jun 17 at 10:22
    
This is because you haven't given the correct relationship name. see my update –  highfive Jun 17 at 10:29
    
So, I am now using the first query, which gets me what I want. However, I need to get the ID from the Contact Object as I will pass it to massemailmessage settargetobjectids and that only accepts ids from contacts and leads etc. is it possible to change the query somehow so that i will end up selecting the id from contact? thanks –  user8737 Jun 17 at 10:45
    
Since the Contact object is a lookup for Patient object it's always having Id of the relevant Contact in Patient object. So when you use Contact__c field value of Patient object it's giving the same Id field value of Contact object. You can use that list for your method –  highfive Jun 17 at 10:50
    
Strange, as when my code is triggered, I get the following message in salesforce: Error:Apex trigger emailTrigger caused an unexpected exception, contact your administrator: EmailTrigger: execution of AfterUpdate caused by: System.EmailException: SendEmail failed. First exception on row 0; first error: INVALID_ID_FIELD, Only contacts, leads and users allowed for targetObjectIds.: []: Class.TriggerHandler.afterUpdateWorkflowActions: line 34, column 1 –  user8737 Jun 17 at 10:56
show 4 more comments

Try this.

Select (Select Contact__c From Patient__r)
From Disease__c s 
WHERE Disease__c.Name == 'Disease X'

Note: Contact could be __c or just Contact.

Hope this helps !

share|improve this answer
    
Thanks, but I need to select from Contact. In this example I am selecting from Patient__r and from Disease__c. So the ID I am getting in the end will be from the contact object. –  user8737 Jun 17 at 10:55
    
You are querying the COntact IDs. Give it a try on either workbench or force.com IDE. You are getting the IDs of COntacts where Patients are looking up that has a master of Disease = Disease X –  JHONG Jun 17 at 11:00
add comment

I think you need to query from the Patient object, try this:

for (Patient__c pat : [ SELECT
                            Id,
                            Disease__c,
                            Disease__c.Name,
                            Contact__c
                        FROM
                            Patient__c
                        WHERE
                            Disease__c.Name = 'Some Disease'    
                        LIMIT
                            1000    ]) {

    targetObjectIds.add(pat.Contact__c);                            
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.