Groups assigned to tasks - database view

[tysteelsii]

I've been trying to create a database view where I can see what groups are assigned to (fulfillers) in various workflow activities (catalog task, approval, etc).   Basically show an assignment group and then show the workflow (workflow version), the activity definition, and activity definition name.   NOTE: I'm not looking at active tasks but the actual assignment group chosen when designing a workflow.   The sys_user_group and wf_activity is where I'm looking at.

The fulfillment group of workflow tasks looks to be located at wf_activity > variables > task_fulfillment_group and you can't just dot walk to that or I would be just using the reporting module without the need of a database view.   But it doesn't seem like I can join the two tables correctly.   I'm thinking that a where clause would link the group sys_id to the wf_activity field like this:

grp_sys_id = wfa_variables.task_fulfillment_group

But I'm unsuccessful (get access violations) or "column not existing errors" when running it.   When I look at the task fulfillment group field on the workflow closer, I see that it looks something like this:

wf_activity.vars.var__m_38891b6f0a0a0b1e00efdfdd77602027.task_fulfillment_group

So it would seem that there is more involved in order to get to that task_fulfillment_group.  

Any suggestions or tips?

[LaurentChicoine]

Hi,

I think you might be using the wrong table. The values of workflow activity definition is stored in sys_variable_value and the rendering of that is strange.

Your database view should look something like this:

table	variable prefix	where clause	left join	order
sys_user_group	gr		false	100
sys_variable_value	wfv	wfv.value = gr.sys_id && wfv.variable = 'd7f2bd33c0a801650021da8ff242a081'	false	200
wf_activity	wfa	wfa.sys_id = wfv.document_key	false	300

For the workflow version information you should be able to dot walk from the workflow activity.

[tysteelsii]

Thank you VERY MUCH for assistance Laurent.

I've done a number of database views but this view has just confused me.   I completely missed the sys_variable_value table.   That seems to be the "glue" between the sys_user_group and wf_activity in this view.   I dropped the wfv.variable in the where clause and the view still worked.   I understand the rest of the where clause but what was the wfv.variable used for?

[LaurentChicoine]

wfv.variable = 'd7f2bd33c0a801650021da8ff242a081' was used to only get the group value for the fulfillment group of catalog task. Otherwise you are joining for all match between the group id and a group in the sys_user_group table even if that value is not the fulfillment group of a catalog task. You could however make that filter inside the report itself there is no need for it to be in the database view.

[tysteelsii]

Makes sense.   Like you mentioned, I will use a filter inside the report itself.   Once again, thank you.   I will now have some happier business units and I learned something as well.