Page History

...

1. Create a new Datalist.
2. Choose JDBC Datalist Database Binder.
3. In "Configure JDBC Datalist Database Binder", choose "Default Datasource" in "Datasource".
4. Apply the following query in "SQL SELECT Query"

   Code Block
   lang sql
   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' GROUP BY a.id

5.  Set "Primary Key" to "a.id"
6.  Click OK.

Image AddedImage Removed
Figure 2: Adding the columns into the Datalist

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

Image Added
Figure 3: Datalist showing the pending activity and assignees

The Datalist 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. Image Added
Figure 4: Retrieving the requester information

We may determine on who is the claimant by looking up the "claimant" field.

In the Datalist'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.