How to Improve Search with Custom Keywords

Overview

One of the challenges we get while going live with Salesforce Scheduler is to load Service Resources. This challenge includes loading resources initially, as well as maintaining them.

Now, To make a service resource; schedulable service resource, you have to take different steps, such as

1. Create Service Resource record
2. Assign Skills with a date range to that resource
3. Assign Service Resource to different territories that he will support
4. Assign Salesforce Scheduler permission set

Of course, you can use a data loader for this activity, however, it becomes difficult to keep mapping those ids in different CSV files which may lead to incorrect mapping of resources. Data loader does not support inserting bulk data into multiple objects in one go! Eventually, it becomes cumbersome for admins, especially if resources are multi-skilled, provide services at different branches OR have different operating hours at different locations. To simplify these steps, we can use a sample example to maintain service resources. All you have to do is to maintain CSV files with all the information you need! The crux of the whole logic here is to maintain this CSV file in a static resource and iterate over records in it with help of APEX.

For this particular blog, we will consider below scenario:

At Universal Banking Solutions company, there are 5 service resources.

1. Karl Schmidt is a banker who is serving customers for their wealth management needs at Market Street and Golden Gate Ave branch. He caters English speaking clients at the Market Street branch and German-speaking clients at Golden Gate Ave branch.
2. Rachel Adams is another resource who looks at general banking.
3. Ryan Dobson serves business banking needs at the Market Street branch.
4. Jacob Smith and Jessie Park take care of Wealth Management at the Market Street branch. However, they speak English and Korean respectively.

At an initial glance, we can see how we can map skills based on each resource. Here what skills look like:

Since this is a straightforward mapping, we can load this data via a data loader. Next comes the fun part where we will map correct resources to correct skills at respective branches.

Part 1:

Let’s begin with creating service resources. To create a service resource, all we need is a user record reference. All other information on the Service Resource record is the same across Salesforce Scheduler implementation. So, if we create a CSV with just one column it will suffice. We can take care of the rest in our APEX logic.

Here is a sample CSV:

Once we have this CSV in static resource, we can load it in an APEX class and iterate over it. Below is a pseudo logic:

//vFileName is static resource name
List<StaticResource> defaultResource = [SELECT Body
                                          FROM StaticResource
                                         WHERE Name = :vFileName];
blob tempB = defaultResource[0].Body;
String contentFile = tempB.toString();
String[] filelines = contentFile.split('\n');
filelines.remove(0); //This is to remove CSV header!

After having all CSV rows in a list of strings, we can iterate over it to create an instance of Service Resource.

Set<String> vSetStringUserNames = new Set<String>();
for (Integer i = 0; i < filelines.size(); i++) {
    String[] inputvalues = filelines[i].split(',');
    vSetStringUserNames.add((inputValues[0]).replaceAll('\r\n|\n|\r'.toLowerCase(), ''));
}

List<ServiceResource> vListServiceResource = new List<ServiceResource>();
Map<String, User> vMapUserToId = new Map<String, User>();
for (User vUser : [SELECT Id, UserName, Name
                     FROM User
                    WHERE UserName IN :vSetStringUserNames
]) {
    vMapUserToId.put(vUser.UserName, vUser);
}
for (String vUserName : vSetStringUserNames) {
    if (vMapUserToId.containsKey(vUserName)) {
        ServiceResource vResource = new ServiceResource();
        vResource.RelatedRecordId = vMapUserToId.get(vUserName).Id;
        vResource.ResourceType = 'T';
        vResource.Name = vMapUserToId.get(vUserName).Name;
        vResource.IsActive = true;
        vListServiceResource.add(vResource);
    } 
}

INSERT vListServiceResource;

In the end, we will get all matching users mapped with service resource records. PS: Once you create a Service Resource, you cannot delete it. You can only deactivate it. So make sure you have correct data in CSV.

Part 2:

Now moving on to mapping skills. This is needed when your org has skill matching enabled. Based on observations we made of resources at Universal Banking Solutions:

Since we have made DeveloperName of skill be matched with language capability, a combination of SkillName and Language will suffice. We will also map Skill Start date as those may differ from resource to resource.

