Details about this thread
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 309 subscribers
  • Views 1097 views
  • Users 0 members are here
Related content from around SugarClub

Reports - Adding display columns from other modules in an opportunity report

Ruben Sanchez

Hi, I would like to pull in information from the Notes module when running an opportunity report. Sales users regularly save updates as notes on the related opportunity. Sales leadership would value being able to display the subject as well as the timestamp on the most recent note when looking at this pipline report. Is that possible.

My current report is a summation report with details. I'm looking at current open ops and grouping by our forecast categories. Ideally i could add the note subject and note timestamp as additional display columns on this report.

Thank you for your help!!!

Parents
  • +1

    Hi ,

    There is no way to limit a report to only show the most recently created record related to the primary module you are reporting on. If you added columns for related note details, your opportunity report would replicate a row in the report for each note related to an opportunity and thus result in an inaccurate pipeline report.

    Alternatively, you could create a set of fields on the Opportunity module to capture this context and use SugarBPM to generate note records. 

    1. Create the following fields in Opportunities via Studio:
      1. Text or TextArea field to capture the body of the note. Choose 'Text' if you never expect the contents to exceed 255 characters.
      2. Datetime field to capture the date & time of the last note entry
      3. Text field (optional) to capture the subject of the note
    2. Add the text field(s) to the appropriate Opportunity layouts so users can fill in the details. If you add the datetime field to editable layouts, I recommend making the field read-only via roles so only SugarBPM can make changes to the value. 
    3. Create a process definition so that when an opportunity is either (1) created with a note field populated, or (2) edited with a new note field value, then update the datetime field on the opportunity and create a related note with value(s) your user entered.



      Your 'Create Related Note' action will use the appropriate variable(s) from the Opportunity fields:

    This solution could also provide a potential benefit of increasing ease of use with your sales team as they no longer would need create separate related notes to the Opportunity, and the fields can be added to list views and dashboards to provide important contextual information. 

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Reply
  • +1

    Hi ,

    There is no way to limit a report to only show the most recently created record related to the primary module you are reporting on. If you added columns for related note details, your opportunity report would replicate a row in the report for each note related to an opportunity and thus result in an inaccurate pipeline report.

    Alternatively, you could create a set of fields on the Opportunity module to capture this context and use SugarBPM to generate note records. 

    1. Create the following fields in Opportunities via Studio:
      1. Text or TextArea field to capture the body of the note. Choose 'Text' if you never expect the contents to exceed 255 characters.
      2. Datetime field to capture the date & time of the last note entry
      3. Text field (optional) to capture the subject of the note
    2. Add the text field(s) to the appropriate Opportunity layouts so users can fill in the details. If you add the datetime field to editable layouts, I recommend making the field read-only via roles so only SugarBPM can make changes to the value. 
    3. Create a process definition so that when an opportunity is either (1) created with a note field populated, or (2) edited with a new note field value, then update the datetime field on the opportunity and create a related note with value(s) your user entered.



      Your 'Create Related Note' action will use the appropriate variable(s) from the Opportunity fields:

    This solution could also provide a potential benefit of increasing ease of use with your sales team as they no longer would need create separate related notes to the Opportunity, and the fields can be added to list views and dashboards to provide important contextual information. 

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Children
  • 0 in reply to Chris Raffle

    Thanks so much for providing the basis for our solution!

    Here's what we did:

    1. Create two new fields on the Opportunities module in Studio:     
      1. Last Note Date (used a datetime field type)
      2. Last Note Description (used a text area field type)
      3. Added these as read-only fields to our record view layout 
    2. Create a new SugarBPM Process to update these fields with the date and description for each new note
    3. Once that BPM was enabled and working then add the “Last Note Date” and “Last Note Description” fields as display columns on opportunities reports