Check Capacity for Gaps and Overlaps

Use the following script to find capacity problems before they show up in your availability and utilization reports. The script reads your RH Capacity records and reports what it finds. It doesn’t create, change, or delete anything, so you can run it at any time, including in production.

This is one of the scripts in Create Capacity Records in Bulk. Read Before you run a script first.

What the script does

The script checks every active resource for the dates you choose, and reports:

  • Resources with no capacity in these dates – Active resources with no RH Capacity record anywhere in the dates checked.
  • Gaps between capacity records – Date ranges with no capacity between a resource’s records, including at the start of the dates checked.
  • Capacity ends before the last date checked – Resources whose capacity runs out early, with the last day that has capacity.
  • Overlapping records with the same role – Two records with the same role that share dates, which counts the resource’s hours twice on those dates. Records with different roles can share dates, so they aren’t reported.
  • Archived resources with capacity – Archived resources that still have capacity on or after the first date checked.
  • Records that span more than one calendar year – Records that would be smaller and faster to change as one record per year.
  • Records whose processing failed – Records whose Async Job Status is Failed, on any date.

Settings

  • CHECK_FROM – The first date to check. The default is today.
  • CHECK_TO – The last date to check. The default is December 31 two years from now, which matches the advice to keep about three years of capacity in place.
  • INCLUDE_ARCHIVED_RESOURCES – Set to true to also check archived resources for gaps and missing capacity.
  • MAX_LINES_PER_SECTION – The number of names listed under each heading of the summary. The default is 100. Longer lists end with a count of the rest.

The script

// Checks RH Capacity for problems and reports them. Nothing is created or changed.
//
// For each resource it reports: no capacity in the period checked, gaps between capacity records,
// capacity that ends before the period does, and overlapping records with the same role. It also
// lists archived resources that still have capacity, records that span more than one calendar
// year, and records whose background processing failed.
//
// How to run (Developer Console > Debug > Open Execute Anonymous Window, tick "Open Log"):
//   Run it and read the summary (filter the log to "Debug Only").

// ---- Settings ----
Date CHECK_FROM = Date.today(); // first date to check
Date CHECK_TO = Date.newInstance(Date.today().year() + 2, 12, 31); // last date to check
Boolean INCLUDE_ARCHIVED_RESOURCES = false; // true = also check archived resources for gaps
Integer MAX_LINES_PER_SECTION = 100; // longer lists are cut short in the summary

// ---- Script ----
Map<Id, ResourceHeroApp__Resource__c> resources = new Map<Id, ResourceHeroApp__Resource__c>(
   [SELECT Id, Name, ResourceHeroApp__Archive__c FROM ResourceHeroApp__Resource__c ORDER BY Name]
);

// Capacity in the period checked, grouped by resource in start-date order.
Map<Id, List<ResourceHeroApp__RH_Capacity__c>> capacityByResource = new Map<Id, List<ResourceHeroApp__RH_Capacity__c>>();
for (ResourceHeroApp__RH_Capacity__c capacity : [
   SELECT ResourceHeroApp__Resource__c, ResourceHeroApp__Role__c, ResourceHeroApp__Start_Date__c, ResourceHeroApp__End_Date__c
   FROM ResourceHeroApp__RH_Capacity__c
   WHERE ResourceHeroApp__Start_Date__c <= :CHECK_TO AND ResourceHeroApp__End_Date__c >= :CHECK_FROM
   ORDER BY ResourceHeroApp__Start_Date__c
]) {
   if (!capacityByResource.containsKey(capacity.ResourceHeroApp__Resource__c)) {
      capacityByResource.put(capacity.ResourceHeroApp__Resource__c, new List<ResourceHeroApp__RH_Capacity__c>());
   }
   capacityByResource.get(capacity.ResourceHeroApp__Resource__c).add(capacity);
}

List<String> noCapacity = new List<String>();
List<String> gaps = new List<String>();
List<String> endsEarly = new List<String>();
List<String> overlaps = new List<String>();
List<String> archivedWithCapacity = new List<String>();
List<String> multiYear = new List<String>();

