Need Help Creating A Process History Report

Unknown

Need Help Creating A Process History Report

Unknown

Hi, I need to pull all of the data shown in the Process History and Collaboration View of Process Folders for a Flow Execution Extension type. I am currently pulling some of the data from the [b]entity_assigment[/b] table via the query editor, but I cannot seem to get it structured the way it should be based on looking at the process folder for a test form I submitted. Can you please tell me how I would do this and what tables I should be pulling from?

Unknown

Hi Betsy,

All of the data that would typically be present in the process history and collaboration tab of a process view page can be found among the [b]entity_assignment[/b], [b]entity_comment[/b], and [b]folder_state[/b] tables. Each of these items related to a given process folder will have that [b]Process Folders Folder ID[/b] as its [b]entity_folder_id[/b] field.

With that in mind, you can get a list of similar properties from each of these tables along with the entitys ID. The ID can then be used to fetch the rest of the entitys data which may not be present in the other types. You could use a query similar to this to get a full list of items that would show up in your process folders process history and collaboration tab:

[i]select [/i]
[i]coalesce(to_state, -----) as Name or ToState, coalesce(from_state, -----) as Description or FromState, [/i]
[i]coalesce(created_on_date, No Date) as Date Created, coalesce(created_by, -----) as Created By, coalesce(folder_state_id, -----) as ID [/i]
[i]from folder_state [/i]
[i]where entity_folder_id = @PFID[/i]
[i]
[/i]
[i]union[/i]
[i]
[/i]
[i]select [/i]
[i]coalesce(entity_name, -----), coalesce(entity_description, -----), coalesce(created_on_date, No Date), [/i]
[i]coalesce(created_by, -----), coalesce(comment_id, -----) [/i]
[i]from entity_comment [/i]
[i]where entity_folder_id = @PFID[/i]
[i]
[/i]
[i]union[/i]
[i]
[/i]
[i]select coalesce(entity_name, -----), coalesce(entity_description, -----), coalesce(created_on_date, No Date), [/i]
[i]coalesce(created_by, -----), coalesce(assignment_id, -----) [/i]
[i]from entity_assignment [/i]
[i]where entity_folder_id = @PFID[/i]

[i]
[/i]

Adding this or a query like it as a Stored Query will make it available as a step in the Flow Designer, which you can then use in a [url=https://documentation.decisions.com/docs/using-flow-source-report]Report Data Source Flow[/url], create logic to handle each of the data types and perform actions accordingly in a flow, or iterate through the results of this to create custom text merges with any desired additional fields for each of the types present.