function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ryanschierholzryanschierholz 

How to get field from Custom Object that's linked to an Event via the WhatId / Related To field

I have a list of Events, some of them are Related To a custom object (Closing__c). I have Apex and SOQL code to query the Events into a List. I would also like to get the data from a field  (i.e. Summary__c) on the Closing that is related to the Event, so I can display that in a LWC. I can get basic info in SOQL using: 
SELECT Id, Subject, WhoId, WhatId, What.Id, Who.Id, Who.Name, What.Name
          FROM Event
But I would like to access other fields on the Closing__c record. What's the best way to do that? 
 
Best Answer chosen by ryanschierholz
ryanschierholzryanschierholz
Here is the solution I came up with. It involves getting the Events and getting the related object records and combining them into a custom sObject. I had to use a map for the related object, because using SOQL in the for loop cause too many queries. 
 
@AuraEnabled(cacheable=true)
    public static List<CustomEvent> getCustomEvents(String uId, String timeLength, Boolean relatedList, List<Id> relatedIds, String context){
        Id userId =  UserInfo.getUserId();
        List<Event> es = New List<Event>();
        List<CustomEvent> ces = New List<CustomEvent>();
        Date t = system.today();
        DateTime n = system.now();
        String sql = 'SELECT Id, Subject, WhoId, WhatId, What.Id, Who.Id, Who.Name, What.Name, ActivityDate, EndDate,StartDateTime, EndDateTime, IsAllDayEvent  ' + 
            ' FROM Event' +
            ' WHERE Id != null'; 
        
                sql += ' AND OwnerId =: userId ';

        sql += ' ORDER BY StartDateTime ASC';
        
        es = Database.query(sql);
        String [] status = new List<String>();
        status.add('Open');
        String [] recTypes = new List<String>();
        List<Closing_Agent_Role__c> cars = getClosingARs(recTypes, status, null, false);
        List<pba__Closing__c> cls = [SELECT Id, Summary__c FROM pba__Closing__c WHERE ownerId =: userId AND Closing_Status__c != 'Canceled'];
        Map<Id,String> m = New Map<Id,String>();
        for(Closing_Agent_Role__c car : cars){
            m.put(car.Closing__c,car.Closing__r.Summary__c);
        }
        for(pba__Closing__c cl : cls){
            m.put(cl.Id,cl.Summary__c);
        }

        for(Event e : es){
            CustomEvent ce = New CustomEvent();
            ce.Id = e.Id;
            ce.Subject = e.Subject;
            ce.ActivityDate = e.ActivityDate;
            if(e.WhatId != null){
                ce.WhatId = e.WhatId;
                ce.WhatIdType = string.valueOf(e.WhatId.getSobjectType());
    
                if(string.valueOf(e.WhatId.getSobjectType()) == 'pba__Closing__c'){
                    ce.RelatedSummary = m.get(e.WhatId);
                } else {
                    ce.RelatedSummary = e.What.Name;
                }
            }
            
            ces.add(ce);
        }
        return ces;


    }

public class CustomEvent {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Subject {get;set;}
        @AuraEnabled 
		public String WhatId {get;set;}
        @AuraEnabled 
		public String WhatIdType {get;set;}
        @AuraEnabled 
		public String WhoId {get;set;}
        @AuraEnabled 
		public String RelatedSummary {get;set;}
        @AuraEnabled 
		public String WhoName {get;set;}
		@AuraEnabled
        public Date ActivityDate {get;set;}

	}

 

All Answers

Abdul KhatriAbdul Khatri
Hi Ryan,

You should be able to do something like this.
SELECT Id, Subject, WhoId, WhatId, What.Id, Who.Id, Who.Name, What.Name
       , Closing__r.Summary__c 
FROM Event

Regards
ravi soniravi soni
Hy Ryan,
simply you can run following query and not only can get data of Summary__c field but also any field date is in parant object of event.
SELECT Id, Subject, WhoId, WhatId, What.Id, Who.Id, Who.Name, What.Name
       , Closing__r.Summary__c 
FROM Event
or you can get all fields value like this => Closing__r.Field_1__c,Closing__r.Field_2__c,Closing__r.Field_3__c.
this is the way to get field value of parent object.
let me know if above info helpful to you and mark it as best answer.
Thank  you
 
Suraj Tripathi 47Suraj Tripathi 47
Hi Ryan,
Greetings!

You can access the parent record using the dot(.) operator.
For example, If you want to access the account record of the specific contact.
SELECT Account.Name, Account.Website, FirstName, LastName FROM Contact WHERE LastName = 'XYZ'

Similarly, you can access WhatId Record from Event Object.
SELECT What.Name, What.Website FROM Event 

If you find your Solution then mark this as the best answer. 

Thank you!

Regards,
Suraj Tripathi
ryanschierholzryanschierholz
That's how it works with other relationships, but Events and Tasks using the WhatId (as opposed to a custom Lookup) are quite different, so that does not work, because there is no relationship (Closing__r) from Event to Closing.

