+ Start a Discussion
Admin User 8648Admin User 8648 

Nested SOQL for FSL Objects

I am trying to Pull up List of Service Appointments related to accounts however unable to get result as I keep geeting error .

Here is what I have Tried :-

A) SELECT ID , Name, (SELECT FSL__Service_Appointment__r.AppointmentNumber, FSL__Service_Appointment__r.SchedStartTime FROM FSL__Service_Appointment__r WHERE Status = 'Scheduled'  ORDER BY SchedStartTime ) FROM Account GROUP BY ID

B) SELECT ID , Name, (SELECT AppointmentNumber, SchedStartTime FROM ServiceAppointment WHERE Status = 'Scheduled'  ORDER BY SchedStartTime ) FROM Account GROUP BY ID

Both throws an Error Unable to Undertand the relationship. 
Shivdeep KumarShivdeep Kumar
Hi,
Did you check below notes?
Note: ServiceAppointment
Represents an appointment to complete field service work for a customer. This object is available in API version 38.0 and later.

Please edit your class and change the version to 38.0


If yes, then try below code:
 
SELECT ID , Name, (SELECT FSL__ServiceAppointment__r.AppointmentNumber, FSL__ServiceAppointment__r.SchedStartTime FROM FSL__ServiceAppointments__r WHERE Status = 'Scheduled'  ORDER BY SchedStartTime ) FROM Account GROUP BY ID

Please let me know if this help!

Thanks
Shivdeep
Admin User 8648Admin User 8648
I am using a SOQL in Workbench with api version of 43. Used ABove SOQL but it still throws an Error :-

INVALID_TYPE: 
FSL__ServiceAppointment__r.SchedStartTime FROM FSL__ServiceAppointments__r WHERE
^
ERROR at Row:1:Column:120
Didn't understand relationship 'FSL__ServiceAppointments__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Andrew GAndrew G
SELECT ID , Name, (SELECT ServiceAppointment.AppointmentNumber, ServiceAppointment.SchedStartTime FROM ServiceAppointments WHERE Status = 'Scheduled'  ORDER BY SchedStartTime ) FROM Account
will give a result (note - no 'group by' plus other changes)
 
SELECT ID , Name, (SELECT ServiceAppointment.AppointmentNumber, ServiceAppointment.SchedStartTime FROM ServiceAppointments WHERE Status = 'Scheduled'  ORDER BY SchedStartTime ) FROM Account GROUP BY ID
will give an error - MALFORMED_QUERY: Grouped query cannot use child relationships in the SELECT list
 
SELECT ID , Name, (SELECT ServiceAppointment.AppointmentNumber, ServiceAppointment.SchedStartTime FROM ServiceAppointments WHERE Status = 'Scheduled'  ORDER BY SchedStartTime ) FROM Account ORDER BY Account.Name
will give a result, but will list all accounts with or without service appointments. 
 
SELECT AppointmentNumber, SchedStartTime, Work_Order__r.Account.Name  FROM ServiceAppointment WHERE Status = 'Scheduled'  ORDER BY SchedStartTime
Will give the account name for the account via the work order record for all the Service Appointments that meet criteria

HTH
Regards
Andrew
 
Admin User 8648Admin User 8648
Hi @andrew,

ut this does not solve my problem. My Actual requiremet is List of SA Created with Parent as Accounts , Grouped by Accounts , to find for one account how many SA were created. 
Andrew GAndrew G
Hi
Tried a few options but none seemed to work.  I checked the error regarding Group By and children and found this nugget:
You can't use child relationship expressions that use the __r syntax in a query that uses a GROUP BY clause. 
ref: 
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_group_by_considerations.htm

If using a straight list as per option 3 or 4 in my previous, the only other option of which I can think would be to do the dirty data thing and have a computed field in the Service Appointment that inherits(computed) from the Account Record the account.Name.  Then do a Group By using that field and only query the Service Appointment table.

Regards
Andrew