List Pending Activity and Assignees

Introduction

Tracking the status of submitted process applications and identifying pending activities and assignees are crucial for effective process management. In this article, you'll learn how to create a list that displays detailed information about submitted applications, including the pending activity and the assigned user. This setup helps users quickly understand the status of their applications and what actions are pending.

How does it work?

In this exercise, you are using the HR Expenses Claim App, which is bundled together in the Joget Enterprise edition and has MySQL as the database.

By default, users can see the submitted applications by going through the Personal Expenses listing, but they will not be able to tell what the next activity is in line and who is supposed to attend to it. This can be solved by creating a new List.

1. Create a new List.
2. Choose Database SQL Query List Data Store.
3. In Configure Database SQL Query List Data Store, choose Default Datasource in Datasource.
4. Apply the following query to the 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:
   Replace the code app_fd_hr_expense_claim with your own table name if you intend to use it for other applications. 
5. Set Primary Key to a.id
6. Click Save.

Next, add in the columns intended, and most importantly, add activityName and assignee to reveal the pending activity and assignees.

The List will now list everyone's pending activities. Next, you will filter the List so that users see only what they submitted.

You may determine 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, you will now be able to list down the records related to the currently Logged In User.

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