List Assignees - JDBC code seems incorrect

 
1
0
-1

I am using the following code from the KB article


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


it does not work, on Claims App as well as my custom app, it looks like a.Id is a GUID while sact.ProcessId is a string like 46_dbProcessName_approver_process


Please advise what is wrong.

    CommentAdd your comment...

    1 answer

    1.  
      1
      0
      -1

      Hi there,

      The mismatch of "a.id= sact.ProcessId" is due to changes done in DX as a security enhancement. See here: Joget v6 to DX Upgrade Notes#ImportantNoteAboutJogetDXNewProcessRecordIDUsingUUID

      The linkage of the record GUID with its associated Process ID can be found in the database table "wf_process_link".

      Try out the fixed query below and see:

      SELECT a.*, sact.Name AS activityName, GROUP_CONCAT(DISTINCT sass.ResourceId SEPARATOR ', ') AS assignee
FROM app_fd_j_expense_claim a
JOIN SHKActivities sact on a.id = (SELECT parentProcessId FROM wf_process_link WHERE 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
        CommentAdd your comment...