Versions Compared

Old Version 3

changes.mady.by.user Hugo

Saved on

compared with

New Version Current

changes.mady.by.user Bastiana

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

...

  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
    langsql
    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
    Note: Please replace the code "app_fd_hr_expense_claim" with your own table name if you intend to use it for other application.
  5.  Set "Primary Key" to "a.id"
  6.  Click OK.

...

With the changes made above, we will now be able to list down the records related to the currently logged in user.

Image Added
Figure 5: Filtered List of Pending Activity and Assignee

Additional Information:

  1. The following query is for MSSQL to use.
    Code Block
    langsql
    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