5

I have a custom object named Milestones (with a field named MilestoneType) that has a Master-Detail relationship to Contact. I want to build a report where I can pull all contacts that have a milestone with a milestone_type of Deacon, but exclude those that also have a milestone_type of Retired. How do I build the report so that this conditions are met?

4

You can do this with Custom Report types (CRT). First, create a custom report type for Contacts with Milestones by going to My Name -> Setup -> Create -> Report Types. Choose Contacts as the base object and then add a relationship for Milestones.

Custom Report Type definition

Create a new report based on the new CRT and you can filter on MilestoneType:

Unfiltered: Unfiltered report

Filtered: Filtered report

Edit To show only Contacts who are Deacons and NOT retired we need to use cross-filters.

Unfiltered, showing updated Contacts. Note that the Contact "Second Test" has milestone types of both Deacon and Retired, while the other is just Deacon. enter image description here

By setting a cross-filter of Contacts without Milestones and Milestone Type equals Retired we filter "Second Test".

enter image description here

  • first off ty. How would I report on all contacts that are deacons and that are NOT retired? – fourq Oct 17 '12 at 19:01
2

Create two roll-up summaries on Contact. One that counts all milestones where type equals 'Deacon' and one that counts all milestones where type = 'Retired'. Then report on all contacts where deacons > 0 and retired = 0.

I don't believe there is a way to directly build this in a report without adding these roll-ups on the contact.

  • @gringriforce I like this answer. I was also thinking about creating a trigger that would update a text field with a semi delimited string of all milestones. If I did it with a text field would I be able to report against it? – fourq Oct 17 '12 at 3:59
  • @fourq Yes, if you had a text (or multi-select picklist) field like Deacon;Retired;... you could definitely report against that but I would avoid creating a trigger if you don't have to. – Greg Grinberg Oct 17 '12 at 4:14
  • You can create the report with cross-filters. – Mike Chale Oct 18 '12 at 23:21

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.