I'm not a developer, I have no idea how to write code, however I have a really good understanding of what a trigger is, when it should be used and etc. If you're curious, I'm a Technical BA at a large financial services firm here in NYC.
Here's our challenge:
We rolled out approvals on opportunities January of 2008. Our user base has grown immensely as well as our use of opportunities and enforcement of getting approvals on discounted opportunities.
As we are enhancing our approval process we need to be able to answer certain questions:
How long does it take for an opportunity to be approved? We currently do pull up some information from the approval process through field updates on the opportunity but that is not detailed enough.
We want to really break it down. How many times is an opportunity submitted for approval? How many times is rejected before finally approved. Why is it being rejected? How many times are users being forced to re-submit due to pricing or product changes (we have validation forcing users to do this). Who is the approver on the opportunity and how long does it take that one person to approve on avg? Who is the approver changed to? Since we really can't answer these questions with out of the box reporting, I think I may have found a solution which would require a custom object and some APEX.
We have done some similar things with opportunity history reporting and case history reporting have found that these designs work well for us and how we need to look at the data.
I posted something similar on Salesforce Answers, but wanted to also put it here. Selfishly because I always wanted to start a blog, so I guess this is also my first post.
Here is the design, would love feedback if you've done this before. Did it work? What challenges did you have?
Design for Approval
History Reporting
Problem: Salesforce currently doesn’t allow for reporting or
metrics on approvals. Our business needs
to be able to report on how long it takes for one approval to happen, how many
times it was submitted/rejected/approved, how many different approvers it went
to.
Potential Solution:
Add a custom object called “Approval History Reporting” to
be a child object of opportunities through a master detail relationship.
Add the following fields to the custom object:
Approval History Start Date/Time
Approval History End Date/Time
Approver – lookup to user
Approver Changed to – lookup to user
From Approval Status (picklist) – Submitted, Approved,
Rejected, Recalled
To Approval Status (picklist) – Submitted, Approved,
Rejected, Recalled
Approver Comments text field
Age = formula field to calculate Approval History End –
Approval History Start, show number in hours
How this object will work
Records will be inserted and/or updated when certain things
happen.
Scenario 1:
Opportunity is submitted for approval
Upon initial submission of an opportunity to an approval
process a record will be inserted into the Approval History Reporting with the
following fields populated:
Approval History Start Date/Time – Time opportunity was
submitted for approval
Approver – user selected as approver
From Approval Status = Blank
To Approval Status = Submitted
Scenario 2:
Opportunity is Approved by Approver:
Existing record with To Status of Submitted will update to
have an Approval End Date/Time of the time the stage changed
New record will insert with the following information:
Approval History Start Date/Time – Time opportunity was
approved
Approver – user selected as approver
From Approval Status =Submitted
To Approval Status = Approved
Comments = any comments entered by approver
Scenario 3:
Opportunity is Rejected by Approver:
Existing record with To Status of Submitted will update to
have an Approval End Date/Time of the time the stage changed
New record will insert with the following information:
Approval History Start Date/Time – Time opportunity was
Rejected
Approver – user selected as approver
From Approval Status =Submitted
To Approval Status = Rejected
Comments = any comments entered by approver
Scenario 4:
Opportunity is Recalled by User:
Existing record with To Status of Submitted will update to
have an Approval End Date/Time of the time the stage changed
New record will insert with the following information:
Approval History Start Date/Time – Time opportunity was
Recalled
Approver – user selected as approver
From Approval Status =Submitted
To Approval Status = Recalled
Comments = any comments entered by approver
Scenario 5: Approver
is reassigned
Existing record that is submitted with no End time will have
the Approver Changed To lookup update to the new approver
Scenario 6:
Opportunity is Recalled and User Submits Again
New record will be inserted with the following information
Approval History Start Date/Time – Time opportunity was
submitted for approval
Approver – user selected as approver
From Approval Status = Recalled
To Approval Status = Submitted
Scenario 7:
Opportunity is Rejected and User Submits Again
New record will be inserted with the following information
Approval History Start Date/Time – Time opportunity was
submitted for approval
Approver – user selected as approver
From Approval Status = Rejected
To Approval Status = Submitted
Scenario 8:
Opportunity is Approved and user hits validation due to product/pricing changes
and is forced to submit again
New record will be inserted with the following information
Approval History Start Date/Time – Time opportunity was
submitted for approval
Approver – user selected as approver
From Approval Status = Approved
To Approval Status = Submitted
** For Clarification on Scenario 8, our validation is on the stage. If a user tries to close and an opportunity hasn't been approved or products/pricing has changed they will be forced to submit again.
** For Clarification on Scenario 8, our validation is on the stage. If a user tries to close and an opportunity hasn't been approved or products/pricing has changed they will be forced to submit again.
One item to note is I'm also considering maybe adding a type field and inserting records when approvers change. I'm not sure if that's necessary right now but that maybe an option for anyone trying to get more reporting on approvers and how often they change.
