Extract Flagged WorkNotes & Create a Report

[AmrutaP79040408]

Hi everyone,

I’m working on a requirement to create a Legal Request report where users should be able to include only the flagged Work Notes from the Activity Log.

So far from my investigation:

Flagged Work Notes are stored in the OOB sys_activity_supplemental table.
The relevant fields in this table are:
Payload – contains values such as "api_version":"1" and "flag":true, which indicates the activity is flagged.
Event ID(string type) – references the sys_id of the journal entry(sys_journal_field).
Document ID(string) – references the element_id in the (sys_journal_field) table.

Using these fields, we can establish the relationship between the flagged activity, the journal entry, and the related record.

Current approach :-
I created a custom table to establish the relationship between sys_activity_supplemental and the Journal table.
I then created a report on the custom table.
With this approach, I am able to extract the flagged Work Notes.

However, I’m looking for an alternative OOB/best-practice approach, if one is available.

**Additional requirements**

There are some additional requirements that the current approach does not fully address:

Assess the feasibility of extracting other Activity Log information based on the activity types selected in the filter, such as:
Additional Comments
Attachments
Other Activity Log entries

Also the extracted Work Notes should be plain text without HTML tags.
Currently, when Work Notes are extracted, they contain HTML tags/formatting that needs to be removed.
Ideally, the solution should support the Activity Log filtering requirements without maintaining a separate custom table.

Is there any OOB functionality, reporting approach, or recommended architecture that can achieve these requirements without creating a custom table?

In particular, I’m interested in knowing how others would approach:

Identifying flagged Work Notes from sys_activity_supplemental.

Using Event ID → Journal sys_id to retrieve the corresponding journal entry.
Using Document ID → Journal element_id to identify the related record.
Extracting additional Activity Log information such as Additional Comments, Attachments, etc.
Applying the same activity filtering available in the Activity Log.
Converting the extracted Work Notes to plain text without HTML.

The custom-table approach works for extracting the flagged Work Notes, but I’m not sure if this is the recommended solution, especially considering the additional Activity Log requirements.

Has anyone implemented something similar or can suggest a better OOB/best-practice approach?

Any suggestions would be greatly appreciated.