+ Start a Discussion
Molson94Molson94 

Need help with a multiple custom object query!

Hi Everyone,

I am looking at writing a class that auto sends a templated notification to a custom list of contacts. I am struggling to get a query that correctly cascades the relationships between 4 custom objects. In my head i think this should be doable, but I cant seem to get it right on paper. The relationships are as follows:

Contact --LookUp--> Product_Contacts__c --LookUp--> Product__c --LookUp--> Promotion__c

I need to be able to tie Contact.Id, and Promotion__c.Id for use on the email template. But only where the Status__c of the Promotion__c is "Approved", and where the Product_Contact__c.Role is 'Manager' or 'Rep'.

In my head I keep thinking that this can be done with 1 query/list, but I may just be getting burned out thinking about it and require some additional feedback from the community.
Best Answer chosen by Molson94
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

As I don't have a holistic view of your scenario I might be mistaken but your query would be something like this:
SELECT Id, Status__c FROM Promotion__c
   WHERE Product__r.Product_Contacts__r.Contact =: contactIds
   AND  Product__r.Product_Contacts__c.Role IN ('Manager','Rep')
   AND Status__c = 'Approved'

You can remove the condition Product__r.Product_Contacts__r.Contact =: contactIds if you do not want to filter your query by contacts.


Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
 

All Answers

Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

As I don't have a holistic view of your scenario I might be mistaken but your query would be something like this:
SELECT Id, Status__c FROM Promotion__c
   WHERE Product__r.Product_Contacts__r.Contact =: contactIds
   AND  Product__r.Product_Contacts__c.Role IN ('Manager','Rep')
   AND Status__c = 'Approved'

You can remove the condition Product__r.Product_Contacts__r.Contact =: contactIds if you do not want to filter your query by contacts.


Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
 
This was selected as the best answer
Molson94Molson94
Zuinglio,

Thank you for the quick reply and the help.
I do not think this will render the list of Contact.Id (its actually referenced on the record type Legal_Name__c), and Promotion__c.Id.

It looks like the query only pulls in a list of Promotion Ids, and not the matched up Contact Ids

Woud it help if i provided a screenshot of the schema?

The end <List> would be:  Legal_Name__C.Id | Promotion__c.Id
With the dependencies that "Approved" Promotions would be included, and only Contacts with a Product_Contact__c.Role__c of Manager or Rep would be pair up with the promotions, based on the related Product?

Is that not possible given the gap between Contact and Promotion?