Figure 1: Viewing submitted application through List
By default, users would be able to see the submitted applications by going through the "Personal Expenses" listing but one will not be able to tell what is the next activity in line and who is supposed to attend to it. This can be solved by creating a new List.
Create a new List.
Choose Database SQL Query List Data Store.
In "Configure Database SQL Query List Data Store", choose "Default Datasource" in "Datasource".
Apply the following query in "SQL SELECT Query"
SELECT a.*, sact.Name AS activityName, GROUP_CONCAT(DISTINCT sass.ResourceId SEPARATOR ', ') AS assignee FROM app_fd_j_expense_claim a INNER JOIN wf_process_link wpl ON wpl.originProcessId = a.id INNER JOIN SHKActivities sact ON wpl.processId = sact.ProcessId JOIN SHKActivityStates ssta ON ssta.oid = sact.State INNER JOIN SHKAssignmentsTable sass ON sact.Id = sass.ActivityId WHERE ssta.KeyValue = 'open.not_running.not_started' GROUP BY a.id
Note: Please replace the code "app_fd_hr_expense_claim" with your own table name if you intend to use it for other application.
Set "Primary Key" to "a.id"
Click OK.
Figure 2: Adding the columns into the List
Next, add in the columns intended, and most importantly, add "activityName" and "assignee" to reveal the pending activity and assignees.
Figure 3: List showing the pending activity and assignees
The List will now list all the pending activities of everyone. Next, we are going filter the List such that user will only see what they submitted.
Figure 4: Retrieving the Requester Information
We may determine on who is the claimant by looking up the "claimant" field.
In the List's "SQL SELECT Query", modify the code to the following:-
SELECT a.*, sact.Name AS activityName, GROUP_CONCAT(DISTINCT sass.ResourceId SEPARATOR ', ') AS assignee FROM app_fd_hr_expense_claim a JOIN SHKActivities sact on a.id = sact.ProcessId JOIN SHKActivityStates ssta ON ssta.oid = sact.State INNER JOIN SHKAssignmentsTable sass ON sact.Id = sass.ActivityId WHERE ssta.KeyValue = 'open.not_running.not_started' AND a.c_claimant = '#currentUser.firstName# #currentUser.lastName#' GROUP BY a.id
This is the new code added in.
AND a.c_claimant = '#currentUser.firstName# #currentUser.lastName#'
With the changes made above, we will now be able to list down the records related to the currently Logged In User.
Figure 5: Filtered List of Pending Activity and Assignee
Additional Information:
The following query is for MSSQL to use.
SELECT dat.*, asg.activityName, asg.assignees FROM (SELECT id, activityName, assignees from (SELECT a.id, sact.Name AS activityName, sass.ResourceId AS assignee FROM app_fd_applications a JOIN SHKActivities sact on a.id = sact.ProcessId JOIN SHKActivityStates ssta ON ssta.oid = sact.State INNER JOIN SHKAssignmentsTable sass ON sact.Id = sass.ActivityId WHERE ssta.KeyValue = 'open.not_running.not_started' group by sact.Name, sass.ResourceId, a.id) AS A CROSS APPLY (SELECT assignee + ',' FROM (SELECT a.id, sact.Name AS activityName, sass.ResourceId AS assignee FROM app_fd_applications a JOIN SHKActivities sact on a.id = sact.ProcessId JOIN SHKActivityStates ssta ON ssta.oid = sact.State INNER JOIN SHKAssignmentsTable sass ON sact.Id = sass.ActivityId WHERE ssta.KeyValue = 'open.not_running.not_started' group by sact.Name, sass.ResourceId, a.id) AS B WHERE A.id = B.id AND A.activityName = B.activityName FOR XML PATH('')) D (assignees) GROUP BY id, activityName, assignees ) asg JOIN app_fd_applications dat ON asg.id = dat.id