...
In this exercise, we are using the HR Expenses Claim App that is bundled together in the Joget Enterprise edition with MySQL as the database.
Figure 1: Viewing submitted application through Datalist
...
Figure 5: Filtered List of Pending Activity and Assignee
Additional Information:
- The following query is for MSSQL to use.
Code Block lang sql 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