0

enter image description here

My data structure is similar to above.

My query should be like

Emails sent on x date, grab fix status from customer.

Email has a lookup relationship to cust id and fix status also has a lookup to customer id,

I know its possible to do such like

Select id, custid__r.name from email where emailsent = today

but is there anyway to do like

Select id, custid__r.fix_status__c.fixed__c from email where emailsent = today

2

Something like this should get you close:

SELECT Id, fixed__c, Custid__c, Custid__r.FirstName__c, Custid__r.LastName__c
FROM FixStatus__c 
WHERE CustId IN (
    SELECT CustId 
    FROM Email__c 
    WHERE SentDate__c = TODAY
)

This query can get you to any field on the customer or fix status objects. Only issue with this query is that it assume the only thing you care about with the email is the sent date. if you wanted more fields from the email object that wouldn't work.

You could also try something like this:

SELECT Id, FirstName, LastName, (
    SELECT Id, Subject__c, Body__c
    FROM Emails__r
    SentDate__c = TODAY
),(
    SELECT Id, fixed__c
    FROM FixStatuses__r
)
FROM Customer__c

This query will get you access to all fields on all objects and filter so that only emails that were sent today are returned.

It just depends on what you are trying to do and how you need the data to look.

Note: This query was removed as it returns ALL customers regardless of whether an email was sent or not. The below query is an updated version that will get you want you need.

Update:

After reviewing the above two queries you could combine them to make something like this:

List<Customer__c> customers = [
    SELECT Id, FirstName__c, LastName__c, (
        SELECT Id, Subject__c, Body__c
        FROM Emails__r
        WHERE SentDate__c = TODAY
    ), (
        SELECT Id, Fixed__c
        FROM FixStatuses__r
    )
    FROM Customer__c
    WHERE Id IN (
        SELECT CustId 
        FROM Email__c 
        WHERE SentDate__c = TODAY
    )
];

for (Customer__c cus : customers) {
    for (Email__c email : cus.Emails__r) {
        system.debug(email.SentDate__c); // should always be today
    }

    for (FixStatus__c fix : cus.FixStatuses__r) {
        system.debug(fix.Fixed__c);
    }
}
3
  • does the child query part not work in workbench? – Tim Willis Oct 30 '19 at 22:03
  • 1
    Not sure I dont use workbench but it did work in the developer console. This is the exact query I tested: SELECT Id, (Select Id FROM Contacts) FROM Account WHERE Id IN (Select AccountId FROM Contact where CreatedDate = TODAY) – gNerb Oct 30 '19 at 22:06
  • Thanks for your thorough response, you gave me a lot to work through, I managed to find an acceptable solution based on your suggestion. – Tim Willis Oct 30 '19 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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