I've already been on Google looking around and it looks like I'm not the person trying to figure this out, but the answers I've found aren't feasible in my environment. Figured I'd ask here if anyone has any better ideas.
Data model: at a high level, we have a custom object Message that has a Lookup to Opportunity. The custom Message object is used for inbound / outbound texting to the customer on the Opportunity through a separate integration.
Problem: The business wants a report showing information from the Opportunity (Customer name, product basics, etc.) WHERE that Opportunity has an inbound Message our integration has marked with a certain Intent. Simple enough. But they also want to see the most recent message that was sent on that messaging thread.
The first option I've found on Google is to create a rollup summary on the master to find the most current record on the child object, but since this is a Lookup it's not feasible.
The workaround I could come up with is two custom Boolean fields on my Message object: LatestInboundMsg
LatestOutboundMsg
From there, build a Flow triggered on Record Created, my custom Message object, to sweep up all records where OpportunityId = {Record.OpportunityId}, and toggle their Booleans to False, and then toggle the Boolean on the triggering record to True based on whether it's inbound or outbound.
That feels like a heavy lift to satisfy a reporting ask. Can anyone come up with something simpler? Formula fields don't look like they can recursively table scan an entire object to do this so this is the only way I can come up with to reproduce what a Rollup summary would do.
Hello,
I think you are on the right track with a formula to set the latest record with a boolean value of true that you can then filter in your report.
Within your flow you can get records by created date so you would need to order by descending to get the latest one. You could then update all of the other records to false in one component on the flow canvas.