In this part too, we will fetch all rows in a list of strings from CSV in a static resource. Once all rows are parsed, below is a pseudo logic that will iterate over it and create ServiceResourceSkill records.

Set<String> vSetStringUserNames = new Set<String>();

for (Integer i = 0; i < filelines.size(); i++) {
  String[] inputvalues = filelines[i].split(',');
  vSetStringUserNames.add((inputValues[0]).replaceAll('\r\n|\n|\r', ''));
}

//First fetch all Service Resource records based in first column
Map<String, Id> vMapServiceResourceToUserName = new Map<String, Id>();
Map<String, Id> vMapSkillNameToId = new Map<String, Id>();
if (!vSetStringUserNames.isEmpty()) {
  for (ServiceResource vServiceResource : [SELECT Id, Name, RelatedRecord.UserName
                                             FROM ServiceResource
                                            WHERE RelatedRecord.UserName IN :vSetStringUserNames
  ]) {
    vMapServiceResourceToUserName.put(vServiceResource.RelatedRecord.UserName, vServiceResource.Id);
  }
}
vSetStringUserNames.clear();

//Get all the skills based in the org. 
//We can even fetch only limited number of skills from second column of CSV.
for (Skill vSkill : [SELECT Id, DeveloperName FROM Skill]) {
  vMapSkillNameToId.put(vSkill.DeveloperName, vSkill.Id);
}

List<ServiceResourceSkill> vListServiceResourceSkill = new List<ServiceResourceSkill>();

//To make sure we have unique combination of skill matching
Set<String> vSetDeDup = new Set<String>(); 

//Now iterate over all the data in CSV rows.
for (Integer i = 0; i < filelines.size(); i++) {
  String[] inputvalues = filelines[i].split(',');
  String firstCol = (inputValues[0]).replaceAll('\r\n|\n|\r', '');

  List<String> vListAllSkills = new List<String>();
  String vSkill = (inputValues[1] +'_' +
                   inputValues[2]).replaceAll('\r\n|\n|\r', '');
  vListAllSkills.add(vSkill);

  for (String vSkillKey : vListAllSkills) {
    String vSkillId = vMapSkillNameToId.containsKey(vSkillKey)
                      ? vMapSkillNameToId.get(vSkillKey)
                      : null;
    String vSerResId = vMapServiceResourceToUserName.containsKey(firstCol)
                       ? vMapServiceResourceToUserName.get(firstCol)
                       : null;
    String deDupKey = vSkillId + vSerResId;
    if (!vSetDeDup.contains(deDupKey)) {
      ServiceResourceSkill vSrSkill = new ServiceResourceSkill();
      vSrSkill.SkillId = vSkillId;
      vSrSkill.ServiceResourceId = vSerResId;
      vSrSkill.EffectiveStartDate = (inputValues[3]).replaceAll('\r\n|\n|\r', '');
      vSrSkill.EffectiveEndDate = System.today().addDays(90);
      
      vListServiceResourceSkill.add(vSrSkill);
      
      vSetDeDup.add(deDupKey);
    }
  }
}

INSERT vListServiceResourceSkill;

Once this is successfully executed, you will get all the correct mapping of Skills to Service Resource!

Part 3:

Now to map a correct resource to territory, we need 2 columns in CSV. One should be the username and the other is the service territory name. We can add more columns in CSV to have a smart mapping of Operating Hours as well. For operating hours we can query for its name in our logic (considering operating hours are already loaded in the system).

The below code snippet will process the above CSV and insert data into the ServiceTerriotryMember entity.

Set<String> vSetStringUserNames = new Set<String>();
Set<String> vSetStringTerrNames = new Set<String>();
Set<String> vSetStringOHNames = new Set<String>();

for (Integer i = 0; i < filelines.size(); i++) {
  String[] inputvalues = filelines[i].split(',');
  vSetStringUserNames.add((inputValues[0]).replaceAll('\r\n|\n|\r', ''));
  vSetStringTerrNames.add((inputValues[1]).replaceAll('\r\n|\n|\r', ''));
  vSetStringOHNames.add((inputValues[3]).replaceAll('\r\n|\n|\r', ''));
}

