0

I need to create a report with the Last Modified By and Last Modified Date from the Opportunity Contact Role object.

This is a standard junction object and the standard field Last Modified By is in the Object Manager but I can't make it visible on the report.

I also tried to create a custom formula field on an Opportunity and a Contact but there is just no option to select Contact Role object there. Also a workaround of adding the fields as columns in the related list seems to be impossible.

The Last Modified By and Last Modified Date can be downloaded via Data Loader and viewed on the Contact Role record so the data is definitely there.

I would be grateful for any hints.

1 Answer 1

0

The standard 'Opportunity with Contact Roles' report type indeed only contains a limited set of fields.

But, you can create your own custom report type. (you might want to read up on that first)

  • Go to setup->Report Types and click 'New Custom Report Type'
  • Select the 'Opportunity' as your Primary Object and fill in all other values with whatever is appropriate. Don't forget to select status 'Deployed' or you won't be able to use the report type
  • click 'Click to relate another object'
  • Select the 'Contact Roles' and Save

The new report type will be default contain all the fields on both objects.

The reason that this is not available be default is simply that Salesforce wants to help you prevent creating inefficient reports.

-----EDIT-----

Turns out Salesforce still show the Contact-level fields on the report, even if you select ContactRole level fields. I checked the report metadata for that scenario and it shows the right field on the metadata, i.e. Opportunity.OpportunityContactRoles$LastModifiedDate . But that must resolve to the Contact-level field then internally, because of the many-to-many relationship.

Anyway, the workaround would be to create formula fields on OpportunityContactRole. E.g. a Date or DateTime formula field that contains the LastModifiedDate of the OpportunityContactRole. Then include that in your custom report types and then on your report and it will show the right date on the report (I tested this and it works correctly).

3
  • Thanks @guy I have created the custom report type following your instructions. Now in the new report the two columns Last Modified By and Date selected from the Contact Role section show data from the Contact and not Contact Role. Example: A contact was updated in Feb 2022 by a Migration User and my Opp Contact Role was updated by myself in Sep 2022. The report is showing me the Migration User with Feb 2022. I paid extra attention when adding the columns and I'm sure they're from the Contact Role group of fields. Any idea how to show the data from the Contact Roles?
    – MarkB
    Sep 2, 2022 at 12:44
  • Ah I see. Salesforce is not being very helpful/consistent there... Will update my answer with a workaround. Sep 2, 2022 at 13:40
  • Thank you @guy, the workaround worked for me as well.
    – MarkB
    Sep 8, 2022 at 11:31

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.