Cumulative Total for a Field

Question

I need a the field to reflect a cumulative total time of all appointments on a field on a record.
Throughout the negotiation of a contract, represented by a record for the Negotiated_Contract__c object, a Customer can have appointments.

Previously, when an appointment was started, a timestamp would be placed in the Appointment_Start_Time__c field, and when it ended a timestamp is added into Appointment_Stop_Time__c field. The difference between the Start and Stop Time is calculated and added into the Total_Appt_Time__c field.

Up until now, this was sufficient because there could only be one appointment for each contract negotiation, but now the requirement is that we can multiple appointments for a given negotiation.

With this in mind, when a new appointment is started, the old appointment start and stop times are cleared and new timestamps are entered; I can accomplish this with a workflow, but I also need some way to calculate a cumulative, or running total of the Total_Appt_Time__c field.

Somehow I need the Total_Appt_Time__c to provide a running total. So something like

Total_Appt_Time__c (New) = Total_Appt_Time__c (Old) + (Appointment_Stop_Time__c - Appointment_Start_Time__c)

So if the fist appointment was 40 minutes and the second was 20 minutes, then the Total_Appt_Time__c = 60 minutes.

Is there a way to accomplish this with a declarative solution (workflow, process builder, formula fields, etc) or does it require Apex? And if so, can someone help get me started?

Answer 1

There's several possible solutions.

Option 1

Build this with the process builder. When the status changes to started, set the Start Time field, and when it the status goes back to stopped, simply add the difference in time to the Total Time field. This would require about two Process Builder entries. The drawback is that you won't have the sub-totals available to you (e.g. there were three appointments, one for 15 minutes, one for 20 minutes, and one for 17 minutes).

Option 2

Create a related custom object with a master-detail relationship and a rollup summary field. When the appointment starts, set the Start Time field. When the appointment ends, create a new child object with the Start Time and Current Time; a formula on this child calculates the time, then triggers the rollup summary to calculate the total time. The only real drawback here is that it'll cost more storage space.

Option 3

Create a bunch of fields, like 4 or 5 sets of appointment times. Set each one appropriately. Of course, the drawback here is what to do when you overflow the designed limit. Reporting is also slightly more complicated with this design.

Option 4

You could build a running total in a long text area and process the total time via a trigger. Reporting will suffer (e.g. you can't easily report on appointments of X length), but you'll still save storage space and be able to store a large number of appointment events.

---

I could go on and on, but I think you get the idea. You need to choose between reportability, storage requirements, and complexity. With the number of options available, I'm pretty sure you can find something that fits your needs.