Apex Code Development You need to sign in to do that
Don't have an account?
Molson94 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.
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.
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
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.
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?