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?

  • It seems like whatever process you had (e.g. Field update on WR) to calculate and populate value of Total_Appt_Time__c field, has to be tweaked to include its previous value (as @sfdcfox commented in the comment to his answer): Total_Appt_Time__c + (Appointment_Stop_Time__c - Appointment_Start_Time__c) – o-lexi Feb 21 '17 at 22:48
  • Thanks but how do I add a field that references itself in the formula - Total_Appt_Time__c = Total_Appt_Time__c + (Appointment_Stop_Time__c - Appointment_Start_Time__c). I cant wrap my head around that piece. – Clink Feb 21 '17 at 23:55
  • It should be working fine, test it – o-lexi Feb 21 '17 at 23:56
  • Glad to hear. Why did you accept sfdcfox's answer then? )) – o-lexi Mar 3 '17 at 20:57

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.

| improve this answer | |
  • Thanks. I'm currently using process builder to accomplish Option 1. This works when there is only one appointment. My issue is that now that I can have multiple appointments, I need the start and stop times to re-populate each time, and I need the Total_Appt_Time__c field to be the cumulative sum of all appointment times (like a running total of total appointment time). – Clink Feb 21 '17 at 22:04
  • @Clink Just add the new time to the cumulative total whenever you trigger a stop event. – sfdcfox Feb 21 '17 at 22:07
  • Thanks, but how can that be accomplished within the same field? How can I have an update take the previous value + the new incremental increase, to get a new value within the same field? like Total_Appt_Time__c (New) = Total_Appt_Time__c (Old) + (Appointment_Stop_Time__c - Appointment_Start_Time__c) – Clink Feb 21 '17 at 22:15
  • 1
    @Clink I'd think it'd be as simple as Total Appt Time = Total Appt Time + (Stop Time - Start Time). Maybe you could add a pic? – sfdcfox Feb 21 '17 at 22:21
  • Can a field reference itself in a process or workflow update? I guess I'm thinking of a circular reference error – Clink Feb 21 '17 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.