Hello. What table/tables are used in the Forms SQL database that will show all active assigned tasks and the user assigned to the task. Thanks for your time.
Table [cf_bp_worker_instnc_to_resume] for user task: [resume_id] column is the id of the user task, and [owner_snapshot_id] column is the user that task is assigned to.
Table [cf_user_snapshot] links the [owner_snapshot_id] with [id].
From what I see in the Forms 10.1 database, the [owner_snapshot_id] is actually tied to the [id] field in the [cf_user_snapshot] table. Is that correct?
You are correct. Updated in the post.
To go along with this, how would I write a SQL query to pull only user tasks from a specific Forms process?
SELECT * FROM [cf_bp_worker_instnc_to_resume] as w inner join [cf_form_process_mapping] as f on w.form_id = f.form_id inner join [cf_bp_processes] as p on f.process_id = p.process_id where p.bp_id = 215
Change 215 to the id of your business process.
I have noticed that the owner_snapshot_id is not populated if the user task has gone to more than 1 person and has not been assigned to an individual. Is there a way to query those as well?
[owner_snapshot_id] would be null if task is available to multiple users or team.
You need additional [cf_bp_instance_approvers] table in this case. Try this:
SELECT a.[team_id], a.[user_snapshot_id], a.[resume_id] FROM [cf_bp_instance_approvers] as a inner join [cf_bp_worker_instnc_to_resume] as w on w.resume_id = a.resume_id inner join [cf_form_process_mapping] as f on w.form_id = f.form_id inner join [cf_bp_processes] as p on f.process_id = p.process_id where p.bp_id = @bpid
Thank you Rui!
I'm using Rui Deng's in slightly modified form. I want all open task for all users. So here's what I'm running,
SELECT a.[team_id], a.[user_snapshot_id], a.[resume_id] FROM [cf_bp_instance_approvers] as a inner join [cf_bp_worker_instnc_to_resume] as w on w.resume_id = a.resume_id inner join [cf_form_process_mapping] as f on w.form_id = f.form_id inner join [cf_bp_processes] as p on f.process_id = p.process_id ORDER BY user_snapshot_id
But I'm not getting the results I'm expecting. I just don't see the users I should. Only certain users are showing up. One user has a boatload of tasks and he's not in either table at all!
Hi Alex,
Not sure why it does not work for you since it works in my place, but I guess maybe you mixed up user_snapshot_id with user_id? Here is an updated script for showing team name and user name and I also added filter on task status, can you check if this is correct?
SELECT a.[team_id], t.[name], a.[user_snapshot_id], u.[username], a.[resume_id], p.[bp_id] FROM [cf_bp_instance_approvers] as a inner join [cf_bp_worker_instnc_to_resume] as w on w.resume_id = a.resume_id inner join [cf_form_process_mapping] as f on w.form_id = f.form_id inner join [cf_bp_processes] as p on f.process_id = p.process_id left join [cf_user_snapshot] as u on a.user_snapshot_id = u.id left join [teams] as t on a.team_id = t.id where w.[status] = 1 or w.[status] = 5 ORDER BY user_snapshot_id