Might be similar question is very popular & asked several times but if someone can suggest a way to achieve this by any way then it'd be very helpful.
What I want:
I want to have dump of worklist data for each user as we're going to send this data to another system for use.
(Example: if I query that final dump with where userid = 'xyzuser1' then it should be able to give me tasks for that user.)
Things to consider:
=> I'm using
- K2 Roles (dynamic also)
- AD-Groups in K2 Roles (optional - I'll find a workaround for this)
=> I'll refresh this data & get the latest dump at certain interval (let's say 10 minutes)
What I've tried:
I'm doing an SQL query. I tried with joining the K2 tables & it is giving me worklist data but only problem is with K2 Roles (dynamic). It gives me participant as role name instead of users.
This is where I'm stuck. I don't know how to get the role users (for dynamic roles only).
I really want to go for SQL query way if anyone can suggest solution.
There are some suggestions online that go for code approach but can anyone confirm if I can get dump of worklist data for ALL the users in my organization?
Roles membership information is stored on a separate schema. You should look at the Identity.Identity table and Identity.IdentityMember table.
Roles are represented in Identity.Identity table by Type=2. You can use that to quickly filter out your role, and grab the ID. Then query the Identity.IdentityMember table WHERE ContainerID = <the ID of your role in Identity.Identity>. This will return you a list of MemberID that belong under that role.
With the list of MemberID, you can join with, or filter the Identity.Identiy table again to grab the list of users (or groups) that are inside the role. If you have a group in the role, you may have to repeat the steps again, to grab all the users in the group.
If you query the K2 database tables then your query will break if the implementation changes in the future. In the System category there is a SmartObject that can return the worklist for all users.
I have to agree with DavidL on this. Querying the K2 database tables is not a recommended approach, future upgrades can change the database structure and will have an impact on your existing solutions. . For example, K2 Five (5.2) has introduced utilized [Authorization].[RoleMember] and [Authorization].[Role] tables to manage K2 roles. If your existing solution is heavily depended on identity role tables for reporting data (i.e. [Identity].[RoleItem], or [Identity].[RoleItem]), then it will need to refactor in this change when you plan to upgrade later on. I would use existing system SmartObjects such as the Task, Task List Share, User Role, and User Role Item to get user worklists. These SmartObjects are under SystemManagementUsersSmartObject category.