Posts

From Ankit Srivastava: Report available Appointment Slots by Resource

Salesforce Scheduler gives tools needed to simplify appointment scheduling in Salesforce. We can create a personalized experience by scheduling customer appointments in person, or by phone or video; with the right person at the right place and time. One of the strong feature of the product is its ability to manage availability of multiple Service Resources for various Work Types across multiple Service Territories.

Now the biggest question is if there is an out of the box Salesforce Report that can provide a list of service appointment slots available for all or a specific set of service resources. The Answer is NO.

So, how do we solve for it?

Salesforce Scheduler calculates the availability of a service resource at runtime on demand (when a user is scheduling an appointment). Salesforce Scheduler uses multiple entities along with data from external systems to calculate availability data. This information is NOT stored anywhere to report on.

To solve for the reporting Question – we need some complex level of customization. This can be achieved using the power of the Salesforce Scheduler and Salesforce platform to generate reports on Service Resource’s availability information.

Approach we will use in this blog post

Salesforce Scheduler provides the LxScheduler namespace which offers the apex method getAppointmentCandidates which provides the availability information. We will use this method to

  • retrieve the information using Apex
  • dump the retrieved information into a custom object that we will create and
  • report on top of the custom object

Create Custom Object – Appointment Slots

Let us create a custom object, “Appointment Slots” with these custom fields to store availability information

  • Service Resource (lookup)
  • Service Territory (lookup)
  • Work Type Group (lookup)
  • Start Date (Date/Time)
  • End Date (Date/Time)
  • Remaining Appointments (Integer, default value = 1)

Enable reporting for this custom object.

Query and Dump retrieved information into the Custom Object

We can use following batch apex to populate availability information in our custom object.
P.S. Apex governor limits will apply on this code (https://developer.salesforce.com/docs/atlas.en-us.234.0.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_apexgov.htm).

Slot.apex

class Slot {
Datetime startTime;
Datetime endTime;
List<String> resources;
String territoryId;
}

ReportDump.apex

public without sharing class ReportDump implements Database.Batchable<sObject>{
public Database.QueryLocator start(Database.BatchableContext BC){
String query = 'SELECT Id FROM ServiceResource where isActive = true';
return Database.getQueryLocator(query);
}

public void execute(Database.BatchableContext BC, List<ServiceResource> srList){
Integer DUMP_DURATION = 30;
String ACCOUNT_ID = '001xx000003GYQRAA4';
String SCHEDULING_POLICY = '0Vrxx0000004C92';
//Hardcoding WTG for now
List<String> wtgs = new List<String>();
wtgs.add('0VSxx0000004C92GAE');

List<Appointment_Slots__c> slots = new List<Appointment_Slots__c>();

for (ServiceResource sr : srList){
//Find all territories he works in
List<ServiceTerritoryMember> stms = [Select Id, ServiceTerritoryId
from ServiceTerritoryMember
where ServiceResourceId = :sr.Id];
List<String> territories = new List<String>();
for (ServiceTerritoryMember stm : stms){
territories.add(stm.ServiceTerritoryId);
}

for (String wtg : wtgs){
lxscheduler.GetAppointmentCandidatesInput input = new lxscheduler.GetAppointmentCandidatesInputBuilder().setWorkTypeGroupId(wtg).setTerritoryIds(territories).setStartTime(System.now().format('yyyy-MM-dd\'T\'HH:mm:ssZ', 'America/New_York')).setEndTime(System.now().addDays(DUMP_DURATION).format('yyyy-MM-dd\'T\'HH:mm:ssZ', 'America/New_York')).setAccountId(ACCOUNT_ID).setSchedulingPolicyId(SCHEDULING_POLICY).setApiVersion(Double.valueOf('54.0')).build();
String response = lxscheduler.SchedulerResources.getAppointmentCandidates(input);

List<Slot> objs = (List<Slot>)JSON.deserialize(response, List<Slot>.class);
for (Slot obj : objs){
Appointment_Slots__c slot = new Appointment_Slots__c();
slot.Start_Date__c = obj.startTime;
slot.End_Date__c = obj.endTime;
slot.Service_Territory__c = obj.territoryId;
slot.Service_Resource__c = sr.Id;
slot.Work_Type_Group__c = wtg;
slots.add(slot);
}
}
}
insert slots;
}

public void finish(Database.BatchableContext BC){
}

}

Create a Report using the Custom Object

Create a Salesforce report using this custom object. Refer – https://help.salesforce.com/s/articleView?id=sf.rd_reports_overview.htm&type=5 on how to create reports. Here is a sample matrix report we created which reports a Service resources total available hours at a Service location for an appointment topic / template – and this is how it looks.

Truncating records in custom Object

The example quoted above requires the Appointment Slots object to be empty in order to calculate the Service Resources availability. Ensure you truncate the data in the custom object before running the above batch job. Please refer this help article on Truncating Salesforce Obejcts https://help.salesforce.com/s/articleView?id=sf.dev_object_trunc.htm&type=5.

You can also use apex to delete all records from the object as shown at https://developer.salesforce.com/forums/?id=906F0000000BPDTIA4