0

I'm new in Apex, I'm running a schedule apex to send out email notification to Assigned_Reviewer__c on Custom object called "Patient relations Review" if the record status is "In progress". We want to extend this functionality across 5 different objects which may or may not have relationship between each other. We want to send out 1 email to all users if they are involved in 5 different objects where the status is Open. The only thing in common in all of 5 objects is User look up field. Can we query on User object to pull out records that the user is involved in different objects? If not, what is the best way to achieve this? Can someone provide me an example?

Thanks!

global class scheduledBatchable implements Schedulable {
    
    global void execute(SchedulableContext sc) {
        
        List<Patient_Feedback_Review__c > ListingPRR = New List<Patient_Feedback_Review__c >();  
        ListingPRR = [SELECT id, Assigned_Reviewer__c, Status__c FROM Patient_Feedback_Review__c  WHERE Status__c= 'In Progress'];
        EmailTemplate template = [SELECT id,Name, DeveloperName FROM EmailTemplate WHERE DeveloperName = 'Patient_relations_Reviews'];
        Messaging.SingleEmailMessage[] mails = new Messaging.SingleEmailMessage[0]; 
        
        Set<Id> userIds = new Set<Id>(); 
        For(Patient_Feedback_Review__c PR: ListingPRR ){
            userIds.add(PR.Assigned_Reviewer__c);
            }
         for(Id userId: userIds) {
            Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
            mail.setReplyTo('[email protected]');
            mail.setSenderDisplayName('Reviews');
            mail.setTargetObjectId(userId);
            mail.setSaveAsActivity(false); 
            mail.setTemplateId(template.id);
            mails.add(mail);
        }
        Messaging.sendEmail(mails);
    } 
  }

1 Answer 1

1

You could query the User object and subquery the 5 children objects to get the number of open child records in each, and then build a list of users that have values returned in any of the subqueries to send the email to

See parent-to-child queries here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

And a hopefully helpful way to find the child relationships to use in the subquery: https://developer.salesforce.com/forums/?id=906F0000000MJapIAG

Also generally useful to understand the Salesforce query limits (e.g. you're allowed up to 20 parent-to-child subqueries in a query): https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm

2
  • User doesn't have any child relationships but other 5 objects has lookup field to User object. Can I still query on user? Sep 14, 2020 at 22:02
  • In Salesforce, as with any database, a relationship between 2 entities automatically results in a Parent and Child relationship. If, for example, you have Object A and Object B, and B has a relationship field to A, A is the Parent Object and B is the Child Object. This creates a Child-to-Parent relationship from A to B, and a Parent-to-Child relationship from B to A. TL;DR yes, you can query the 5 Child Objects from the User because those Child Objects have relationship fields to the User
    – Instread
    Sep 14, 2020 at 23:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .