Indexed Dates

In order to display records, Isimio must first load them from your Salesforce database. As with any database, once a certain number of records are accrued in an object, the process of retrieving records matching certain criteria becomes slower. Isimio must filter all records being shown to you by your selected date range.

This problem is often resolved by using a field index, which allows the database to find records that match your criteria much faster. While Salesforce allows the user to create an index on certain types of fields (any field that can be marked as an external ID), date and date-time fields are unfortunately not included in that list.

Isimio therefore implements a workaround that allows it to filter records not by their date fields, but by a numeric field that represents the date.

To implement this solution, follow these instructions:

1. Create two custom fields on your object. Both must be Numbers with at least 8 digits, and 0 decimal digits. For the purpose of this example, we’ll call them Indexed Start Date and Indexed End Date.
2. Mark both of the custom fields as External ID.
3. Use an automation tool to copy the date value of the regular start and end fields into the numeric fields. The format should be YYYYMMDD, for example: 20210520.
   • We recommend using an Apex before-insert/before-update trigger, or a record-triggered before-save flow.
     
     The following Apex functions are provided in the package to convert a date or date-time value into a numeric index:
     Kruvi.Utils.indexDate(Date dt)
     Kruvi.Utils.indexDate(DateTime dt)
     
     For example:
     myRecord.Indexed_Start_Time__c = Kruvi.Utils.indexDate(myRecord.Start_Date_Time__c);
   • While you may achieve the same result using Process Builder or a Workflow Rule, or after-save flows or triggers, field updates using these methods are slower and require a second database operation.
4. Open the Schedule Object record for the relevant object, and enter the API names of the indexed fields in the Indexed Start Field and Indexed End Field, respectively. For example: Indexed_Start_Date__c.

The following is a sample trigger handler class that indexes start and end fields:

public without sharing class ShiftTriggerHandler extends TriggerHandler {
    public override void beforeInsert() {
        //Before records are inserted
        List<Shift__c> lstNew = (List<Shift__c>)Trigger.new;
        for (Shift__c shift : lstNew) {
            //Index start/end fields for new records
            shift.Indexed_Start_Date__c = Kruvi.Utils.indexDate(shift.Planned_Start_Time__c);
            shift.Indexed_End_Date__c = Kruvi.Utils.indexDate(shift.Planned_End_Time__c);
        }
    }

    public override void beforeUpdate() {
        //Re-calculate the Indexed Start/End Date fields for shifts whose dates have changed
        Integer shiftCount = Trigger.new.size();
        for (Integer i=0;i < shiftCount;i++) {
            //We need to compare the old shift state to the new state
            Shift__c shiftNew = (Shift__c)Trigger.new[i],
                          shiftOld = (Shift__c)Trigger.old[i];

            if (shiftNew.Planned_Start_Time__c != shiftOld.Planned_Start_Time__c) {
                //Start time changed
                shiftNew.Indexed_Start_Date__c = Kruvi.Utils.indexDate(shiftNew.Planned_Start_Time__c); 
            }
            if (shiftNew.Planned_End_Time__c != shiftOld.Planned_End_Time__c) {
                //End time changed
                shiftNew.Indexed_End_Date__c = Kruvi.Utils.indexDate(shiftNew.Planned_End_Time__c);
            }
        }
    }
}