Display the last actity date and note body of a deal in tabluar view

[tbunch]

I'm trying to write a report with 6 columns:
Deal Name, Deal Owner, Deal Stage, Amount, Last Note Date & Last Note

Steps:

1. I start with a Custom Report starting with Deals and then the associated Sales Activities (filter on type Note) or associated with Sales Note (tried both).
2. I have 4 Deal columns (Deal Name, Deal Owner, Deal Stage and Amount) - Looks great.
3. I can add Activity Date with  MAX aggregation turned on... Great
4. When I add Note Body - I see a row for each note ever attached to the deal.  
5. Then I attempted to restrict it setting a limit to 1, hoping that I will get 1 note per deal;  However, the grouping options are only (MAX (Activity date) or Count of deals.  Both result in a single deal record showing up on the report.

Are there any clues as to how to better control the JOIN behavior?
Or alternative methods to accomplishing the same results?

I am really trying to keep this as a 1 row per deal report and I am fine if the note fields are NULL (that helps me work with the deal owner).

I did try to do my research before posting... these two did not quite get it done for me.

1. https://community.hubspot.com/t5/Tips-Tricks-Best-Practices/Display-details-of-the-last-activity-in-...
2. https://community.hubspot.com/t5/Dashboards-Reporting/Need-to-run-a-report-that-shows-notes/td-p/648... 

[tbunch]

Update on how we solved this via a workflow...

1. Create a Custom Field `Last Note` on the Deal Object.
2. Create a Workflow to populate deal.last_note.
   1. Workflow Trigger - Event (Has completed: Property value changed, Property name is Number of sales activities, New value: Number of sales activities is known)
   2. Custom Code

const hubspot = require('@hubspot/api-client');

exports.main = async (event, callback) => {
    const retryAttempts = 5; 
    const retryDelayBase = 1000;
    async function executeWithRetry(fn) {
        for (let attempt = 1; attempt <= retryAttempts; attempt++) {
            try {
                return await fn();
            } catch (error) {
                console.error(`Error occurred (attempt ${attempt}/${retryAttempts}): ${error}`);
                if (attempt < retryAttempts && error.response && error.response.status === 429) {
                    const delay = Math.pow(2, attempt - 1) * retryDelayBase;
                    console.log(`Retrying after ${delay} milliseconds...`);
                    await new Promise(resolve => setTimeout(resolve, delay));
                } else {
                    throw error;
                }
            }
        }
    }
  	
  try {
    // Using private app key to initialise hubspot client
    const hubspotClient = new hubspot.Client({
        "accessToken": process.env.API_KEY
 	});
    
    // Pass in property from workflow
    let dealId = event.inputFields['hs_object_id']
    console.log(dealId)
    
    // API call to pull in all of the associated notes  
    const dealResponse = await executeWithRetry(async () => { 
        const dealResponse = await hubspotClient.apiRequest({
        method: 'GET',
        path: `/crm/v3/objects/deals/${dealId}/?associations=notes`
        })
        return dealResponse
    })
    
    // Build out an array of note objects
   let json = await dealResponse.json()//.associations.p45774821_planned_payments.results   
   let notes = json.associations.notes.results
   let noteDetails = []

   for(let note of notes) {
        const noteResponse = await executeWithRetry(async () => {
            const noteResponse = await hubspotClient.apiRequest({
                method: 'GET',
                path: `/crm/v3/objects/notes/${note.id}/?properties=hs_createdate, hs_lastmodifieddate, hs_note_body`
            })
            return noteResponse
        })
        let json = await noteResponse.json()
        if (json.archived != 'true'){
            let noteNode = {
                "hs_createdate": json.properties.hs_createdate,
                "hs_lastmodifieddate": json.properties.hs_lastmodifieddate,
                "hs_note_body": json.properties.hs_note_body
            }
            noteDetails.push(noteNode)
        }
   }

   // sort notes hs_lastmodifieddate
   noteDetails.sort((a,b) => {
    const noteA = new Date(a.hs_lastmodifieddate)
    const noteB = new Date(b.hs_lastmodifieddate)
    if (noteA < noteB) {return -1}
    if (noteA > noteB) {return 1}
    return 0
   })
	console.log(noteDetails)
   // Create a String for the Last Note
   let lastNoteDate = new Date(noteDetails[0].hs_lastmodifieddate)
   let lastNoteString = lastNoteDate.getFullYear()+ '-' +(lastNoteDate.getMonth()+1)+ '-' + lastNoteDate.getDate() + " - " + noteDetails[0].hs_note_body
   // Remove html tags
   lastNoteString = lastNoteString.replace(/<[^>]*>/g, '')

   // Push the lastNoteString to the Deal
   const apiResponse = await executeWithRetry(async () => { 
      const apiResponse = await hubspotClient.crm.objects.basicApi.update('deals', dealId, {properties: {'last_note' : lastNoteString }});
        return apiResponse
      })
    let data = await apiResponse
    console.log(data)
  } catch(error){
    console.log(error)
  }
}

[karstenkoehler · Accepted solution]

Hi @tbunch,

As far as I know, this is not possible. There aren't any report settings in the custom report builder that would limit the display of records of one of the data sources to the latest one.

It would indeed be useful to have this; you could request this in the HubSpot Ideas section of the community: https://community.hubspot.com/t5/HubSpot-Ideas/idb-p/HubSpot_Ideas

These requests submitted to the HubSpot Ideas section of the community are reviewed by the HubSpot product team, based on their popularity and the assumed demand.

Have a great day!

[tbunch]

@karstenkoehler 

Thanks for the pointer to the Idea submission - I'll get that one in with some additional documentation.

Do you know of some potential work arounds if I were to create a custom field on the deal object called `Last Note` and updated it via workflow (I'm still unsure if I could limit to the last note, but it sounds like there is a way to access all of the associated notes and perhaps use a custom javascript solution to trim it to the last entry).

Based on a couple of your other responses to @PhilThomas @YEakman , I may run into some issues with re-enrollment for deals.

References:

https://community.hubspot.com/t5/CRM/Triggering-a-workflow-every-time-a-note-is-added-to-an-object/m... 
https://community.hubspot.com/t5/Tips-Tricks-Best-Practices/Creating-a-workflow-for-created-notes/m-...