Thanks for these ideas, folks, but this is a different situation. I even tried the query and got an error:
 
SELECT What.Name, What.Summary__c FROM Event LIMIT
                  ^
ERROR at Row:1:Column:19
No such column 'Summary__c' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

 
ravi soniravi soni
hi RyanAtTGH,
yes, now i have understand your question completely and I'm sure below info will help you.
set<string> setWhatIds = new set<string>();
for(Task oTask : [SELECT WhoId, WhatId,Who.Name, What.Name, Subject FROM Task]){
    if(string.valueOf(oTask.WhatId.getsobjecttype()) == 'Insightsfirst_Document__c'){
        system.debug(oTask.WhatId);
        setWhatIds.add(oTask.WhatId);
    }
    }
    if(setWhatIds.size() > 0){
        for(Insightsfirst_Document__c oInsighDocument : [SELECT Id,Name,SourceDate_GMT__c 
                                                         From Insightsfirst_Document__c 
                                                         Where Id In : setWhatIds]){
              system.debug('oInsighDocument : ' + oInsighDocument.SourceDate_GMT__c);                                               
            
        }
    }
Actually, you can't get data directly from  related To because WhatId have a multiple objects. but you can get data from above info.
simply, I perfomed the same requirment on task object and in task object firstly I fetched all Insightsfirst_Document__c's record where Id Contains Into WhatId and stored into a set. and after that I did query of Insightsfirst_Document__c(my custom object) and get all fields of my custom object.

Note => Make sure you have a Field Level security on field which you want to get.

let me know if this info helpful to you and mark it as best answer. and if you are facing any issue than please mention here.
Thank you


 


 
 
ryanschierholzryanschierholz
Yes, thank you for understanding, veer. This was my assumption for a solution as well, but it seemed inefficient in the sense that you have to run an addtional SOQL within the outter for loop. 

I am wanting to return data about the Event along with the Closing to an LWC, so my plan is that I would create a new sObject type in the Apex controller, which would have a combination of Event info and Closing info, in a manner similar to what you proposed. 
ryanschierholzryanschierholz
Here is the solution I came up with. It involves getting the Events and getting the related object records and combining them into a custom sObject. I had to use a map for the related object, because using SOQL in the for loop cause too many queries. 
 
@AuraEnabled(cacheable=true)
    public static List<CustomEvent> getCustomEvents(String uId, String timeLength, Boolean relatedList, List<Id> relatedIds, String context){
        Id userId =  UserInfo.getUserId();
        List<Event> es = New List<Event>();
        List<CustomEvent> ces = New List<CustomEvent>();
        Date t = system.today();
        DateTime n = system.now();
        String sql = 'SELECT Id, Subject, WhoId, WhatId, What.Id, Who.Id, Who.Name, What.Name, ActivityDate, EndDate,StartDateTime, EndDateTime, IsAllDayEvent  ' + 
            ' FROM Event' +
            ' WHERE Id != null'; 
        
                sql += ' AND OwnerId =: userId ';

        sql += ' ORDER BY StartDateTime ASC';
        
        es = Database.query(sql);
        String [] status = new List<String>();
        status.add('Open');
        String [] recTypes = new List<String>();
        List<Closing_Agent_Role__c> cars = getClosingARs(recTypes, status, null, false);
        List<pba__Closing__c> cls = [SELECT Id, Summary__c FROM pba__Closing__c WHERE ownerId =: userId AND Closing_Status__c != 'Canceled'];
        Map<Id,String> m = New Map<Id,String>();
        for(Closing_Agent_Role__c car : cars){
            m.put(car.Closing__c,car.Closing__r.Summary__c);
        }
        for(pba__Closing__c cl : cls){
            m.put(cl.Id,cl.Summary__c);
        }

        for(Event e : es){
            CustomEvent ce = New CustomEvent();
            ce.Id = e.Id;
            ce.Subject = e.Subject;
            ce.ActivityDate = e.ActivityDate;
            if(e.WhatId != null){
                ce.WhatId = e.WhatId;
                ce.WhatIdType = string.valueOf(e.WhatId.getSobjectType());
    
                if(string.valueOf(e.WhatId.getSobjectType()) == 'pba__Closing__c'){
                    ce.RelatedSummary = m.get(e.WhatId);
                } else {
                    ce.RelatedSummary = e.What.Name;
                }
            }
            
            ces.add(ce);
        }
        return ces;


    }

public class CustomEvent {
		@AuraEnabled 
		public Id Id {get;set;}
		@AuraEnabled 
		public String Subject {get;set;}
        @AuraEnabled 
		public String WhatId {get;set;}
        @AuraEnabled 
		public String WhatIdType {get;set;}
        @AuraEnabled 
		public String WhoId {get;set;}
        @AuraEnabled 
		public String RelatedSummary {get;set;}
        @AuraEnabled 
		public String WhoName {get;set;}
		@AuraEnabled
        public Date ActivityDate {get;set;}

	}

 
This was selected as the best answer