0

I am are trying extracts both Service Appointment and its ParentRecord which is a Work Order.

SELECT LastModifiedDate,SchedStartTime,ParentRecordId,ParentRecord.Name FROM ServiceAppointment where Status IN ('002') AND ParentRecordType = 'WorkOrder' Order by SchedStartTime

I am able to get ParentRecord.ID and ParentRecord.Name but not other fields either standard or custom. Is there any way I can do this with a single SOQL statement?

3 Answers 3

3

There are some objects in Salesforce with polymorphic relationships. A single field that can point to more than just one type of SObject (though it can still only hold a single value).

At time of writing, these are all standard fields. We can't create custom polymorphic relationships. There is an Idea to allow custom polymorphic relationships with a status of "not planned".

Using standard query syntax is problematic. Salesforce can only guarantee that each SObject will have a common set of standard fields (Id, Name). If you were allowed to query other fields, how would Salesforce handle the situation where the related SObject doesn't have those fields?

To address that, Salesforce introduced a new query construct in API v46.0 (Summer '19, that's when it went GA at least). This is TYPEOF - WHEN - THEN - END documented in working with polymorphic relationships.

I tried it out myself on Event, and came up with the following

for(Event e :[
                 SELECT 
                     // Fields on the root object (Event) can still be queried like usual
                     Id, WhatId,
                     // TYPEOF needs to be followed by a polymorphic relationship name
                     // In this case, that means I'm using "What" instead of "WhatId"
                     TYPEOF What 
                         // SObject type, followed by the fields you want from that object
                         // Custom fields and traversing further up an object hierarchy are just fine
                         WHEN Opportunity THEN Name, SOW__c, Account.Name 
                         WHEN Case THEN Subject, Status 
                     // Need to explicitly tell Salesforce when the TYPEOF clause ends
                     END 
                 FROM Event 
                 // The "Type" qualifier only works on polymorphic fields
                 // If you don't add this filter, records related to other objects
                 //   will have "null" returned when you access e.What
                 WHERE What.Type = 'Opportunity' 
                 LIMIT 10
             ]
){
    Opportunity relatedParent = e.What;
    system.debug(relatedParent);
}

So applying this to your query would look something like

[SELECT 
    LastModifiedDate, SchedStartTime, 
    TYPEOF Parent WHEN WorkOrder THEN Id, Name, MyCustomField__c END
 FROM ServiceAppointment 
 WHERE Status IN ('002') AND Parent.Type = 'WorkOrder' 
 ORDER BY SchedStartTime]
3
  • Thank you for sharing this Derek! Dec 11, 2020 at 16:07
  • Thanks Derek!. Can we use Parent column in WHERE clause? I also need to filter by parent status.
    – Unknown
    Dec 12, 2020 at 13:44
  • 1
    @Unknown Nope, specific fields on the parent record can only be specified in the TYPEOF clause. A formula field might be able to help you out, but I'd expect that you'd need to do further filtering via Apex.
    – Derek F
    Dec 12, 2020 at 14:00
1

With Service Appointments, the ParentRecordId can be used to refer to many different kinds of objects, such as Work Orders, Accounts, and Work Order Line Items just to name a few. Since there isn't an explicit parent-child relationship between Service Appointment and a specific type of object, we can't use a relationship query to retrieve records

Select Id, (Select Id, Description from WorkOrders) from ServiceAppointment
// won't work since the relationship on Service Appointment could refer to any other object

The ID and Name fields are always available since we can expect them on every object.

1
  • 2
    This used to be the case, but it turns out Salesforce added a feature in Summer '19 to allow this. I don't blame you for not knowing. I myself didn't know about this until I started looking into it.
    – Derek F
    Dec 11, 2020 at 14:59
0

Work order and service Appointment have parent and child relationship.

Try something like this.

Set<Id> workRecordId = new Set<Id>();
for(ServiceAppointment sa:Trigger.new){
    workRecordId.add(sa.ParentRecordId);
}
Map<Id,WorkOrder> Workordermap= new Map<Id,WorkOrder>([Select Id,Name From WorkOrder Where Id=:workRecordId]);
For(ServiceAppointment sa:Trigger.new){
    if(Workordermap.get(sa.ParentRecordId)!= null){
        if(Workordermap.get(sa.ParentRecordId).WorkType.Name != null){
            sa.Work_Type__c = Workordermap.get(sa.ParentRecordId).WorkType.Name;
        }
    }
}

Something like this you can use...

You must log in to answer this question.

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