for (ResourceHeroApp__Resource__c resource : resources.values()) {
   List<ResourceHeroApp__RH_Capacity__c> capacities = capacityByResource.get(resource.Id);
   if (resource.ResourceHeroApp__Archive__c && !INCLUDE_ARCHIVED_RESOURCES) {
      continue;
   }

   if (capacities == null) {
      noCapacity.add(resource.Name);
      continue;
   }

   // Walk the records in start-date order, tracking the last day covered so far.
   Date coveredThrough = CHECK_FROM.addDays(-1);
   for (ResourceHeroApp__RH_Capacity__c capacity : capacities) {
      if (capacity.ResourceHeroApp__Start_Date__c > coveredThrough.addDays(1)) {
         gaps.add(resource.Name + ': ' + coveredThrough.addDays(1).format() + ' to ' + capacity.ResourceHeroApp__Start_Date__c.addDays(-1).format());
      }
      if (capacity.ResourceHeroApp__End_Date__c > coveredThrough) {
         coveredThrough = capacity.ResourceHeroApp__End_Date__c;
      }
   }
   if (coveredThrough < CHECK_TO) {
      endsEarly.add(resource.Name + ': last day ' + coveredThrough.format());
   }

   // Records with the same role that share any dates double the capacity on those dates.
   for (Integer i = 0; i < capacities.size(); i++) {
      for (Integer j = i + 1; j < capacities.size(); j++) {
         ResourceHeroApp__RH_Capacity__c first = capacities[i];
         ResourceHeroApp__RH_Capacity__c second = capacities[j];
         if (first.ResourceHeroApp__Role__c == second.ResourceHeroApp__Role__c && second.ResourceHeroApp__Start_Date__c <= first.ResourceHeroApp__End_Date__c) {
            overlaps.add(
               resource.Name + ': ' + first.ResourceHeroApp__Start_Date__c.format() + '-' + first.ResourceHeroApp__End_Date__c.format() +
               ' and ' + second.ResourceHeroApp__Start_Date__c.format() + '-' + second.ResourceHeroApp__End_Date__c.format()
            );
         }
      }
   }

   for (ResourceHeroApp__RH_Capacity__c capacity : capacities) {
      if (capacity.ResourceHeroApp__Start_Date__c.year() != capacity.ResourceHeroApp__End_Date__c.year()) {
         multiYear.add(resource.Name + ': ' + capacity.ResourceHeroApp__Start_Date__c.format() + ' to ' + capacity.ResourceHeroApp__End_Date__c.format());
      }
   }
}

// Archived resources with capacity on or after CHECK_FROM, with the last day of that capacity.
Map<String, Date> archivedLastDays = new Map<String, Date>();
for (ResourceHeroApp__RH_Capacity__c capacity : [
   SELECT ResourceHeroApp__Resource__r.Name, ResourceHeroApp__End_Date__c
   FROM ResourceHeroApp__RH_Capacity__c
   WHERE ResourceHeroApp__Resource__r.ResourceHeroApp__Archive__c = TRUE AND ResourceHeroApp__End_Date__c >= :CHECK_FROM
   ORDER BY ResourceHeroApp__Resource__r.Name, ResourceHeroApp__End_Date__c
]) {
   archivedLastDays.put(capacity.ResourceHeroApp__Resource__r.Name, capacity.ResourceHeroApp__End_Date__c);
}
for (String name : archivedLastDays.keySet()) {
   archivedWithCapacity.add(name + ': capacity through ' + archivedLastDays.get(name).format());
}

List<String> failed = new List<String>();
for (ResourceHeroApp__RH_Capacity__c capacity : [
   SELECT ResourceHeroApp__Resource__r.Name, ResourceHeroApp__Start_Date__c, ResourceHeroApp__End_Date__c
   FROM ResourceHeroApp__RH_Capacity__c
   WHERE ResourceHeroApp__Async_Job_Status__c = 'Failed'
   ORDER BY ResourceHeroApp__Resource__r.Name, ResourceHeroApp__Start_Date__c
]) {
   failed.add(capacity.ResourceHeroApp__Resource__r.Name + ': ' + capacity.ResourceHeroApp__Start_Date__c.format() + ' to ' + capacity.ResourceHeroApp__End_Date__c.format());
}

List<String> summary = new List<String>();
summary.add('');
summary.add('==== Capacity check ' + CHECK_FROM.format() + ' to ' + CHECK_TO.format() + ' (nothing changed) ====');
Map<String, List<String>> sections = new Map<String, List<String>>{
   'Resources with no capacity in these dates' => noCapacity,
   'Gaps between capacity records' => gaps,
   'Capacity ends before ' + CHECK_TO.format() => endsEarly,
   'Overlapping records with the same role' => overlaps,
   'Archived resources with capacity on or after ' + CHECK_FROM.format() => archivedWithCapacity,
   'Records that span more than one calendar year' => multiYear,
   'Records whose processing failed' => failed
};
for (String heading : sections.keySet()) {
   List<String> lines = sections.get(heading);
   summary.add(heading + ': ' + lines.size());
   for (Integer i = 0; i < lines.size() && i < MAX_LINES_PER_SECTION; i++) {
      summary.add('   ' + lines[i]);
   }
   if (lines.size() > MAX_LINES_PER_SECTION) {
      summary.add('   ... and ' + (lines.size() - MAX_LINES_PER_SECTION) + ' more');
   }
}
System.debug(LoggingLevel.ERROR, String.join(summary, '\n'));

Run the script

Run the script with the steps in Run a script. The script has no DRY_RUN setting, because it never changes anything, so you only need to run it once.

Fix what the script finds

The summary showsWhat to do
Resources with no capacity in these datesCreate Capacity with Standard Hours
Capacity ends before the last date checkedCopy Each Resource’s Latest Capacity
Gaps between capacity recordsOpen the resource and create a record for the missing dates, or run Create Capacity with Standard Hours with ONE_RECORD_PER_MONTH set to true to fill whole months that have no capacity
Overlapping records with the same roleOpen the resource and shorten or delete one of the records
Archived resources with capacityEnd Capacity for Archived Resources
Records that span more than one calendar yearSplit Multi-Year Capacity Records
Records whose processing failedOpen each record, clear the Async Job Status field, and save it. Resource Hero processes the record again.

Related Support Posts:

Ready to get started?

Schedule a call to see if Resource Hero is right for you

Book now