Grouping Opportunities in Projects

I recently ran into a requirement to group opportunities  into projects and add some insight for the sales reps on the state of their projects.  Here is a step-by-step walk-through, including the needed groovy script to aggregate some opportunity information on the project level.

Create the project object

Using the App Composer, I created a new object, specifying the automatic number generation for the project ID field

No object is available to users until the application is aware of what users are allowed to do through the roles they have.  This is just an initial security configuration though, security can be defined on a much more fine-grained level.

Then I added an account field using a dynamic choice list field to tie projects to accounts

At this stage, you might want to create default pages for this new project object.

A last thing is to add a Projects ‘picker’, a list of values that can be used by other objects to choose a project.  (e.g. this will be used in the next step when I add a project field to the opportunity object)

Add Project to Opportunity Object

There is no need to define a relationship between the project and the opportunity object as this will be implicitly created in the next step.

Then I added a new custom opportunity field for project of type dynamic choice list again.  This allows me to choose a project on each opportunity.  This is not a mandatory field as not all opportunities have to be linked to projects.

I then added the project field on the opportunity detail screen so sales reps can specify to which project their opportunity belongs>.

Adding the list of opportunities to the project

Using the App Composer, I added a related object to the project UI to visualize the list of opportunities associated with a project

More Project Fields

Since the association between projects and opportunities now is completed, I can add some extra fields to the projects that show insight in the linked opportunities.  I used formula fields so I can use a little bit of groovy script to go over the opportunities linked to the project and aggregate the needed information.  These are the fields I added:

• An attachment field to add project attachments is made available by creating the custom object
• # Open Opportunities: a number formula field to count the number of open opportunities

def vo = newView(‘OpportunityVO’)
def vc = newViewCriteria(vo)
def vcr = vc.createRow()
def vci = vcr.ensureCriteriaItem(‘Project_Id_c’)
vci.setOperator(‘=’)
vci.setValue(Id)
vc.insertRow(vcr)
vo.appendViewCriteria(vc)
vo.executeQuery()

def total = 0

while (vo.hasNext()) {

def optyRec = vo.next()

def statusCode = optyRec.getAttribute(‘StatusCode’)
if (statusCode == ‘OPEN’) {
total += 1
}
}

return total

• Status: a text formula field indicating based on the ‘# Open Opportunities’, whether a projects should be considered ‘Active’ or ‘Inactive’.  A project is active as soon as there is at least 1 open opportunity.  The formula is reusing the formula field above.

def result = ‘Inactive’

if (NumberOpenOpportunities_c > 0) {
result = ‘Active’
}
return result

• # Open vs Closed Opportunities: a text formula field, showing how many opportunities out of the total number of linked opportunities are still open

def vo = newView(‘OpportunityVO’)
def vc = newViewCriteria(vo)
def vcr = vc.createRow()
def vci = vcr.ensureCriteriaItem(‘Project_Id_c’)
vci.setOperator(‘=’)
vci.setValue(Id)
vc.insertRow(vcr)
vo.appendViewCriteria(vc)
vo.executeQuery()

def total = 0
def open  = 0

while (vo.hasNext()) {

def optyRec = vo.next()

def statusCode = optyRec.getAttribute(‘StatusCode’)

total += 1
if (statusCode == ‘OPEN’) {
open += 1
}
}

def result = open + ‘ / ‘ + total

return result

• Total Open Opportunity Revenue: a number formula field summarizing the open opportunity revenue total for the opportunities that are linked to each project.

def vo = newView(‘OpportunityVO’)
def vc = newViewCriteria(vo)
def vcr = vc.createRow()
def vci = vcr.ensureCriteriaItem(‘Project_Id_c’)
vci.setOperator(‘=’)
vci.setValue(Id)
vc.insertRow(vcr)
vo.appendViewCriteria(vc)
vo.executeQuery()

def total = 0

while (vo.hasNext()) {

def optyRec = vo.next()
def statusCode = optyRec.getAttribute(‘StatusCode’)
if (statusCode == ‘OPEN’) {
total += optyRec.getAttribute(‘Revenue’)
}
}

return total

I must admit there were slightly more efficient ways to write the groovy above, but this way I could show a few creative examples, like basing a formula field on another formula field (the status field is based on the field containing the number of open opportunities).

These fields were added to the project list view and the project detail view.  I used the field grouping functionality to show the aggregated numbers in a separate group.

More related objects to Projects

In order to work properly with projects, I allowed sales reps to link notes to projects.

Future Enhancements

There are a few more things I could add from here:

• Ability to link activities to projects
• Ability to add reports to projects that summarize in visually attractive ways, information that is available about the linked opportunities, like a win-loss analysis.
• Create custom subject area to add an ability to report on project and their linked opportunities

 Results

Sales reps now can maintain a list of their projects

On each opportunity, they can indicate to what project the opportunity belongs

On the project, an overview of the related opportunities is available