Map<String, Id> vMapServiceResourceToUserName = new Map<String, Id>();
Map<String, Id> vMapSerTerNameToId = new Map<String, Id>();
Map<String, Id> vMapSerOHNameToId = new Map<String, Id>();

//First fetch all existing data to map to CSV column values.
if (!vSetStringUserNames.isEmpty()) {
  for (ServiceResource vServiceResource : [SELECT Id, Name, RelatedRecord.UserName
                                             FROM ServiceResource
                                            WHERE RelatedRecord.UserName IN :vSetStringUserNames
  ]) {
    vMapServiceResourceToUserName.put(vServiceResource.RelatedRecord.UserName, vServiceResource.Id);
  }
}
vSetStringUserNames.clear();
for (ServiceTerritory vSerTer : [SELECT Id, Name,
                                   FROM ServiceTerritory
                                  WHERE Name IN :vSetStringTerrNames
]) {
  vMapSerTerNameToId.put(vSerTer.Name, vSerTer.Id);
}
vSetStringTerrNames.clear();
for(OperatingHours vOHNames: [SELECT Id, Name
                                FROM OperatingHours
                               WHERE Name IN: vSetStringOHNames]){
  vMapSerOHNameToId.put(vOHNames.Name, vOHNames.Id);
}
vSetStringOHNames.clear();

List<ServiceTerritoryMember> vListServiceTerrMember = new List<ServiceTerritoryMember>();
Set<String> vSetDeDup = new Set<String>();
for (Integer i = 0; i < filelines.size(); i++) {
  String[] inputvalues = filelines[i].split(',');
  String firstCol = (inputValues[0]).replaceAll('\r\n|\n|\r', '');
  String vTerrName = (inputValues[1]).replaceAll('\r\n|\n|\r', '');
  String vOHName = (inputValues[3]).replaceAll('\r\n|\n|\r', '');

  String vServiceResourceId = (vMapServiceResourceToUserName.containsKey(firstCol))
                              ? '' + vMapServiceResourceToUserName.get(firstCol)
                              : null;
  String vServiceTerrId = (vMapSerTerNameToId.containsKey(vTerrName))
                          ? '' + vMapSerTerNameToId.get(vTerrName)
                          : null;
  String vOperatingHourId = (vMapSerOHNameToId.containsKey(vOHName))
                            ? '' + vMapSerOHNameToId.get(vOHName)
                            : null;

  String deDupKey = vTerrName + vServiceResourceId + vServiceTerrId;
  if (!vSetDeDup.contains(deDupKey)) {
    ServiceTerritoryMember vSerTer = new ServiceTerritoryMember();
    vSerTer.ServiceResourceId = vServiceResourceId;
    vSerTer.ServiceTerritoryId = vServiceTerrId;
    vSerTer.OperatingHoursId = vOperatingHourId;
    vSerTer.EffectiveStartDate = (inputValues[2]).replaceAll('\r\n|\n|\r', '');
    vSerTer.TerritoryType = (inputValues[4]).replaceAll('\r\n|\n|\r', '');

    vListServiceTerrMember.add(vSerTer);
  }
  vSetDeDup.add(deDupKey);
}

INSERT vListServiceTerrMember;

Epilogue:

In conclusion, if we can make a little customization using CSV data and APEX, we can easily maintain Service Resource data. Since loading a large CSV file may get into APEX heap and CPU time limits, we can combine all logic into a single batch class and process data in bulk. Sample batch class is here: https://github.com/snathpatil/smartserviceresource This Git repo will show you how you can execute it and make it work for all 3 objects. In case of missing data or exceptions during data insert, this batch class will send an email with a CSV attachment with error details. Admin can, later on, mitigate issues in that CSV and upload data with the data loader.

All this logic will help you maintain existing resources and onboarding new resources in your org. Even during loading data into your sandboxes, this comes in handy to quickly load some dummy data, so that developers and QA can test those corner cases which may get unnoticed if you create a small data set.

This will surely make your System Admins life easy!

Source Code

https://github.com/snathpatil/smartserviceresource